PostgreSQL(part-5)

UNION
Used to combine two or more rows and removes duplicates

select empid from employee union select empid from team;

118
123
101
124
111

UNION ALL
Used to combine two or more rows and remains duplicates

select empid from employee union all select empid from team;

101
123
101
124
118
101
111
118
101
123
124

INTERSECT
Get the values that are common to both tables

select empid from employee intersect select empid from team;

101
124
118
123

EXCEPT
Gets the values that are in the first query but NOT in the second query

select empid from employee except select empid from team;

(0 rows)
There is no employee ID that exists only in employee

 select empid from team except select empid from employee;

111
(1 row)
111 exists in team but does not exist in employee

EXCEPT ALL
Returns the rows that are in the first query but not in the second query and remains duplicate

Table A

101
101
101
123

Table B

101
101
123

select empid from A except all select empid from B;

101
Why?
A has 101 → 3 times
B has 101 → 2 times
3 - 2 = 1