SQL: joins

Join

This is an essential operation when you manage databases and allow you to link tables together, 2 by 2. The names of the relevant tables must be indicated after the FROM clause. In the WHERE clause, you can set the conditions to perform the join.

Basic notions of Alias.

An alias is used to rename a column or a table. This operation allows you to give a column a "quicker" name. It is also used when you want to manipulate a table more easily, in particular when there are different joining conditions. Naming a table as an alias is needed if you want to perform a self-join.

Alias of column

Syntax: Col1 AS "Column name".
In the result of the corresponding query, Col1 will be changed to "Column name". Quotation marks are only required when an alias name has spaces.

Alias of table

Syntax: Table1 Alias1
In a query, Alias1 will be the same as Table1

Equi-join

This operation enables you to connect, with an equal operator, tables that have at least one common attribute. We must have n-1 conditions to reach, n being the number of tables which intervene in the query. If no condition in the join is specified, the corresponding request will perform the Cartesian product of the implied tables.

-Name and gender description of each student.

SELECT Nometu AS "Student Name", Lbsexe AS Sex
FROM ETUDIANT E, SEXE S
WHERE E.Cdsexe=S.Cdsexe;

In this query, the join is done on the common attribute (Cdsexe) between the tables ETUDIANT and SEXE which have the respective aliases E and S. Since the join attribute (Cdsexe) has the same name in both tables, you must prefix it with the name of the table to which it is connected.

Test queries examples

SQL summary

Q05: Display the mark obtained by the student "Gros, Sophie" in the subject number 2.

SQL summary

Self-join

It is the joint of a table with itself. This kind of jointure is useful when you want to connect attributes that are in the same table.

-Display the number of students whose mark on the subject number 1 is lower than the mark than they obtained on the subject number 2.

SELECT N1. Numetu
FROM NOTES N1, NOTES N2
WHERE N1.Numetu=N2.Numetu
  AND N1.Numat=1
  AND N2.Numat=2
  AND N1.Note < N2.Note;

Q06: Display the names of students who have a mark on Subject 1 below their mark on Subject 2.

SQL summary

Theta-join

This operation, which should be used with care, enables the connection of two tables when the table junction column operator is not the equal sign. The operator of comparison can be: greater (>), lower (<), greater or equal (> =), lower or equal (< =), not equal (< >).

- Display, for each student, how much are before him (her) on the alphabetical list of names.

SELECT E1.Nometu, COUNT(*)
FROM ETUDIANT E1, ETUDIANT E2
WHERE E1.Nometu>E2.Nometu
GROUP BY E1.Nometu
ORDER BY E1.Nometu;

This query gives a correct result for all students, with the exception of the first one in the alphabetical list that is not displayed. This error can be avoided by using an outer joint.

Outer-Joins

A "standard" join between 2 tables, or inner join, will only return rows if the join column in one table is equal to the join column in the other table. It may be helpful, under certain circumstances, to display all the rows of a particular table, whether or not it corresponds to the other table. Columns which do not correspond are filled with the NULL value. This operation is called an outer join.

There are 3 types of outer joins and the way an outer join is executed depends on the position of the tables relative to the join statement.

Syntax, in SQL-92, is the following one:

a) Left outer join

SELECT ...
FROM table1 [alias]
LEFT OUTER JOIN table2 [alias]
ON <Join conditions>
...

All rows in Table 1 will be displayed even if < Join Conditions > is not completed in Table 2. 
The next query displays all students, with or without a mark. If a student does not have a mark, the field Numetu of the corresponding table (NOTES) will have the value NULL.

SELECT DISTINCT(E.Nometu), N.Numetu
FROM ETUDIANT E
LEFT OUTER JOIN NOTES N
ON E.Numetu=N.Numetu;

b) Right outer join

SELECT ...
FROM table1 [alias]
RIGHT OUTER JOIN table2 [alias]
ON <Join conditions>
...

All rows in Table 2 will be displayed even if < Join Conditions > is not completed in Table 1.

The following query displays all students with one or more marks.

SELECT DISTINCT(E.Nometu), N.Numetu
FROM ETUDIANT E
RIGHT OUTER JOIN NOTES N
ON E.Numetu=N.Numetu;

c) Full outer join

SELECT ...
FROM table1 [alias]
FULL OUTER JOIN table2 [alias]
ON <Join conditions>
...

All the rows of table1 and table2 will be displayed and the columns for which there is no matching will be filled with value NULL.

Here, below, two possible uses of outer joints.

- Display the names of students that have no marks.

SELECT DISTINCT(E.Nometu), N.Numetu
FROM ETUDIANT E
LEFT OUTER JOIN NOTES N
ON E.Numetu=N.Numetu
WHERE N.Numetu is NULL;

- Display, for each student, how many are before him (her) in the alphabetical list of names.

SELECT E1.Nometu, COUNT(E2.Numetu)
FROM ETUDIANT E1
LEFT OUTER JOIN ETUDIANT E2
ON E1.Nometu>E2.Nometu
GROUP BY E1.Nometu
ORDER BY E1.Nometu;

Test queries examples

Next section | Previous section | SQL summary