／var／logmarcus chiu

SQL - JOIN

Created on · Last Modified on

Subpages

Example Joins

Table A

table a

A_id

b_id

1

1

2

1

3

2

4

2

Table B

table b

B_id

a_id

1

3

2

3

3

4

4

4

5

10

6

11

Self Join

SELECT * FROM a AS a_1
(INNER|LEFT|RIGHT|FULL OUTER) JOIN a AS a_2
ON a_1.A_id = a_2.b_ib;

joining a table with itself

Left Inclusive Join

SELECT * FROM a 
LEFT JOIN b 
ON a.A_id = b.a_ib;

A_id

b_id

B_id

a_id

3

2

1

3

3

2

2

3

4

2

3

4

4

2

4

4

1

1

NULL

NULL

2

1

NULL

NULL

Full Outer Inclusive Join

SELECT * FROM a
FULL OUTER JOIN b
ON a.A_id = b.a_ib;

A_id

b_id

B_id

a_id

3

2

1

3

3

2

2

3

4

2

3

4

4

2

4

4

1

1

NULL

NULL

2

1

NULL

NULL

NULL

NULL

5

10

NULL

NULL

6

11

Right Inclusive Join

SELECT * FROM a
RIGHT JOIN b
ON a.A_id = b.a_ib;

A_id

b_id

B_id

a_id

3

2

1

3

3

2

2

3

4

2

3

4

4

2

4

4

NULL

NULL

5

10

NULL

NULL

6

11

Left Exclusive Join

SELECT * FROM a 
LEFT JOIN b 
ON a.A_id = b.a_ib 
WHERE b.a_id IS NULL;

A_id

b_id

B_id

a_id

1

1

NULL

NULL

2

1

NULL

NULL

Full Outer Exclusive Join

SELECT * FROM a
FULL OUTER JOIN b
ON a.A_id = b.a_ib
WHERE a.A_id IS NULL 
   OR b.a_id IS NULL;

A_id

b_id

B_id

a_id

1

1

NULL

NULL

2

1

NULL

NULL

NULL

NULL

5

10

NULL

NULL

6

11

Right Exclusive Join

SELECT * FROM a
RIGHT JOIN b
ON a.A_id = b.a_ib
WHERE a.A_id IS NULL;

A_id

b_id

B_id

a_id

NULL

NULL

5

10

NULL

NULL

6

11

Cross Join

SELECT * FROM a
CROSS JOIN b
ON a.A_id = b.a_ib;

A_id

b_id

B_id

a_id

3

2

1

3

3

2

2

3

4

2

3

4

4

2

4

4

Explicit Inner Join

SELECT * FROM a
INNER JOIN b
ON a.A_id = b.a_ib;

A_id

b_id

B_id

a_id

3

2

1

3

3

2

2

3

4

2

3

4

4

2

4

4

Implicit Inner Join

SELECT * FROM a, b
WHERE a.A_id = b.a_ib;

A_id

b_id

B_id

a_id

3

2

1

3

3

2

2

3

4

2

3

4

4

2

4

4