lishman levelup
«previous  next»


SQL Basics
Joins
Constraints



Oracle Join Concepts


First, let's add the following CONTINENT table to the database:

CONTINENT
CONT_ID CONT_NAME
1 Africa
2 Asia
3 Europe
4 North America
5 South America
6 Oceania
7 Antarctica

With our current table definitions there is no way to identify which continent a country is in. Therefore, we must alter the COUNTRY table to include the CONT_ID column:

COUNTRY
CTRY_ID CONT_ID CTRY_NAME CTRY_AREA CTRY_POP POP_UPD_ON CURRENCY
14 3 Germany 137882 82046000 30-nov-08 Euro
48 1 Ghana 92100 23837000 null Cedi
53 6 Australia 2969907 21884000 4-sep-09 Australian Dollar
73 3 Greece 50949 11257285 1-Jan-09 Euro
122 3 Georgia 26900 4382100 1-Jan-09 Lari
123 6 New Zealand 104428 4320300 4-sep-09 New Zealand Dollar
147 1 Gambia 4361 1705000 null Dalasi
149 1 Gabon 103347 1475000 null CFA franc

The CONT_ID column on the COUNTRY table contains the appropriate continent identifier for that row. For example, Australia and New Zealand have a CONT_ID of 6 which is the identifier on the CONTINENT table for Oceania. Similarly Germany, Greece and Georgia have a CONT_ID of 3 which is the identifier for Europe.

A Simple Join

Now suppose we need to find the name of the continent that Greece is in. We could fetch the CONT_ID for Greece like this:
SELECT cont_id
FROM country
WHERE ctry_name = 'Greece';
CONT_ID
3

Then retrieve the appropriate continent name for this identifier:
SELECT cont_name
FROM continent
WHERE cont_id = 3;
CONT_NAME
Europe

Or we could use a join and let the database figure out the result for us.
SELECT cont_name
FROM country
  JOIN continent USING (cont_id)
WHERE ctry_name = 'Greece';
CONT_NAME
Europe

The JOIN...USING clause tells the database to join the CONTINENT and COUNTRY tables together and return any rows where the CONT_ID column in each table contains the same value.

Behind the scenes, Oracle retrieves the COUNTRY row for Greece then accesses the row in the CONTINENT table with the same value in the CONT_ID column.

Multiple Rows

In the first join example, a single row is returned because a country can only exist in one continent. In other words, CONT_ID on the COUNTRY table can only point to one CONTINENT row. However if we execute this SQL statement:
SELECT ctry_name
FROM country
  JOIN continent USING (cont_id)
WHERE cont_name = 'Europe';
CTRY_NAME
Germany
Greece
Georgia

We see that multiple rows are returned, one for each country in Europe. A COUNTRY can only reference one CONTINENT but a CONTINENT can be referenced by many COUNTRYs.

Note that the ordering of the tables in the FROM clause is not significant. Switching CONTINENT and COUNTRY has no effect on the results.
SELECT ctry_name
FROM continent
  JOIN country USING (cont_id)
WHERE cont_name = 'Europe';
CTRY_NAME
Germany
Greece
Georgia

If the WHERE clause is omitted then all the countries will be matched up to the appropriate continents. Also, if the asterisk (*) is used in the SELECT clause then all the columns from all the tables in the FROM clause will be returned.
SELECT *
FROM continent
  JOIN country USING (cont_id);
CONT_ID CONT_NAME CTRY_ID CTRY_NAME CTRY_AREA CTRY_POP POP_UPD_ON CURRENCY
6 Oceania 53 Australia 2966200 21884000 04-SEP-09 Australian Dollar
1 Africa 149 Gabon 103347 1475000   CFA franc
1 Africa 147 Gambia 4361 1705000   Dalasi
3 Europe 122 Georgia 26900 4382100 01-JAN-09 Lari
3 Europe 14 Germany 137847 82046000 30-NOV-08 Euro
1 Africa 48 Ghana 92098 23837000   Cedi
3 Europe 73 Greece 50949 11257285 01-JAN-09 Euro
6 Oceania 123 New Zealand 104454 4320300 04-SEP-09 New Zealand Dollar

Multiple Joins

Let's extend our geographic model to include a few major cities.

CITY
CITY_ID CTRY_ID CITY_NAME CITY_AREA CITY_POP
1 53 Sydney 4689 4399722
2 53 Melbourne 3400 3892419
3 53 Brisbane 2279 1945639
4 53 Perth 2079 1602559
5 53 Adelaide 705 1172105
6 123 Auckland 419 1313200
7 123 Christchurch 550 368900
8 123 Wellington 171 381900
9 123 Hamilton 98 138500
10 123 Tauranga 64 110500
11 73 Athens 15 745514
12 73 Thessaloniki 7 763468
13 73 Piraeus 4 175697
14 73 Patras 48 171616
15 73 Heraklion 42 137711
16 14 Berlin 344 3396300
17 14 Hamburg 291 1731200
18 14 Munich 119 1241100
19 14 Cologne 156 969500
20 14 Frankfurt 95 646000
21 122 Tbilisi 280 1382900
22 122 Kutaisi 27 267300
23 122 Batumi (null) 144600
24 122 Rustavi 23 121786
25 122 Zugdidi (null) 104900

The CITY table includes a reference to the owning COUNTRY in the CTRY_ID column. This defines the relationship between countries and their cities.
SELECT city_name, ctry_name
FROM country
  JOIN city USING (ctry_id)
WHERE ctry_name IN ('Germany', 'Greece');
CITY_NAME CTRY_NAME
Athens Greece
Berlin Germany
Cologne Germany
Frankfurt Germany
Hamburg Germany
Heraklion Greece
Munich Germany
Patras Greece
Piraeus Greece
Thessaloniki Greece

This allows us to demonstrate that multiple joins can be used in a single SQL query. Here we display all the cities in Oceania and which countries they are in:
SELECT city_name, ctry_name
FROM continent
  JOIN country USING (cont_id)
  JOIN city USING (ctry_id)
WHERE cont_name = 'Oceania';
CITY_NAME CTRY_NAME
Adelaide Australia
Auckland New Zealand
Brisbane Australia
Christchurch New Zealand
Hamilton New Zealand
Melbourne Australia
Perth Australia
Sydney Australia
Tauranga New Zealand
Wellington New Zealand

We are required to join to the CONTINENT table because we need to include only those countries in Oceania. If, however, we have access to the identifier of Oceania rather than the name (which is often the case in an application) we could omit the join to the CONTINENT table and re-write the query like this:
SELECT city_name, ctry_name
FROM country
  JOIN city USING (ctry_id)
WHERE cont_id = 6;
Reducing the number of joins in a query tends to have a positive impact on performance.
»