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

This article is issued from Wikiversity. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.