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:
After FROM, mention the tables that you want to join, separated by a comma.
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
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
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 ;
Select all the treasures. Display title, year, and provide a full description
of the subject code
Select all the libraries. Display the names of the libraries and the names
of the cities in which they are located
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
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