msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Back to basics: outer joins

Continuing the series about joins, today I will review outer joins. An outer join extends the result of an inner join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

The ANSI SQL syntax of an outer join is:

FROM t1 { LEFT | RIGHT | FULL } [OUTER] JOIN t2

OUTER is optional. If you use LEFT, RIGHT, or FULL, the assumption is that it is an outer join.

LEFT OUTER JOIN

LEFT specifies that the results be generated using all rows from t1. For those rows in t1 that don’t have corresponding rows in t2, NULLs are returned in the result set for the t2 columns.

For example, to list all departments even if they have no employees, you can perform a LEFT OUTER JOIN between the dept and the emp tables:

SELECT dept.dept_name, emp.emp_name
FROM dept LEFT OUTER JOIN emp 
ON dept.dept_id = emp.dept_id

DEPT_NAME  EMP_NAME
---------- ---------
HR         
IT         Eddie                                          

2 rows selected

A corresponding query using the old join syntax is:

SELECT dept.dept_name, emp.emp_name
FROM dept, emp
WHERE dept.dept_id = emp.dept_id(+)

The (+) operator following emp.dept_id means that you want to display a row from the dept table, even though there exists no corresponding row in the emp table.

RIGHT OUTER JOIN

RIGHT specifies that the results be generated using all rows from t2. For those rows in t2 that don’t have corresponding rows in t1, NULLs are returned in the result set for the t1 columns.

For example, to list all the employees even if they are not related to any particular department, you can perform a RIGHT OUTER JOIN between the emp and the dept tables:

SELECT dept.dept_name, emp.emp_name
FROM dept RIGHT OUTER JOIN emp 
ON dept.dept_id = emp.dept_id

DEPT_NAME  EMP_NAME  
---------- ---------
           King
IT         Eddie

2 rows selected

A corresponding query using the old join syntax is:

SELECT dept.dept_name, emp.emp_name
FROM dept, emp 
WHERE dept.dept_id(+) = emp.dept_id

The (+) operator following dept.dept_id means that you want to display a row from the emp table, even though there exists no corresponding row in the dept table.

LEFT or RIGHT?

The LEFT and RIGHT keywords in an outer join query are relative to the position of the tables in the FROM clause. Here is a way to decide which to use, LEFT or RIGHT: look at t1 and t2 in the FROM clause, if t1 is to your left and you want to return all rows from t1, use LEFT JOIN. If t1 is to your right and you want to return all rows from t1, use RIGHT JOIN.

FULL OUTER JOIN

FULL specifies that the results be generated using all rows from t1 and all rows from t2. For those rows in t1 that don’t have corresponding rows in t2, NULLs are returned in the result set for the t2 columns. Additionally, for those rows in t2 that don’t have corresponding rows in t1, NULLs are returned in the result set for the t1 columns.

For example, to list all the departments (with or without employees), as well as all the employees (with or without a department), use a FULL OUTER JOIN:

SELECT dept.dept_name, emp.emp_name
FROM dept FULL OUTER JOIN emp 
ON dept.dept_id = emp.dept_id


DEPT_NAME  EMP_NAME  
---------- ---------
HR         
IT         Eddie
           King

3 rows selected

A corresponding query using the old join syntax is:

SELECT dept.dept_name, emp.emp_name
FROM dept, emp 
WHERE dept.dept_id(+) = emp.dept_id(+)

OOPS! if you execute the above query you get this error:

ORA-01468: a predicate may reference only one outer-joined table

The fact is that FULL OUTER JOIN is not supported in the old syntax. The outer join operator (+) can appear on only one side of an expression in the join condition, not on both (a workaround is to use a UNION of two SELECT statements).

One other advantage to using ANSI SQL is to overcome the restriction that a condition containing the (+) operator may not use the IN operator and may not be combined with another condition using the OR operator. For example:

SELECT dept.dept_name, emp.emp_name
FROM dept, emp 
WHERE dept.dept_id(+) = emp.dept_id
OR emp.emp_name = 'King'

Will give you this error:

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Here is how you would write the query using the ANSI SQL join and without getting the ORA-01719 error:

SELECT dept.dept_name, emp.emp_name
FROM dept RIGHT OUTER JOIN emp 
ON (dept.dept_id = emp.dept_id
    OR emp.emp_name = 'King')

DEPT_NAME  EMP_NAME  
---------- ---------
HR         King
IT         King
IT         Eddie

3 rows selected

That concludes my overview of outer joins. Later, I will review the rest of the join types


Filed in Joins, Oracle on 22 Mar 06 | Tags: ,


Reader's Comments

  1. |

    I must admit this “left”/”right” concept has never made any sense to me.

    Say you want to list customers and their orders, and you want to include customers who not have placed an order, so your query is an outer join driven from CUSTOMERS. Your FROM clause would intuitively (at least, to me) start with CUSTOMERS:

    FROM customers LEFT OUTER JOIN orders

    Surely it is ORDERS that is being joined? And it’s on the right? And if you wanted the join the other way around wouldn’t you just write

    FROM orders LEFT OUTER JOIN customers

    rather than writing it backwards and switching to “RIGHT OUTER JOIN” syntax? Why did they even need to invent two syntax options for exactly the same thing anyway? (I suspect this was a committee decision.)

    While I’m at it, I would always begin any ORDERS predicate with a column name from ORDERS, e.g:

    FROM customers c LEFT OUTER JOIN orders o ON o.order_id = c.order_id

    (notice that “o” is now on the left) for the same reason that I would write

    WHERE ename = ‘FORD’

    and not

    WHERE ‘FORD’ = ename

    Bafflingly (to me), I often see it written backwards.

    btw the interaction of WHERE clauses with outer joins might be worth a mention, as it seems to be another source of confusion.

  2. |

    Yes, I agree that there is no need to have RIGHT and LEFT, since:

    FROM customers LEFT OUTER JOIN orders
    

    is the same as:

    FROM orders RIGHT OUTER JOIN customers 
    

    You can always stick with LEFT (or RIGHT) and just switch table positions based on your requirement. I have no idea why both keywords are needed, maybe it just made sense that every right had a left and every left had a right.

  3. |

    > I have no idea why both keywords are needed, maybe it just made sense that every right had a left and every left had a right.

    I can’t see why they needed either of them. Why not just “OUTER JOIN”? My guess is the committee argued over it for a week and finally agreed to include everyone’s suggestions just so they could get on with something else, like recursive subquery factoring or the MODEL clause.

  4. |

    Why not just “OUTER JOIN”?

    Yes, why not. If the OUTER JOIN implicitly means LEFT OUTER JOIN, that should work, making LEFT/RIGHT optional.

    My guess is the committee argued over it

    That is a plausible guess.

  5. |

    good

  6. |

    Excellent explanation with a very simple example ,everyone can understand easily. Please send these types of articles based on only Oracle PL/SQL by mail Thanks

  7. |

    See friends , there are 2 keywords “LEFT” and “RIGHT” despite that only one is sufficient. This is because , the “LEFT OUTER JOIN” is supposed to be used by the Left handed people and vice versa. So, don’t think about it much , it is not a big issue.

  8. |

    very useful for your queries and suggestions…

    i’m experting a lot of from you.

    thanks and regards, murugan

  9. |

    Thanks much for this. The example of correcting the syntax for error ORA-01719 was exactly what I needed!