LEFT JOIN

The SELECT .. LEFT JOIN statement

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

games yr city 2004 Athens 2008 Beijing 2012 London 2032	city name country Sydney Australia Athens Greece Beijing China London UK

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

• LEFT and RIGHT JOIN Tutorial
• SELECT JOIN