SELECT .. JOIN
(Redirected from JOIN)
The SELECT .. JOIN statement
Sometimes you need to access two or more tables to get the data required.
|
|
schema:scott
DROP TABLE games CASCADE;
DROP TABLE city CASCADE
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