LEFT JOIN
From SQLZoo
The SELECT .. LEFT JOIN statement
The LEFT JOIN will include rows from the left table even when the linking value is null.
|
|
schema:scott
DROP TABLE games;
DROP TABLE city
CREATE TABLE games(
yr INTEGER,
city VARCHAR(20));
INSERT INTO games VALUES (2004,'Athens');
INSERT INTO games VALUES (2008,'Beijing');
INSERT INTO games VALUES (2012,'London');
INSERT INTO games VALUES (2032,'');
CREATE TABLE city (
name VARCHAR(20),
country VARCHAR(20));
INSERT INTO city VALUES ('Sydney','Australia');
INSERT INTO city VALUES ('Athens','Greece');
INSERT INTO city VALUES ('Beijing','China');
INSERT INTO city VALUES ('London','UK');
There is no data on where the 2032 games will be held. The LEFT JOIN will include a row for 2032 even though it has no corresponding city.
SELECT games.yr, city.country
FROM scott.games LEFT JOIN scott.city
ON (games.city = city.name)
SELECT games.yr, city.country
FROM games LEFT JOIN city
ON (games.city = city.name)
See also