SQL Tutorial

Previous  |  Home  |  Next

INNER JOIN

The queries that have been discussed so far have all dealt with one table only. Nevertheless, the same principles also work for queries from two tables. In SQL, it is also possible to query multiple tables simultaneously. If you want to combine the data from two or more tables, you need to consider the way in which these two tables are related. In the vast majority of the cases, the relationship between two tables is based on the fact that a primary key in one table occurs as a foreign key in the related table. If this is indeed the case, you can make use of a so-called inner join.

Creating an inner join consists of two actions:

  1. After FROM, mention the tables that you want to join, separated by a comma.
  2. After WHERE, explain the way in which these table are related. Normally, you will use the following formula: FK = PK.

Suppose that you were asked to make a list of all the treasures which contains not only the titles and the year, but also the full names of their creators. You need to extract data from different tables, as the information on the treasure can be found in TREASURE, but the latter two data items can be found in the table CREATOR. To make such a list, you need to join different tables. In the FROM clause, you need to mention the names of these two tables. In the WHERE clause, you need to explain that the column "creator" in the table TREASURE (a foreign key) refers to the primary key of the table CREATOR ("creator_id"). The following query will return the title, the year and the full name of the creator:

SELECT TITLE, YEAR, NAME_FIRST, NAME_LAST
FROM TREASURE, CREATOR
WHERE CREATOR = CREATOR_ID ;

It may be the case that the name of a column occurs in more than one table. In this situation, you need to avoid ambiguities by mentioning the name of the table as well as the name of the column, as follows: CREATOR.NAME_FIRST.

It is also possible to link more than two tables. When you add a table to the list after FROM, you shoudl also add a statement to the WHERE clause. The number of linking criteria that you will need will be the number of joined tables minus one.

The following query returns the title, the year, the full name of the creator and the full name of the country of birth of all the treasures.

SELECT TITLE, YEAR, NAME_FIRST, NAME_LAST, COUNTRY_BORN
FROM TREASURE, CREATOR, COUNTRY
WHERE CREATOR = CREATOR_ID AND COUNTRY_CODE = COUNTRY_BORN ;


Exercises

  1. Select all the treasures. Display title, year, and provide a full description of the subject code
  2. Select all the libraries. Display the names of the libraries and the names of the cities in which they are located
  3. Produce a list of all the cities. Provide the names of thes cities and the full names of the countries in which they are situated
  4. Select all the treasures. Display title, year, and a full description of the subject code, and the full names of the creators. Sort the list alphabetically by the last name of the creator