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.
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.
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.
SELECTDISTINCT(E.Nometu),
N.Numetu FROM ETUDIANT E LEFT OUTER JOIN NOTES N ON E.Numetu=N.Numetu;