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

Table cities

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:

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:

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:

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.

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.

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!