Database: SELECT: Joins - Syntax Variation

SQL has gone thru many versions, resulting in several alternative styles to use for innre joins. They are all valid, but some of the newer notations many not yet be supported by your favorite DBMS. The principal shift in thinking is that the joins should be expressed in the FROM clause, and the WHERE clause should be used for selecting rows after the join. Example. The problem is to show all psychology instructors (dept_code='PSYC') and the classes (sections) they are teaching. These examples use two tables, Instructor with a primary key of instructor_id and Section which contains a foreign key instructor_id, which corresponds to a record in the Instructor table.

Joining in the WHERE clause (traditional 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. Note that the WHERE clause has a mix of the join conditions and row selection conditions.

SELECT instructor_lname, instructor_fname, course_code
  FROM Instructor, Section
  WHERE Instructor.instructor_id=Section.instructor_id AND dept_code='PSYC';

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 instructor_lname, instructor_fname, course_code
  FROM Instructor INNER JOIN Section ON Instructor.instructor_id=Section.instructor_id
  WHERE dept_code='PSYC';

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 instructor_lname, instructor_fname, course_code
  FROM Instructor INNER JOIN Section USING (instructor_id)
  WHERE dept_code='PSYC';

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 instructor_lname, instructor_fname, course_code
  FROM Instructor NATURAL INNER JOIN Section
  WHERE dept_code='PSYC';