Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (col_a int, col_b int);
insert into test values (1,1),(2,3),(3,2),(4,0);

CREATE TABLE

INSERT 0 4

select * from test order by 1;

col_a
col_b

1
1

2
3

3
2

4
0

SELECT 4

--
-- [inner] join: only when a = b
--
select a.col_a, b.col_b
from test a
join test b 
  on b.col_b = a.col_a

col_a
col_b

1
1

2
2

3
3

SELECT 3

--
-- left [outer] join: all of left side a
--
select a.col_a, b.col_b
from test a
left join test b 
  on b.col_b = a.col_a

col_a
col_b

1
1

2
2

3
3

4
null

SELECT 4

--
-- right [outer] join, all of right side b
--
select a.col_a, b.col_b
from test a
right join test b 
  on b.col_b = a.col_a

col_a
col_b

null
0

1
1

2
2

3
3

SELECT 4

--
-- full [outer] join: all of left a and all of right b
--
select a.col_a, b.col_b
from test a
full join test b 
  on b.col_b = a.col_a

col_a
col_b

null
0

1
1

2
2

3
3

4
null

SELECT 5

--
-- natural join: an inner join, but automatic on the same column names
--
select a.col_a, b.col_b
from test a
natural join test b 

col_a
col_b

1
1

2
3

3
2

4
0

SELECT 4

--
--  some join with a "using" instead of "on" 
--
select a.col_a, b.col_b
from test a
inner join test b using (col_a)

col_a
col_b

1
1

2
3

3
2

4
0

SELECT 4

--
-- cross join: a * b (cartesian product)
--
select a.col_a, b.col_b
from test a
cross join test b 

col_a
col_b

1
1

1
3

1
2

1
0

2
1

2
3

2
2

2
0

3
1

3
3

3
2

3
0

4
1

4
3

4
2

4
0

SELECT 16

--
-- lateral join: 
--
select a.col_a, lat_b.col_b
from test a
cross join lateral (
  select b.col_b, count(*) as total
  from test b 
  where b.col_b = a.col_a
  group by b.col_b
) lat_b

col_a
col_b

1
1

2
2

3
3

SELECT 3

--
-- correlated sub-query
--
select 
  a.col_a, 
  (select max(col_b) from test b where a.col_a = b.col_b) as col_b
from test a

col_a
col_b

1
1

2
2

3
3

4
null

SELECT 4

--
-- where b in a
--
select a.col_a
from test a
where col_a in (select distinct col_b from test b)

col_a

1

2

3

SELECT 3

--
-- where tupple in opposite tupple
--
select col_a, col_b
from test a
where (col_a, col_b) in (select col_b, col_a from test b)

col_a
col_b

1
1

2
3

3
2

SELECT 3

--
-- where b exists in a
--
select a.col_a
from test a
where exists (
  select 1
  from test b 
  where b.col_b = a.col_a
)

col_a

1

2

3

SELECT 3

--
-- union all: stack a and b
--
select col_a as col_ab from test a
union all
select col_b from test b

col_ab

1

2

3

4

1

3

2

0

SELECT 8

--
-- union: stack a and b, but discard duplicates
--
select col_a as col_ab from test a
union
select col_b from test b

col_ab

2

4

1

3

0

SELECT 5

--
-- intersect: only when the value is in both
--
select col_a as col_ab from test a
intersect
select col_b from test b

col_ab

3

2

1

SELECT 3

--
-- except: a minus b
--
select col_a as col_ab from test a
except
select col_b from test b

col_ab

4

SELECT 1
col_a	col_b
null	0
1	1
2	2
3	3
4	null