Selecting data from multiple tables

So far we’ve only looked at examples of selecting data from a single table; however we can retrieve data from multiple tables with a single SQL query. To illustrate this we will introduce a new table called Cities, which looks like this:

City Country
Toronto Canada
Ottawa Canada
Vancouver Canada
Los Angeles USA
New York USA
San Francisco USA
Mexico Mexico
Rome Italy
Venice Italy
London UK
Manchester UK
Leeds UK

If we want to select all cities in North America from the Countries table, we need to select data from both the Countries and the Cities tables. Here is how to do that:

SELECT Countries.Continent, Cities.City
FROM Countries, Cities
WHERE Countries.Country = Cities.Country AND Countries.Continent = ‘North America’

At first this statement might look complicated to you, so I’ll try to explain it in details.
The first line is the SELECT part of the statement, followed by 2 column names, which are prefixed with their respective table names for clarity. These are the 2 columns (each one of them is from a different table) we are selecting. The second line simply specifies which tables we are selecting the data from (the tables are separated by commas). The third line does the matching between the tables. The matching is done by a column common for both tables – the Country column. The second part of the WHERE clause restricts the results to only Countries, which have a Continent value of ‘North America’.

The result of this statement is below:

Continent City
North America Toronto
North America Ottawa
North America Vancouver
North America Los Angeles
North America New York
North America San Francisco
North America Mexico

If we tried to select all North American cities only from the cities table, we wouldn’t be able to do that simply, because the Cities table doesn’t have a Continent column. The same is valid for the Countries table, because this table doesn’t have City column. Thus we had to join the tables on the common column (both tables have a column called Country).

Our SELECT statement can be rewritten in the following way using the JOIN SQL keyword:

SELECT Countries.Continent, Cities.City
FROM Countries JOIN Cities
ON Countries.Country = Cities.Country
WHERE Countries.Continent = ‘North America’

This statement does the same as the first one, but with most SQL implementations is faster compared to the first one. The JOIN clause specifies which tables we are joining and the ON keyword tells the SQL engine on which columns to do the join (matching).