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