Set Operations

UNION operator

• The UNION operator combines result sets of two or more SELECT statements into a single result set.

• The UNION operator removes all duplicate rows from the combined data set. To retain the duplicate rows, you use the the UNION ALL instead.

SELECT select_list_1
FROM table_expresssion_1
UNION
SELECT select_list_2
FROM table_expression_2

select * from product where id < 2
UNION
select * from product where id > 2;

 id |                        name                        | price |          country
----+----------------------------------------------------+-------+---------------------------
  4 | CG                                                 |   500 | NEPAL
  1 | Iphone                                             | 10000 | USA
  6 | Jio                                                |  1600 | INDIA
  5 | Tesla                                              | 11500 | USA
  3 | Vivo                                               |  2000 | CHINA
(5 rows)

---

Intersect Operator

• The INTERSECT operator returns any rows that are available in both result sets.

SELECT select_list
FROM A
INTERSECT
SELECT select_list
FROM B;

select * from product where id < 2
INTERSECT
select * from product where id IN (1,2,3,4,5);

 id |                        name                        | price |          country
----+----------------------------------------------------+-------+---------------------------
  1 | Iphone                                             | 10000 | USA
(1 row)

---

Except Opeartor

• The EXCEPT operator is used to return distinct rows from the first (left) query that are not in the output of the second (right) query while comparing result sets of two or more queries.

SELECT column_list
FROM A
WHERE condition_a
EXCEPT
SELECT column_list
FROM B
WHERE condition_b;

select * from product where id > 5
Except
select * from product where id IN (1,2,3,4,5);

 id |                        name                        | price |          country
----+----------------------------------------------------+-------+---------------------------
  6 | Jio                                                |  1600 | INDIA
(1 row)