Equi-joins page 1

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:

  1. Table_1 < JOIN type > JOIN Table_2 ON < predicate >


 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

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

  1. Table_1 NATURAL INNER JOIN Table_2
is equivalent to the following join on predicate:

  1. 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)

  1. SELECT * FROM Pass_in_trip
  2. JOIN Passenger ON Pass_in_trip.id_psg = Passenger.id_psg
  3. WHERE trip_no=1123;

trip_no date id_psg place id_psg name
1123 2003-04-05 00:00:00 3 2a 3 Kevin Costner
1123 2003-04-08 00:00:00 1 4c 1 Bruce Willis
1123 2003-04-08 00:00:00 6 4b 6 Ray Liotta

  1. SELECT * FROM Pass_in_trip
  2. NATURAL JOIN Passenger
  3. WHERE trip_no=1123;

id_psg trip_no date place name
6 1123 2003-04-08 00:00:00 4b Ray Liotta
3 1123 2003-04-05 00:00:00 2a Kevin Costner
1 1123 2003-04-08 00:00:00 4c Bruce Willis

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.

Bookmark and Share
Pages 1 2
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100