Database: SELECT: Joins - Syntax Variation

SQL has gone thru many versions, resulting in alternatives to write joins. They are all valid, but some of the newer notations many not yet be supported by your favorite DBMS. Here are equivalent ways of performing an inner join on two tables, Instructors with a primary key of ins_id and Sections which contains a foreign key ins_id, which corresponds to a record in the Instructors table. The purpose of each of the following queries is to show instructors and which courses they are teaching.

Joining in the WHERE clause (original SQL)

The tables are simply listed in the FROM clause, and specification of the equal fields is in the WHERE clause. This older style generally works in all systems.

SELECT ins_lname, ins_fname, crs_id
  FROM Instructors, Sections
  WHERE Instructors.ins_id=Sections.ins_id;

Using INNER JOIN ... ON

Here the join is specified in the FROM clause, leaving the WHERE clause to specify conditions on which rows to select. The INNER keyword is optional in some DBMSes (but required in Access).

SELECT ins_lname, ins_fname, crs_id
  FROM Instructors INNER JOIN Sections ON Instructors.ins_id=Sections.ins_id;

Using INNER JOIN ... USING

If the key names are the same in both tables, the USING clause is a shorter than the above. Not supported by Access.

SELECT ins_lname, ins_fname, crs_id
  FROM Instructors INNER JOIN Sections USING (ins_id);

Using NATURAL JOIN

When the joining keys have the same name, an even shorter way to write this is with a NATURAL join. Not supported by Access.

SELECT ins_lname, ins_fname, crs_id
  FROM Instructors NATURAL INNER JOIN Sections;