Difference between revisions of "LEFT JOIN"
Jump to navigation Jump to search
|(One intermediate revision by one other user not shown)|
Latest revision as of 15:43, 6 January 2014
The SELECT .. LEFT JOIN statement
The LEFT JOIN will include rows from the left table even when the linking value is null.
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)