SQL/Join

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' );

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

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

te ce ve yooo

tu cu vu 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