Difference between revisions of "LEFT JOIN"

From SQLZOO
Jump to: navigation, search
(Created page with " <h1>The SELECT .. LEFT JOIN statement</h1> <table class='NoBorder'><td><tr> The LEFT JOIN will include rows from the left table even when the linking value is null. </td><td>...")
 
Line 10: Line 10:
 
<tr><td>2008</td><td align='left'>Beijing</td></tr>
 
<tr><td>2008</td><td align='left'>Beijing</td></tr>
 
<tr><td>2012</td><td align='left'>London</td></tr>
 
<tr><td>2012</td><td align='left'>London</td></tr>
<tr><td>2032</td><td align='left'>null</td></tr>
+
<tr><td>2032</td><td align='left'>ADITYA</td></tr>
 
</table>
 
</table>
 
</td><td>
 
</td><td>

Revision as of 11:36, 12 March 2013

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
2032ADITYA
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

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense