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 --
CATEGORY 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.