Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- https://kb.databasedesignbook.com/posts/sql-joins/

create table A (a integer not null primary key);

create table B (b integer not null primary key, ref_a integer);

create table C (c integer not null primary key, ref_b integer);

insert into A values (1), (2);

insert into B values (11,1), (12,NULL);

insert into C values (101,11), (102,12), (103,NULL);

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 2

INSERT 0 2

INSERT 0 3

select * from A left join B on B.ref_a = A.a

a
b
ref_a

1
11
1

2
null
null

SELECT 2

select * from B left join C on C.ref_b = B.b OR C.c = 103

b
ref_a
c
ref_b

11
1
101
11

11
1
103
null

12
null
102
12

12
null
103
null

SELECT 4

select * from A 
            left join B on A.a = B.ref_a
            left join C on C.ref_b = B.b OR C.c = 103

a
b
ref_a
c
ref_b

1
11
1
101
11

1
11
1
103
null

2
null
null
103
null

SELECT 3

select *
  from A left join (B left join C on C.ref_b = B.b OR C.c = 103) on A.a = B.ref_a

a
b
ref_a
c
ref_b

1
11
1
101
11

1
11
1
103
null

2
null
null
null
null

SELECT 3

a	b	ref_a
1	11	1
2	null	null

a	b	ref_a	c	ref_b
1	11	1	101	11
1	11	1	103	null
2	null	null	null	null