An SQL join clause combines records from two or more tables in a database.
Two tables
| 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' );
|
| Cross join
|
SELECT * FROM tblSuit JOIN tblLight
| 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
| 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' );
|
| 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
|
| ta LJ te
|
SELECT * FROM ta
LEFT JOIN te ON ca = ce
|
| 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
|
|
