I have been searching the Internet for definitions, and perhaps some examples, of table joins. More specifically, what is the difference between a LEFT and RIGHT join, and an INNER and OUTER join? Is the same terminology used by Oracle, Access, IBM DB2 and other vendor databases?
To answer your last question first, yes, the same terminology is used for LEFT, RIGHT, INNER, and OUTER joins, by all database systems that support this syntax. And all recent versions do support this syntax, because it's standard SQL.
An INNER join returns only rows where the join condition is satisfied. The join condition is specified in the ON clause. There's another syntax structure that is possible for inner joins, the "table list" syntax, in which the tables are listed in the FROM clause, and the join conditions are specified in the WHERE clause instead. There is no logical difference between the following two queries; they will both return the same results and perform exactly the same:
select columns from table1 , table2 where table1.id = table2.t1_id
select columns from table1 inner join table2 on table1.id = table2.t1_id
The difference in syntax becomes more noticeable as you start joining more and more tables into the query. In the table list syntax (which pre-dates the adoption of JOIN syntax as a standard), sometimes the join conditions end up all mish-mashed together in the WHERE clause, along with other filter conditions, making it really difficult to understand them, and especially to determine at a glance whether any join conditions are incorrectly specified. See Converting to LEFT OUTER JOIN syntax (15 July 2005) for an example.
An OUTER join returns all rows from one table, plus matching rows, if any, based on the join condition, from the other table. There are three types of OUTER joins. LEFT OUTER joins and RIGHT OUTER joins are identical, and the difference is entirely based on which table you mention first. So the following two queries will both return the same results:
select columns from table1 left outer join table2 on table1.id = table2.t1_id
select columns from table2 right outer join table1 on table2.t1_id = table1.id
Important: make sure you understand why the above two queries are identical. Note which table is named first in the FROM clause.
This is why I personally never write RIGHT OUTER joins. They're too confusing. (Perhaps I am easily confused.) I think from left to right, so I write the FROM clause by first stating the table from which I want all rows, and then the LEFT OUTER join to the table that might provide the matching rows.
The final type of OUTER join is the FULL OUTER JOIN. This can be defined as a UNION of LEFT and RIGHT outer joins. However, it's better that you completely understand LEFT and RIGHT outer joins first, before tackling a FULL OUTER join.
This was first published in September 2005