Difference between revisions of "LEFT JOIN"

From SQLZOO
Jump to: navigation, search
(Undo revision 13425 by 117.218.241.7 (talk))
 
(2 intermediate revisions by 2 users not shown)
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'>ADITYA</td></tr>
+
<tr><td>2032</td><td align='left'></td></tr>
 
</table>
 
</table>
 
</td><td>
 
</td><td>

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.

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

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense