Oracle Inner Joins

So far, all the joins we have seen have been inner joins. Inner joins only return rows which satisfy the join condition.

A Simple Inner Join

This is best illustrated with a simple example using the CONTINENT and COUNTRY tables:

SELECT *
FROM continent;

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

Even though there are seven continents, when we join them to the COUNTRY table, any continents with no matching countries are missing from the result set.

SELECT cont_name, ctry_name
FROM continent
  JOIN country USING (cont_id);

CONT_NAME	CTRY_NAME
Africa	Gabon
Africa	Gambia
Africa	Ghana
Europe	Georgia
Europe	Germany
Europe	Greece
Oceania	Australia
Oceania	New Zealand

Antarctica, Asia, North America and South America are not returned because they do not have any matching rows in the COUNTRY table and, therefore, do not satisfy the join condition.

This is the expected behavior for an inner join; both tables in a join must satisfy the join condition if a row is to be returned in the result set. Later, we see how outer joins can be used to return all the rows from one of the tables even if there are no matching rows on the other side of the join.

Incidentally, the INNER keyword can be added to the JOIN clause to show that an inner join is being used but, as this is the most common type of join and therefore the default, it is not necessary to do so.

SELECT cont_name, ctry_name
FROM continent
  INNER JOIN country USING (cont_id);

The ON Clause

The USING clause in a JOIN is a concise way to speciy a join condition when two columns with the same name are being compared for equality. However, if the column names are different or a more complex join condition is required, then we must use the ON clause instead.

For example, how can we find any cities that are larger than a country? We can use this SQL:

SELECT city_name, city_area, ctry_name, ctry_area
FROM country
  JOIN city ON city_area > ctry_area;

Which produces this result:

CITY_NAME	CITY_AREA	CTRY_NAME	CTRY_AREA
Sydney	4689	Gambia	4361

We can include the logical operators, AND and OR, for more complex conditions in the ON clause. Here we return cities which have a population that is greater than ten percent of the countries entire total.

SELECT city_name, city_pop, ctry_name, ctry_pop
FROM country
  JOIN city ON city_pop > ctry_pop * 0.1
            AND country.ctry_id = city.ctry_id;

CITY_NAME	CITY_POP	CTRY_NAME	CTRY_POP
Sydney	4399722	Australia	21884000
Melbourne	3892419	Australia	21884000
Tbilisi	1382900	Georgia	4382100
Auckland	1313200	New Zealand	4382100

We match the CTRY_ID columns to ensure that we are comparing a cities population against the population of the country it is in, and not just any country. We are forced to qualify the CTRY_ID column with the name of the owning table, to avoid any ambiguity.

A Complex Inner Join

Let's finish up with an example that uses many of the techniques we have discovered in this section.

This rather contrived example finds any cities in countries that do not use the Euro as their currency, that have a population greater than one percent of the country total and have less than or the same number of letters in the name as the continent they are in (LENGTH() returns the length of a string).

SELECT city_name, ctry_name, cont_name
FROM continent
  JOIN country ctry USING (cont_id)
  JOIN city ON city_pop > ctry_pop * .01
            AND ctry.ctry_id = city.ctry_id
            AND length(city_name) <= length(cont_name)
WHERE currency != 'Euro';

CITY_NAME	CTRY_NAME	CONT_NAME
Batumi	Georgia	Europe
Perth	Australia	Oceania
Sydney	Australia	Oceania

CTRY_ID is qualified in the ON clause using a table alias called ctry. A table alias is specified after a table name in the FROM clause and is used to provide an alternative name for a table in the rest of the query.

This query shows us several things.

Many tables can be joined together, three in this case but many more can be handled by Oracle.
USING and ON can both be used in the same FROM clause.
Complex conditions using logical operators can be specified in the ON caluse.
The join condition can reference several tables. CITY is joined to COUNTRY and CONTINENT in the ON clause.
When two or more columns share the same name, we need to qualify them to avoid any ambiguity.
When qualifying columns, we can use the table name or a table alias.
Join conditions are specified in the FROM clause and and non-join conditions in the WHERE clause.

outer join