SELECT .. JOIN - Revision history

194.210.194.135 at 23:04, 8 February 2013

2013-02-08T23:04:19Z

194.210.194.135 at 22:48, 8 February 2013

2013-02-08T22:48:58Z

194.210.194.135 at 22:47, 8 February 2013

2013-02-08T22:47:49Z

194.210.194.135 at 22:46, 8 February 2013

2013-02-08T22:46:40Z

194.210.194.135 at 22:44, 8 February 2013

2013-02-08T22:44:53Z

194.210.194.135 at 22:35, 8 February 2013

2013-02-08T22:35:13Z

Marcin: Undo revision 3091 by Marcin (talk)

2012-08-05T01:50:23Z

Undo revision 3091 by Marcin (talk)

Marcin at 01:49, 5 August 2012

2012-08-05T01:49:54Z

Marcin at 01:49, 5 August 2012

2012-08-05T01:49:07Z

Connor: Created page with "

The SELECT .. JOIN statement

Sometimes you need to access two or more tables to get the data required.

Sometimes you need to access two or more tables to get the data required.
</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>1896</td><td align='left'>Athens</td></tr>
<tr><td>1948</td><td align='left'>London</td></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>
</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 (1896,'Athens');
INSERT INTO games VALUES (1948,'London');
INSERT INTO games VALUES (2004,'Athens');
INSERT INTO games VALUES (2008,'Beijing');
INSERT INTO games VALUES (2012,'London');
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>
You can use a JOIN to get results from two or more related tables.
In this example each row of the table <code>games</code> is related to a row of the table <code>city</code>.
If you want to find the country where the games took place you must JOIN the games table to the city table on the common field that is games.city and city.name
<source lang='sql' class='def e-oracle'>
SELECT games.yr, city.country
FROM scott.games JOIN scott.city
ON (games.city = city.name)
</source>
<source lang='sql' class='def'>
SELECT games.yr, city.country
FROM games JOIN city
ON (games.city = city.name)
</source>
</div>

<p>See also</p>
<ul>
<li>[[Music_Tutorial |JOIN Tutorial]]</li>
<li>[[More_JOIN_operations |Another JOIN Tutorial]]</li>
<li>[[LEFT_JOIN |SELECT .. LEFT JOIN]]</li>
</ul>

@@ Line 26: / Line 26: @@
 <caption align='center'>'''eloix'''</caption>
 <tr><th align='center'>'''name'''</th><th align='center'>'''cod'''</th></tr>
-<tr><td>Txadinha</td><td align='left'>1</td></tr>
+<tr><td>1</td><td align='left'>Txadinha</td></tr>
-<tr><td>Sao Filipe</td><td align='left'>2</td></tr>
+<tr><td>2</td><td align='left'>Sao Filipe</td></tr>
-<tr><td>Fazenda</td><td align='left'>3</td></tr>
+<tr><td>3</td><td align='left'>Fazenda</td></tr>
-<tr><td>London</td><td align='left'>4</td></tr>
+<tr><td>4</td><td align='left'>London</td></tr>
 </table>
 </td></table>
@@ Line 55: / Line 55: @@
     name VARCHAR(20),
     cod int);
-INSERT INTO eloix VALUES ('Txadinha',1);
+INSERT INTO eloix VALUES (1,'Txadinha');
-INSERT INTO eloix VALUES ('Sao Filipe',2);
+INSERT INTO eloix VALUES (2,'Sao Filipe');
-INSERT INTO eloix VALUES ('Fazenda',3);
+INSERT INTO eloix VALUES (3,'Fazenda');
-INSERT INTO eloix VALUES ('Plateau',4);
+INSERT INTO eloix VALUES (4,'London');
 </source>
 You can use a JOIN to get results from two or more related tables.

@@ Line 29: / Line 29: @@
 <tr><td>Sao Filipe</td><td align='left'>2</td></tr>
 <tr><td>Fazenda</td><td align='left'>3</td></tr>
-<tr><td>Plateau</td><td align='left'>4</td></tr>
+<tr><td>London</td><td align='left'>4</td></tr>
 </table>
 </td></table>

@@ Line 26: / Line 26: @@
 <caption align='center'>'''eloix'''</caption>
 <tr><th align='center'>'''name'''</th><th align='center'>'''cod'''</th></tr>
-<tr><td>Txadinha</td><td align=1>Athens</td></tr>
+<tr><td>Txadinha</td><td align='left'>1</td></tr>
-<tr><td>Sao Filipe</td><td align=2>London</td></tr>
+<tr><td>Sao Filipe</td><td align='left'>2</td></tr>
-<tr><td>Fazenda</td><td align=3>Athens</td></tr>
+<tr><td>Fazenda</td><td align='left'>3</td></tr>
-<tr><td>Plateau</td><td align=4>Beijing</td></tr>
+<tr><td>Plateau</td><td align='left'>4</td></tr>
 </table>
 </td></table>

@@ Line 26: / Line 26: @@
 <caption align='center'>'''eloix'''</caption>
 <tr><th align='center'>'''name'''</th><th align='center'>'''cod'''</th></tr>
-<tr><td>'Txadinha'</td><td align=1>Athens</td></tr>
+<tr><td>Txadinha</td><td align=1>Athens</td></tr>
-<tr><td>'Sao Filipe'</td><td align=2>London</td></tr>
+<tr><td>Sao Filipe</td><td align=2>London</td></tr>
-<tr><td>'Fazenda'</td><td align=3>Athens</td></tr>
+<tr><td>Fazenda</td><td align=3>Athens</td></tr>
-<tr><td>'Plateau'</td><td align=4>Beijing</td></tr>
+<tr><td>Plateau</td><td align=4>Beijing</td></tr>
 </table>
 </td></table>

@@ Line 21: / Line 21: @@
 <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>

@@ Line 43: / Line 43: @@
 INSERT INTO city VALUES ('Beijing','China');
 INSERT INTO city VALUES ('London','UK');
 </source>
 You can use a JOIN to get results from two or more related tables.

@@ Line 4: / Line 4: @@
 Sometimes you need to access two or more tables to get the data required.
 </td><td>
-<table style='float:left' class='NoBorder'>
+<table style='float:left' class='Bordered'>
 <caption align='center'>'''games'''</caption>
 <tr><th align='center'>'''yr'''</th><th align='center'>'''city'''</th></tr>

@@ Line 58: / Line 58: @@
 </source>
 </div>
 <p>See also</p>