SELECT statement

Using multiple tables in a query

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

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)

Console

Execute

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     join Passenger on Pass_in_trip.id_psg = Passenger.id_psg where trip_no=1123;

    Scheme

No scheme

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.