link: SQL Operations

SQL JOINS

Example Data

select * from TableA;
id
----
 1
 2
 3
 4
 5
 6
 7
 8
(8 rows)
 
select * from TableB;
id
----
 1
 2
 3
 4
 5
 9
 10
 11
(8 rows)
 

Inner Join

Returns rows that have matching values in both tables.

select * from TableA inner join TableB on TableA.id = TableB.id;
id | id
----+----
 1 |  1
 2 |  2
 3 |  3
 4 |  4
 5 |  5
(5 rows)
 

Left Outer Join

Includes all rows from the left table and matched rows from the right table. Unmatched rows from the right table are filled with NULL.

select * from TableA left outer join TableB on TableA.id = TableB.id;
id | id
----+----
 1 |  1
 2 |  2
 3 |  3
 4 |  4
 5 |  5
 6 |
 7 |
 8 |
(8 rows)
 

Left Outer Exclusive Join

Returns rows from the left table that have no matches in the right table

select * from TableA left outer join TableB on TableA.id = TableB.id where TableB.id is null;
id | id
----+----
 6 |
 7 |
 8 |
(3 rows)
 

Full Outer Join

Includes all rows from both tables, filling in NULLs where matches are not found.

select * from TableA full outer join TableB on TableA.id = TableB.id;
id | id
----+----
 1 |  1
 2 |  2
 3 |  3
 4 |  4
 5 |  5
 6 |
 7 |
 8 |
   |  9
   | 10
   | 11
(11 rows)
 

Full Outer Exclusive Join

Returns rows from both tables that do not match each other.

select * from TableA full outer join TableB on TableA.id = TableB.id where TableA.id is null or TableB.id is null;
id | id
----+----
 6 |
 7 |
 8 |
   |  9
   | 10
   | 11
(6 rows)

Union

Merges the results of two queries, removes duplicates, and sorts the rows.

select * from TableA union select * from TableB;
id
----
10
 2
 5
 4
 6
 9
 1
11
 3
 8
 7
(11 rows)

Union All

Combines all rows from multiple queries, including duplicates, without sorting

select * from TableA union all select * from TableB;
id
----
 1
 2
 3
 4
 5
 6
 7
 8
 1
 2
 3
 4
 5
 9
10
11
(16 rows)