LEFT JOIN

From SQLZoo
Jump to navigation Jump to search

The SELECT .. LEFT JOIN statement

The LEFT JOIN will include rows from the left table even when the linking value is null.

games
yrcity
2004Athens
2008Beijing
2012London
2032
city
namecountry
SydneyAustralia
AthensGreece
BeijingChina
LondonUK
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

DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects