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.
