SQL uses aliases. These are alternative names for particular tables in a query. Joining a table with itself is a self-join. There are two advantages to aliases:

• Queries referencing tables with long names can use shortened names for the tables.
• Self-joins are not possible without aliases.

The convention to achieve this is by placing a letter after the table names in the FROM clause. Subsequent reference to this table in the query is via this letter:

SELECT a.name, capital, population 
FROM s_country a, s_city b
WHERE capital = b.name;

However, the need for clarity may supercede the desire for brevity. In such cases it may be necessary to use a more descriptive alias.

Inner and outer joins

Inner joins have been covered by the above. An alternative syntax exists:

SELECT s_contry.name, capital, population 
FROM s_country, INNER JOIN s_city 
ON capital = s_city.name;

Inner joins work where tables have rows that are matched. In the situation of rows from tables that do not match it is necessary to use outer joins. Otherwise the unmatched rows will vanish from the resulting table.

The use of outer joins ensure that data is not lost from unmatched rows.

The table that we require to preserve unmatched rows can be specified:

SELECT student.student_id, name, phone_no
FROM student LEFT OUTER JOIN telephone
ON  student.student_id = telephone.student_id;

The keyword ON is necessary instead of WHERE, because the keyword JOIN has been used.

Logical processing:

• The FROM clause produces an intermediate table, a cartesian product of student and telephone
• ON applies the join condition to the rows of the Cartesian product starting a second intermediate table
• A row is added to complete the FROM clause where the JOIN condition is not satisfied. Adds NULL for entries from the telephone table
• SELECT produces final table containing specified columns

A RIGHT OUTER JOIN adds null entries to preserve rows originating in the right table. A full outer join preserves rows from both tables by combining left and right outer joins.

Natural joins

SELECT student.student_id, name, phone_no
FROM student LEFT OUTER JOIN telephone
ON  student.student_id = telephone.student_id;

In the above example the matching columns have identical names i.e. student.student_id = telephone.student_id. SQL can achieve such joins where columns in tables have the same name by use of the NATURAL JOIN:

SELECT student.student_id, name, phone_no
FROM student NATURAL JOIN telephone;

The NATURAL JOIN is an inner join.

Comments, suggestions, ideas to
Stuart Banner