Equi-joins

The joins that we considered earlier and which predominate in the examples of this e-book are called join on predicate.

The syntax of this kind of join is:

Table_1 < join type > JOIN  Table_2 ON < predicate >

where

 join type := [INNER] | [OUTER]{LEFT | RIGHT | FULL}

These compounds are the most common; any logical expression can be used as a predicate. It is for this reason that all dialects support this kind of connection.

A special, but often used, join is an equi-join, the case where the predicate is the equality of values ​​in the columns of the tables which are joining.

In this case, the equality columns often have the same names, because the tables that are associated with the foreign key participate in the join. However, the latter is not important, because We can rename the columns when needed.

So, for this special case of join - the equi-joining over columns with the same names - there are separate syntactic forms of the join: a natural join and a join been using column names.

Natural join

Table_1 NATURAL < join type > JOIN  Table_2

There are not predicate here, because it is implied, namely the pairwise equality of all columns with the same names in both tables. For example, if each of joining tables include columns a and b, then the natural join

Table_1 NATURAL INNER JOIN  Table_2

is equivalent to the following join on predicate:

Table_1 INNER JOIN  Table_2 ON Table_1.a = Table_2.a AND Table_1.b = Table_2.b

   In addition in the case of natural join, duplicates of columns having the same names will be excluded from the result. Compare, for example, the results of following queries (Airport database)

select * from Pass_in_trip
    join Passenger on Pass_in_trip.id_psg = Passenger.id_psg
where trip_no=1123;

select * from Pass_in_trip
    NATURAL join Passenger
where trip_no=1123;

Presented results show that the id_psg column participating in join, does not repeat for a natural join.

Among the DBMS available on the site sql-ex.ru, only SQL Server does not support a natural join. If you want to get practice with a natural join, choose  PostgreSQL or MySQL in the console.

If you want to perform an equi-join not on all columns with matching names, but only on their part, then we can use the connection USING:

Table_1 < join type > JOIN  Table_2 USING(< column list >)

The list of columns contains those columns by which the equi-join is performed. Accordingly,this list can contain only those of the columns whose names coincide in both joining tables.

The list of columns contains those columns by which the equi-join is performed. Accordingly,this list can contain only those of the columns whose names coincide in both joining tables.

The join of rows from the Income and Outcome tables by the equality of values in the date column (database “Recycled firm”)

select * from Income join Outcome USING(date)
where MONTH(date) >= 4;

The join of rows from the Income and Outcome tables by the equality of values in the date and point columns

select * from Income join Outcome USING(date, point)
where MONTH(date) >= 4;

The join of rows from the Income and Outcome tables by the equality of values in the date, point, and code columns

select * from Income join Outcome USING(date, point, code);

does not return any rows.

This join over all columns with matching names is equivalent to a natural join

select * from Income NATURAL join Outcome;

To demonstrate the output in the last example, let’s use the left join

select * from Income left join Outcome USING(date, point, code)
where MONTH(date) >= 4;