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.

inner join