Access UNION: Query Unique Values from Two Tables

1 year ago
Olivia Parker
2 minutes

You can use the UNION keyword to query distinct values from two tables. The UNION operator is used to combine the results of two or more SELECT statements and return all unique rows.

For example, if we have two tables, table1 and table2, with the same columns, we can use the following statement to query for unique values in both tables.

SELECT col1, col2, col3
FROM table1
UNION
SELECT col1, col2, col3
FROM table2;

In this example, col1, col2, and col3 are the column names in the table. The UNION operator will return all non-duplicate rows from both tables.

If you want to return duplicate rows as well, you can use the UNION ALL operator, for example:

SELECT col1, col2, col3
FROM table1
UNION ALL
SELECT col1, col2, col3
FROM table2;

Please note that the result set of UNION and UNION ALL operators will automatically remove duplicates. If you want to manually remove duplicates, you can use the DISTINCT keyword, for example:

SELECT DISTINCT col1, col2, col3
FROM (
    SELECT col1, col2, col3
    FROM table1
    UNION
    SELECT col1, col2, col3
    FROM table2
) AS subquery;

In this example, a subquery is used to perform a UNION operation, and then the DISTINCT keyword is used in the outer query to remove duplicate results.

bannerAds