Re: The (+) outer join operator.

From: Colin Fraser <cfraser_at_korma.chilli>
Date: 29 Nov 1994 08:51:26 GMT
Message-ID: <3beq2e$75f_at_pheidippides.axion.bt.co.uk>

In article F6B_at_iglou.com, proberts_at_iglou.iglou.com (Phil Roberts) writes:
>I've been told that I should be using an outer join operator (+) in an
>insert statement I am trying to run. I've read the small bit of info
>in the SQL Reference Manual about (+) but I'm still having a hard time
>understanding what it does, and why I should use it. Can someone explain
>the (+) outer join operator better than the manual?

Phil,

A simple example:

Suppose you wanted a query to return all customers and their order numbers. You would do something like:

	select c.CUSTOMER_NAME
	,	o.NUMBER
	from	customer c
	,	order o
	where	c.CUSTOMER_ID = o.CUSTOMER_ID
	order by ...
	etc.

and this would work BUT it might not list all customers since some might be new accounts with no orders processed, may not have ordered anything lately and their order records purged from the database etc. If you need them listed nonetheless you would use the outer join operator to ensure that a row was returned for each thus:

	select c.CUSTOMER_NAME
	,	o.NUMBER
	from	customer c
	,	order o
	where	c.CUSTOMER_ID = o.CUSTOMER_ID (+)
	order by ...
	etc.

This effectively says "if this side of the where clause doesn't return any rows, return one anyway with all columns referring to this table set to NULL". Thus in this case any customer with no orders recorded would still have a row returned with the customer name and a null for the order number.

Not a very elegant explanation but I hope it helps.

I find the outer join operator very useful, so some understanding and practice with it is worthwhile.

Good luck,

Colin Fraser

#include <disclaimer.std> Received on Tue Nov 29 1994 - 09:51:26 CET