SQL/Join
< SQL
An SQL join clause combines records from two or more tables in a database.
Two tables
editCreate 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
|
Left join | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SELECT * FROM tblSuit
LEFT JOIN tblLight ON tblSuit.Color = tblLight.Color
|
Right join | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SELECT * FROM tblSuit
RIGHT JOIN tblLight ON tblSuit.Color = tblLight.Color
|
Left join without inner join | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
SELECT * FROM tblSuit
LEFT JOIN tblLight ON tblSuit.Color = tblLight.Color
WHERE tblLight.Light IS null
|
Inner join | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
SELECT * FROM tblSuit
JOIN tblLight ON tblSuit.Color = tblLight.Color
|
Right join without inner join | ||||||||
---|---|---|---|---|---|---|---|---|
SELECT * FROM tblSuit
RIGHT JOIN tblLight ON tblSuit.Color = tblLight.Color
WHERE tblSuit.Suit IS NULL
|
Full outer join without inner join | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Three tables
editCreate 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
|
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
|
ta LJ te RJ tu | |||||||||
---|---|---|---|---|---|---|---|---|---|
SELECT * FROM ta
LEFT JOIN te ON ca = ce
RIGHT JOIN tu ON ca = cu
|