Difference between revisions of "SELECT .. JOIN"
From SQLZOO
| (One intermediate revision by one user not shown) | |
Revision as of 01:50, 5 August 2012
The SELECT .. JOIN statement
|
|
schema:scott
DROP TABLE games; DROP TABLE city
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');
You can use a JOIN to get results from two or more related tables.
In this example each row of the table games is related to a row of the table city.
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
SELECT games.yr, city.country FROM scott.games JOIN scott.city ON (games.city = city.name)
SELECT games.yr, city.country FROM games JOIN city ON (games.city = city.name)
See also
