Selecting Data From Multiple Tables: SQL JOINS
This lesson covers perhaps the most important element of the SQL language: the SQL JOIN
operation. As you know, a database stores data in several tables. When you need to combine data from multiple tables you do a JOIN between the tables where the data is stored.
This article presents examples using the SQL JOIN
clause.
Sample Tables
There are two sample tables: tours
and cities
.
Table tours
Tour_Name | Best_Season | Price | Duration (days) |
---|---|---|---|
United States and Canada | March to Sep | 3600 | 8 |
United States Big Cities | All year | 4300 | 12 |
Africa Tour | All year | 2100 | 7 |
Brazil Beaches | Dec to March | 1800 | 8 |
Table cities
City_Name | Type_of_City | Tour_Name | Days Staging |
---|---|---|---|
Washington | culture | United States and Canada | 5 |
Washington | culture | United States Big Cities | 5 |
Cairo | culture | Africa Tour | 4 |
Rio de Janeiro | beach | Brazil Beaches | 4 |
Johannesburg | safari | Africa Tour | 3 |
Florianópolis | beach | Brazil Beaches | 4 |
Quebec | culture | United States and Canada | 3 |
New York | culture | United States Big Cities | 7 |
To use the JOIN
clause to combine the two tables, there must be a shared column appearing in both tables. In this database, the column is tour_name
. It is easy to identify the shared column because it has the same name on both tables. In other databases, you have to look at the values as the shared column can have different names. The important thing is the values of the columns, as the JOIN operator creates pairs of records for those records having the same value on the shared column.
Two types of JOIN
operators are presented: INNER JOIN
and LEFT OUTER JOIN
.
SQL INNER JOIN Operator
Here is a simple SQL JOIN
query to obtain the pairs:
SELECT
tour_name,
best_season,
price,
city_name,
type_of_city,
days_staging
FROM tours INNER JOIN cities ON tours.tour_name = cities.tour_name
How is a pair of records created by the SQL INNER JOIN
operator?. For each record on the first table (tours
), every record on the second table (cities
) having the same value in the columns specified in the ON clause (tour_name
) is paired with the record from the first table.
Here is the result:
tour_name | best_season | price | city_name | type_of city | days_staging |
---|---|---|---|---|---|
United States Canada | March to Sep | 3600 | Quebec | culture | 3 |
United States Canada | March to Sep | 3600 | Washington | culture | 5 |
United States Big Cities | All year | 4300 | New York | culture | 7 |
United States Big Cities | All year | 4300 | Washington | culture | 5 |
Africa Tour | All year | 2100 | Johannesburg | safari | 3 |
Africa Tour | All year | 2100 | Cairo | safari | 4 |
Brazil Beaches | Dec to March | 1800 | Florianópolis | beach | 4 |
Brazil Beaches | Dec to March | 1800 | Rio de Janeiro | beach | 4 |
Suppose you want to know what tours are available for a culture city for less than $ 3000. Here is the query:
SELECT
tour_name,
price
FROM tours INNER JOIN cities ON tours.tour_name = cities.tour_name
WHERE price < 3000
AND type_of_city = ‘culture’
The result is:
tour_name | price |
---|---|
Africa Tour | |
2100.00 |
SQL OUTER JOIN Operator
In some cases a record in the first table does not have a counterpart record in the second table in order to form a pair. For example, there could be a tour_name
not related with any city, perhaps because it is a tour to the jungle or a one week sailing trip.
Here is a new version of the table tours
:
Tour_Name | Best_Season | Price | Duration (days) |
---|---|---|---|
United States and Canada | March to Sep | 3600 | 8 |
United States Big Cities | All year | 4300 | 12 |
Africa Tour | All year | 2100 | 7 |
Brazil Beaches | Dec to March | 1800 | 8 |
Caribbean: a sailing week | May to October | 5300 | 7 |
5 days in the Amazonas Jungle | All year | 900 | 5 |
If you want to obtain a list of the available tours, you can execute the first INNER JOIN
again:
SELECT
tour_name,
best_season,
price,
city_name,
type_of_city,
days_staging
FROM tours INNER JOIN cities ON tours.tour_name = cities.tour_name
The tours Caribbean: a sailing week and 5 days in the Amazonas Jungle are not returned in the results as there is no associated city.
tour_name | best_season | price | city_name | type_of_city | days_staging |
---|---|---|---|---|---|
United States Canada | March to Sep | 3600 | Quebec | culture | 3 |
United States Canada | March to Sep | 3600 | Washington | culture | 5 |
United States Big Cities | All year | 4300 | New York | culture | 7 |
United States Big Cities | All year | 4300 | Washington | culture | 5 |
Africa Tour | All year | 2100 | Johannesburg | safari | 3 |
Africa Tour | All year | 2100 | Cairo | safari | 4 |
Brazil Beaches | Dec to March | 1800 | Florianópolis | beach | 4 |
Brazil Beaches | Dec to March | 1800 | Rio de Janeiro | beach | 4 |
To include the other tours, you use the SQL LEFT OUTER JOIN
. The SQL LEFT OUTER JOIN
is does not discard a row with no counterpart on the second table.
Here is the query:
SELECT
tour_name,
best_season,
price,
city_name,
type_of_city,
days_staging
FROM tours LEFT OUTER JOIN cities ON tours.tour_name = cities.tour_name
In the results, you can see the tours without cites are included. The values of the columns from the second table are NULL
, as this is the normal behavior of the SQL LEFT OUTER JOIN
operator.
tour_name | best_season | price | city_name | type_of city | days_staging |
---|---|---|---|---|---|
United States Canada | March to Sep | 3600 | Quebec | culture | 3 |
United States Canada | March to Sep | 3600 | Washington | culture | 5 |
United States Big Cities | All year | 4300 | New York | culture | 7 |
United States Big Cities | All year | 4300 | Washington | culture | 5 |
Africa Tour | All year | 2100 | Johannesburg | safari | 3 |
Africa Tour | All year | 2100 | Cairo | safari | 4 |
Brazil Beaches | Dec to March | 1800 | Florianópolis | beach | 4 |
Brazil Beaches | Dec to March | 1800 | Rio de Janeiro | beach | 4 |
Caribbean: a sailing week | May to Oct | 5300 | |||
5 days in the Amazonas Jungle | All year | 900 |
Closing Words
In this lesson you learned two of the SQL JOIN
operators INNER JOIN
and LEFT OUTER JOIN
. There are other less frequently used JOIN
operators you can explore now that you understand the basics. Keep going, learn SQL and increase your skills!