Predict the number of rows in output

Question

I have below table:

col_a	col_b
0	0
1	0
0	0
1	1
1	1
2	0

what will be the count of rows after joining the table in col_a and col_b - left, right and inner? I want to understand how SQL join works.

Answer 1

There's more than joins in Sql

create table test (col_a int, col_b int);
insert into test values (1,1),(2,3),(3,2),(4,0);

select * from test order by 1;

col_a | col_b
----: | ----:
    1 |     1
    2 |     3
    3 |     2
    4 |     0

--
-- [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

--
-- 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

--
-- 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

--
-- 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

--
-- 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

--
--  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

--
-- 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

--
-- 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

--
-- 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

--
-- 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 |

--
-- 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

--
-- 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 |

--
-- 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 |

--
-- 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 |

--
-- 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 |

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

| col_ab |
| -----: |
|      4 |

db<>fiddle here