Re: Conditional Relationships ?

On 30 Dec 2002 12:19:33 -0800, 71062.1056_at_compuserve.com (--CELKO--) wrote:

[snip...]
>The UNION JOIN was defined in SQL-92, but I know of no SQL product
>that has implemented it. As the name implies, it is a cross between a
>UNION and a FULL OUTER JOIN. The definition followed easily from the
>other infixed JOIN operators. The syntax has no searched clause
>
> <table expression 1> UNION JOIN <table expression 2>
>
>The statement takes two dissimilar tables and puts them into one
>result table. It preserves all the rows from both tables and does not
>try to consolidate them. Columns which do not exist in one table are
>simply padded out with NULLs in the result rows. Columns with the
>same names in the tables have to be renamed differently in the result.
> It is equivalent to
>
> <table expression 1>
> FULL OUTER JOIN
> <table expression 2>
> ON 1 = 2;
>
>Any searched expression which is always FALSE will work. As an
>example of this, you might want to combine the medical records of male
>and female patients into one table with this query.
>
> SELECT *
> FROM (SELECT 'male', prostate FROM Males)
> OUTER UNION
> (SELECT 'female', pregnancy FROM Females);
>
>to get a result table like this
>
>Result
>male prostate female pregnancy
>==================================
>'male' no NULL NULL
>'male' no NULL NULL
>'male' yes NULL NULL
>'male' yes NULL NULL
>NULL NULL 'female' no
>NULL NULL 'female' no
>NULL NULL 'female' yes
>NULL NULL 'female' yes

But of what actual USE is such a result set in real life?? There is usually a good reason for putting things in separate tables...

Bob Hairgrove
rhairgroveNoSpam_at_Pleasebigfoot.com Received on Mon Dec 30 2002 - 23:56:48 CET