Problem solve Get help with specific problems with your technologies, process and projects.

More than two tables in an OUTER JOIN

I need one example to join more than two tables using outer join.

There are several ways, depending on specific needs (OUTER with OUTER, OUTER with INNER, and so on), but in each case you will need parentheses.

Consider the following tables --

  id  descr
5  animal
  10  vegetable
  15  mineral

ITEM id descr 101 puppy 102 granite 104 quartz 110 iron 115 happiness 117 goldfish
CATITEM catid itemid 5 101 5 117 15 102 15 104 15 110

Notice that there is no item in the vegetable category.

There are two ways to write the outer join that lists all categories and the items in them --

select category.descr as catname
     , item.descr as itemname
  from category 
       left outer join 
       ( catitem 
         left outer join item 
         on catitem.itemid = item.id )
       on category.id = catitem.catid 

select category.descr as catname , item.descr as itemname from ( category left outer join catitem on category.id = catitem.catid ) left outer join item on catitem.itemid = item.id

Both of these produce the same result --

catname   itemname
animal    puppy
animal    goldfish
vegetable -- 
mineral   granite
mineral   quartz
mineral   iron

Notice there was no category for happiness. A double outer join in the "opposite" direction would be

select item.descr as itemname
     , category.descr as catname
  from item
       left outer join 
       ( catitem 
         left outer join category 
         on catitem.catid = category.id )
       on item.id = catitem.itemid

This produces --

itemname  catname
puppy     animal
granite   mineral
quartz    mineral
iron      mineral
happiness -- 
goldfish  animal


For More Information

  • Dozens more answers to tough SQL questions from Rudy Limeback are available here.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
  • Discover the difference between a LEFT JOIN and a LEFT OUTER JOIN in Oracle.


Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.