Difference between revisions of "LEFT JOIN"

From SQLZOO
Jump to: navigation, search
(gvhh)
Line 1: Line 1:
  
<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>
 
<table style='float:left' class='Bordered'>
 
<caption align='center'>'''games'''</caption>
 
<tr><th align='center'>'''yr'''</th><th align='center'>'''city'''</th></tr>
 
<tr><td>2004</td><td align='left'>Athens</td></tr>
 
<tr><td>2008</td><td align='left'>Beijing</td></tr>
 
<tr><td>2012</td><td align='left'>London</td></tr>
 
<tr><td>2032</td><td align='left'></td></tr>
 
</table>
 
</td><td>
 
<table style='float:left' class='Bordered'>
 
<caption>'''city'''</caption>
 
<tr><th align='center'>'''name'''</th><th align='center'>'''country'''</th></tr>
 
<tr><td align='left'>Sydney</td><td align='left'>Australia</td></tr>
 
<tr><td align='left'>Athens</td><td align='left'>Greece</td></tr>
 
<tr><td align='left'>Beijing</td><td align='left'>China</td></tr>
 
<tr><td align='left'>London</td><td align='left'>UK</td></tr>
 
</table>
 
</td></table>
 
 
<div class='ht'>
 
<div class=params>schema:scott</div>
 
<source lang=sql class='tidy'> DROP TABLE games;
 
DROP TABLE city</source>
 
<source lang=sql class='setup'> 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');
 
</source>
 
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.
 
<source lang='sql' class='def e-oracle'>
 
SELECT games.yr, city.country
 
  FROM scott.games LEFT JOIN scott.city
 
      ON (games.city = city.name)
 
</source>
 
<source lang='sql' class='def'>
 
SELECT games.yr, city.country
 
  FROM games LEFT JOIN city
 
      ON (games.city = city.name)
 
</source>
 
</div>
 
 
<p>See also</p>
 
<ul>
 
  <li>[[Using_Null |LEFT and RIGHT JOIN Tutorial]]</li>
 
  <li>[[SELECT_.._JOIN |SELECT JOIN]]</li>
 
</ul>
 

Revision as of 12:21, 16 December 2013

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense