What is the difference between UNION, UNION ALL, INTERSECT, and EXCEPT operations in PostgreSQL?

In PostgreSQL, UNION, UNION ALL, INTERSECT, and EXCEPT are set operations used to combine or compare the results of multiple SELECT statements.

1. UNION: The UNION operation combines the results of two or more SELECT statements, removing any duplicate rows. It is similar to combining the results of two tables using the SQL JOIN operation. The syntax for UNION is as follows:

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2

Example:

Suppose we have two tables named "students" and "teachers". Both tables have the columns "name" and "age".

students
--------
name | age
-----|-----
John | 18
Sara | 19
Tom  | 20

teachers
--------
name  | age
------|-----
Emily | 30
Tom   | 40

If we want to combine the names of students and teachers without any duplicates, we can use the UNION operation as follows:

SELECT name FROM students
UNION
SELECT name FROM teachers

The output of this query would be:

name  
------
Emily
John
Sara
Tom

As we can see, the duplicate name "Tom" has been removed from the result set.

2. UNION ALL: The UNION ALL operation combines the results of two or more SELECT statements, including duplicate rows. The syntax for UNION ALL is the same as for UNION, except we use the keyword UNION ALL instead of UNION. Example:

SELECT name FROM students
UNION ALL
SELECT name FROM teachers

The output of this query would be:

name  
------
John
Sara
Tom
Emily
Tom

As we can see, the duplicate name "Tom" appears twice in the result set.

3. INTERSECT: The INTERSECT operation returns only the rows that are common to both SELECT statements. In other words, it returns the intersection of two result sets. The syntax for INTERSECT is as follows:

SELECT column1, column2, ... FROM table1
INTERSECT
SELECT column1, column2, ... FROM table2

Example:

Suppose we have two tables named "table1" and "table2". Both tables have the columns "id" and "name".

table1
-------
id | name
---|-----
1  | A
2  | B
3  | C

table2
-------
id | name
---|-----
2  | B
3  | C
4  | D

If we want to find the rows that are common to both tables based on the "id" column, we can use the INTERSECT operation as follows:

SELECT id, name FROM table1
INTERSECT
SELECT id, name FROM table2

The output of this query would be:

id | name
---|-----
2  | B
3  | C

As we can see, only the rows with "id" 2 and 3 are common to both tables.

4. EXCEPT: The EXCEPT operation returns only the rows that are in the first SELECT statement but not in the second SELECT statement. In other words, it returns the set difference between two result sets. The syntax for EXCEPT is as follows:

SELECT column1, column2, ... FROM table1
EXCEPT
SELECT column1, column2, ... FROM table2

Example:

Suppose we have the same two tables as in the previous example.

If we want to find the rows that are in "table1" but not in "table2" based on the "id" column, we can use the EXCEPT operation as follows:

SELECT id, name FROM table1
EXCEPT
SELECT id, name FROM table2

The output of this query would be:

id | name
---|-----
1  | A

As we can see, only the row with "id" 1 is in "table1" but not in "table2".