Q

LEFT and RIGHT joins, INNER and OUTER joins

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?

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close