Home

UNION

SELECT *
FROM skating
WHERE year BETWEEN 2010 AND 2014

UNION

SELECT *
FROM cycling
WHERE year BETWEEN 2010 AND 2014;

Some caveats:

  1. Both tables must have the same number of columns so that the results can be merged into one table.
  2. Respective columns must have the same kind of information.

UNION ALL

By default, UNION removes duplicate rows. UNION ALL can remedy this.

SELECT *
FROM cycling
WHERE country = 'Germany'

UNION ALL

SELECT *
FROM skating
WHERE country = 'Germany';

SELECT country
FROM cycling

UNION ALL

SELECT country
FROM skating;

INTERSECT

INTERSECT only shows the rows which belong to BOTH tables.

SELECT year
FROM cycling
WHERE country = 'Germany'

INTERSECT

SELECT year
FROM skating
WHERE country = 'Germany';

SELECT person
FROM cycling

INTERSECT

SELECT person
FROM skating;

EXCEPT

It shows all the results from the first (left) table with the exception of those that also appeared in the second (right) table.

SELECT person
FROM cycling
WHERE country = 'Germany'

EXCEPT

SELECT person
FROM skating
WHERE country = 'Germany';

SELECT country
FROM cycling

EXCEPT

SELECT country
FROM skating;

Repository

https://github.com/okeeffed/developer-notes-nextjs/content/postgresql/SQL-A-Z-Postgres-Track-SQL-Basics/6-Set-Operations

Sections

UNION
UNION ALL
INTERSECT
EXCEPT

Related

1-SQL-Basics-In-Postgres
2-Querying-Multiple-Tables
3-Aggregation-And-Grouping
4-Joins
5-Subqueries
6-Set-Operations
7-Basics-Quiz
SQL-A-Z-Postgres-Track-SQL-Basics