SQL allows for multiple table query processing using the FROM clause. A join is the combination of data from two related columns from two tables.

A join is a refinement of a general combination of data from two tables known as a Cartesian product. The Cartesian product is another table containing all possible combinations of the pairs of rows from the tables.

SELECT *
From s_country, s_city;

Logical processing is as follows:

• An intermediate table is formed by the FROM clause containing all columns of both tables. This intermediate table is the Cartesian product.
• Processing SELECT uses all columns to produce the final table. A copy of the intermediate table.

Referencing a specific column in one table that has the same identifier as another column from the second table requires the use of dot notation:

s_country.name, s_country.capital, s_city.name, s_city.population

This would be required for the above table in the case of the two name columns. However there is no ambiguity about the population and capital columns. These can be referenced directly.

Relationships

As some of the entries in the columns are identical there is a possibilty of a relationship between the tables. The Cartesian product of the two tables can be used to join the tables based upon the relationship via a join condition.

The join condition is specified in a WHERE clause:

SELECT s_country.name, capital, population
From s_country, s_city
WHERE capital = s_city.name;

Logical processing is as follows:

• An intermediate table is formed by the FROM clause containing all columns of both tables. This intermediate table is the Cartesian product of s_country and s_city.
• Processing the WHERE clause applies the join condition to rows that satisfy the condition to produce a second intermediate table.
• Processing SELECT uses the condition satisfying columns to produce the final table.

It is helpful if join conditions precede other predicates in any query to help in distinguishing the join.

Comments, suggestions, ideas to
Stuart Banner