SQL Joins

When two tables are matched in a query, a join is made. The goal is to relate the data of one table with the data of another table. The result is to have the rows of two tables side by side.

For this to be feasible and for the relationship to be semantically valid, the tables in question must have a common column. Usually, this common column takes the form of a primary key in one table and a foreign key in the other table. To simplify things these two columns usually have the same name.

Joins can be established with operators such as “<” and “>”. However, the most common joins involve an equality comparison between a remote key and a primary key.

Joins from the Cartesian product

The Cartesian product between two tables is the association of each row of the first table with each row of the second table. This is produced when two tables are simply mentioned in the FROM clause.

SELECT * FROM product, class;

A pure Cartesian product is meaningless (ie it does not interpret). It is rather desirable to establish a relationship that is defined as a subset of the Cartesian product.

The desired relationship is based on a comparison condition between two columns (usually a primary key and a foreign key).

Thus, the only pairs of rows preserved will be those that respect the join condition.
The instruction takes the following form:

SELECT list of columns FROM list of tables WHERE condition of join between two
tables, [AND condition of join between two tables, …];

Note:
Since the comparison columns often have the same name, it is necessary to use the explicit names of the columns. When columns of the same name are present in different tables of the query, it is necessary to use the explicit names of the columns in any clause of the query where these columns are mentioned.

SELECT * FROM product, class WHERE product.class = class.class;

Explicit joins (INNER JOIN)

With the first SQL standard, joins from the Cartesian product was the only way to establish a join. Now, with the 1992 standard, it is possible to define joins explicitly in the FROM clause by using the JOIN keyword.

FROM table INNER JOIN table ON join condition

Associate with each product the name of the class to which it belongs.

SELECT product_code, name
FROM product INNER JOIN class
ON product.class = class.class;

“External” joins (OUTER JOIN)

The way to define a join implies that only the rows that satisfy the condition will be retained. Thus, any row of a table, for which the value of the join column can not find a similar value in the other table, will be rejected.

If you want to keep lines that do not respect the join condition, then you must establish an “outer” join. This will have the effect of keeping all the rows (those which nevertheless respect the other conditions of the WHERE clause) of a table in the result.

The space, normally occupied by one line of the other table, will be replaced by NULLs.

FROM table [LEFT | RIGHT] OUTER JOIN table ON join condition

A join that uses a LEFT OUTER JOIN will show all rows in the first table, whereas a join that uses a RIGHT OUTER JOIN will show all rows in the second table.

List the names of all employees with the project’s code they are responsible for (if
applicable).

SELECT name, project_code
FROM Leader LEFT OUTER JOIN Project
ON Leader.empno = Project.empno ORDER BY name;

Move to the next article: SQL Aggregate Calculations