lishman levelup
«previous  next»


SQL Basics
Joins
Constraints



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.
»