Applications of Databases to Humanities and Social Sciences

SQL: joins

Join

It is a fundamental operation when one handles databases and which allows to link together, 2 by 2, tables. It is necessary to specify, after the FROM clause, the name of the tables which will intervene and, after the WHERE clause, the conditions which are going to realize the join.

Basics of alias

An alias is used to rename a column or a table. This operation is useful when one wants to give a more "vivid" name to a column a or when one wants to handle a table more easily in particular when there are various conditions of join. Giving a name of alias to a table is mandatory in the event of self-joins.

Alias of column

Syntax : Col1 AS "Column name".
In the result of the corresponding query, Col1 will be replaced by "Column name". The quotation marks are mandatory only if the name of alias comprises spaces.

Alias of table

Syntax : Table1 Alias1
In a query, Alias1 will be identical to Table1

Equi-join

This operation allows to connect, with a relation of equality, the tables which have at least a common attribute. One must have n-1 conditions of join, n being the number of tables which intervene in the query.
If no condition of join is specified, the corresponding query will realize the Cartesian product of the implied tables.

-Name and wording of the sex 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 realized on the common attribute (Cdsexe) between the tables ETUDIANT and SEXE which have for respective aliases E and S. The join attribute (Cdsexe) having the same name in the 2 tables, it is necessary to prefix it by the name of the table to which it is connected.

SQL summary

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

SQL summary

Self-join

It is the join of a table with itself. This operation is useful when one wishes to connect attributes which are inside the same table.

-Display the number of the students who have a mark in the subject number 1 lower than the mark than they obtained in 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 Name of the students who have a mark in the subject number 1 lower than the mark they obtained in the subject number 2.

SQL summary

Theta join

This operation, which must be used carefully, allows to connect two tables when the column of join of a table is not connected with the column of join of the other table by 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 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 the students, except for the first of the alphabetical list which does not appear. This error can be avoided by using, among others, an outer join.

Outer joins

A "standard" join between 2 tables, or inner join, returns rows only if the column of join of a table is equal to the column of join of the other table. It can be useful, in certain circumstances, to display all the rows of a particular table if there is or not matching with the other table. The columns for which there is no matching are filled with the value NULL. This operation is called an outer join.

There are 3 types of outer joins and the way an outer join is performed depends on the position of the tables compared to the join instruction.

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 the rows of table1 will be displayed even if < Join conditions > is not realized in table2.

The following query display all the students, having or not having a mark. If a student does not have a mark, the field Numetu of the corresponding table (NOTES) will have 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 the rows of table2 will be displayed even if < Join conditions > is not realized in table1.

The following query display all the students who have at least a mark.

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 joins.

- Display the name of the students who do not have any mark.

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 on 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;

Next section | Previous section | SQL summary

© Marc Grange, February 2001 | Last update: March 28, 2012