Join (SQL)

An SQL join clause combines records from two or more tables in a database.

## Two tables[edit | edit source]

Create tables

CREATE TABLE tblSuit

Suit VARCHAR(20),
Color VARCHAR(20)

CREATE TABLE tblLight

Light VARCHAR(20),
Color VARCHAR(20)

INSERT INTO tblSuit( Suit, Color ) VALUES( 'A', 'black' );
INSERT INTO tblSuit( Suit, Color ) VALUES( 'B', 'black' );
INSERT INTO tblSuit( Suit, Color ) VALUES( 'C', 'red' );
INSERT INTO tblSuit( Suit, Color ) VALUES( 'D', 'red' );

INSERT INTO tblLight( Light, Color ) VALUES( '0', 'red' );
INSERT INTO tblLight( Light, Color ) VALUES( '1', 'green' );

tblSuit  | Suit | Color

|

tblLight  | Light | Color

|

Cross join

SELECT * FROM tblSuit JOIN tblLight

It's a Cartesian product:  | ×  |  |

|
|

| (  ,  )  | (  ,  )
| (  ,  )  | (  ,  )
| (  ,  )  | (  ,  )
| (  ,  )  | (  ,  )
Suit  | tblSuit.Color  | Light  | tblLight.Color

|
|
|

|  |  |
|  |  |
|  |  |
|  |  |
|  |  |
|  |  |
|  |  |
|  |  |

Left join

SELECT * FROM tblSuit
LEFT JOIN tblLight ON tblSuit.Color = tblLight.Color

| Suit  | tblSuit.Color  | Light  | tblLight.Color

|
|
|

|  |  |
|  |  |
|  |  |
|  |  |
Right join

SELECT * FROM tblSuit
RIGHT JOIN tblLight ON tblSuit.Color = tblLight.Color

| Suit  | tblSuit.Color  | Light  | tblLight.Color

|
|
|

|  |  |
|  |  |
|  |  |

Left join without inner join

SELECT * FROM tblSuit
LEFT JOIN tblLight ON tblSuit.Color = tblLight.Color
WHERE tblLight.Light IS null

| Suit  | tblSuit.Color  | Light  | tblLight.Color

|
|
|

|  |  |
|  |  |
Inner join

SELECT * FROM tblSuit
JOIN tblLight ON tblSuit.Color = tblLight.Color

| Suit  | tblSuit.Color  | Light  | tblLight.Color

|
|
|

|  |  |
|  |  |
Right join without inner join

SELECT * FROM tblSuit
RIGHT JOIN tblLight ON tblSuit.Color = tblLight.Color
WHERE tblSuit.Suit IS NULL

| Suit  | tblSuit.Color  | Light  | tblLight.Color

|
|
|

|  |  |

Full outer join

SELECT * FROM tblSuit
LEFT JOIN tblLight ON tblSuit.Color = tblLight.Color
UNION
SELECT * FROM tblSuit
RIGHT JOIN tblLight ON tblSuit.Color = tblLight.Color

| Suit  | tblSuit.Color  | Light  | tblLight.Color

|
|
|

|  |  |
|  |  |
|  |  |
|  |  |
|  |  |
Full outer join without inner join

| Suit  | tblSuit.Color  | Light  | tblLight.Color

|
|
|

|  |  |
|  |  |
|  |  |

## Three tables[edit | edit source]

Create tables

CREATE TABLE ta ( ca VARCHAR(5) );
CREATE TABLE te ( ce VARCHAR(5) );
CREATE TABLE tu ( cu VARCHAR(5) );

INSERT INTO ta( ca ) VALUES( 'va' );
INSERT INTO ta( ca ) VALUES( 'yooo' );

INSERT INTO te( ce ) VALUES( 've' );
INSERT INTO te( ce ) VALUES( 'yooo' );

INSERT INTO tu( cu ) VALUES( 'vu' );
INSERT INTO tu( cu ) VALUES( 'yooo' );

ta  | ca

yooo
te  | ce

yooo
tu  | cu

yooo
Cross join

SELECT * FROM ta JOIN te JOIN tu

| ca | ce | cu

|
|

va | ve | vu
yooo | ve | vu
va | yooo | vu
yooo | yooo | vu
va | ve | yooo
yooo | ve | yooo
va | yooo | yooo
yooo | yooo | yooo

ta J te

SELECT * FROM ta
JOIN te ON ca = ce

| ca | ce

|

yooo | yooo
ta LJ te

SELECT * FROM ta
LEFT JOIN te ON ca = ce

| ca | ce

|

va |
yooo | yooo

ta LJ te LJ tu

SELECT * FROM ta
LEFT JOIN te ON ca = ce
LEFT JOIN tu ON ca = cu

| ca | ce | cu

|
|

va |  |
yooo | yooo | yooo

ta LJ te RJ tu

SELECT * FROM ta
LEFT JOIN te ON ca = ce
RIGHT JOIN tu ON ca = cu

| ca | ce | cu

|
|

|  | vu
yooo | yooo | yooo