PostgreSQL Set Operations

Set Operations:

operator to combine result sets of multiple queries into a single result sets.

1. union
2. union all
3. intersect
4. except

Sample Tables:
pearl=# select * from siva;
 id |   name   | age | salary
----+----------+-----+--------
  1 | muthu    |  21 |   2000
  2 | siva     |  26 |   4000
  3 | thanigai |  27 |   5000
(3 rows)

pearl=# select * from muthu ;
 id |    dob     |  place 
----+------------+---------
  1 | 1992-04-16 | theni
  4 | 1991-07-22 | madurai
  5 | 1991-07-22 | covai
(3 rows)

Union Examples:

pearl=# select id from siva union select id from muthu;
 id
----
  2
  5
  4
  3
  1
(5 rows)

Union all Examples:

pearl=# select id from siva union all select id from muthu;
 id
----
  1
  2
  3
  1
  4
  5
(6 rows)

Intersect Examples:

pearl=# select id from siva intersect select id from muthu;
 id
----
  1
(1 row)

Except Examples:

pearl=# select id from siva except select id from muthu;
 id
----
  3
  2
(2 rows)

pearl=# select id from muthu except select id from siva;
 id
----
  5
  4
(2 rows)