Home → 2002/10/22, 20h38

SQL (SQL92-Standard) joins notes

INNER JOIN The result consists of rows from T1 paired with rows from T2, filtered with the required ON clause. This is similar to a "traditional" join done in the WHERE clause.
CROSS JOIN This creates a Cartesian product of the rows in both tables, just like when the WHERE clause is forgotten. This join can not be used with the ON clause.
The USING clause TBD
The ON clause This syntax allows you to specify the column names for join keys in both tables.
LEFT OUTER JOIN This returns all the rows from the table on the left side of the join, along with the values from the right-hand side, or nulls if a matching row doesn't exist.
RIGHT OUTER JOIN This returns all the rows from the table on the right side of the join, along with the values from the left-hand side, or nulls if a matching row doesn't exist.
FULL OUTER JOIN This returns all rows from both tables, filling in any blanks with nulls (see important notes below.)

Here is a sample set of tables that will be used in the join examples below:

Table t1 t2 t3
Column x y z
Values 1
2		  
2
3		 1
2
3
4

Sample queries with simple joins, and queries involving multiple join sections in the from clause: 


select x, y from t1 INNER JOIN t2 on x = y
x       y
---------
2       2


select x, y from t1 CROSS JOIN t2
x       y
---------
1       2
2       2
1       3
2       3


select x, y from t1 LEFT OUTER JOIN t2 on x = y
x       y
---------
1       null
2       2


select x, y from t1 RIGHT OUTER JOIN t2 on x = y
x       y
---------
2       2
null    3


select x, y, z from ( t3 left outer join t2 on z = y) left outer join t1 on y = x
x     y      z
-------------
null  null  1
2     2     2
null  3     3
null  null  4


select x, y, z from ( t3 left outer join t2 on z = y) left outer join t1 on z = x
x     y     z
-------------
1     null  1
2     2     2
null  3     3
null  null  4

Some portability notes

  • The default behavior for JOIN without any specifier (OUTER, CROSS, INNER...) may vary between vendors. It is recommended not to rely on such a default and always use JOIN with the intended specifier. For example, Oracle defaults to a INNER join, while MySQL defaults to a CROSS join.
  • FULL joins is buggy on MySQL, version 4.0.3-beta. It does not accept any ON clause and seems to behave like a CROSS join.
  • The JOIN syntax is not supported on PostgresSQL 6.x and below.
  • The JOIN syntax is not supported on Sybase 11.x and below.