Re: JOIN on multiple conditions

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 11/22/04 

Date: Mon, 22 Nov 2004 08:03:48 -0800

>> [OUTER UNION] I only have experience with SQL Server, and I don't
think such an operation is implemented (at least not in SQL Server
2000). What is it supposed to do? <<

Let TN be the table that you get from an INNER join. Let TN1 be the
table you get from padding the rows of the first table that were left
out of the INNER JOIN with NULLs. Let TN2 be the table you get from
padding the rows of the second table that were left out of the INNER
JOIN with NULLs.

Now define the infixed JOINs in terms of UNION ALL operations on those
tables:

a) LEFT OUTER JOIN

 SELECT * FROM TN
 UNION ALL
 SELECT * FROM XN1

 b) RIGHT OUTER JOIN

 SELECT * FROM TN
 UNION ALL
 SELECT * FROM XN2

 c) FULL OUTER JOIN

 SELECT * FROM TN
 UNION ALL
 SELECT * FROM XN1
 UNION ALL
 SELECT * FROM XN2

 d) UNION JOIN

 SELECT * FROM XN1
 UNION ALL
 SELECT * FROM XN2

A UNION JOIN is a FULL OUTER JOIN with the jam filling in the middle
removed :). The SAS statistical package has it, but I don't know if
anyone bothered to implement it.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: OUTER JOIN Problem
    ... Don't outer join. ... Use a union. ... select user, date, sumas incalls, 0 as saves, 0 as tickets ... > dtDate userID calls saves tickets ...
    (microsoft.public.sqlserver.programming)
  • Re: How to join 2 Access tables to return ALL records from both tables
    ... Since you have already eliminated the duplicates ... change to UNION ALL for better performance. ... > Do you mean you want a FULL OUTER JOIN? ... > You recognize the right join as finding records in x not in y, ...
    (microsoft.public.access.queries)
  • Re: Full outer join and Union again !!
    ... Choose the columns in the two queries so that the column order is the same. ... Union the two queries: ... are project_id and transaction_id and I have anoher table called budget ... > I have tried the left outer join assuming that my transaction table is the ...
    (microsoft.public.access.queries)
  • Re: Equilevant of Oracle for optional records
    ... The Jet equivalent of an OUTER JOIN is a LEFT JOIN, a UNION, and a RIGHT ... I prefer to eliminate the duplications and use UNION ALL. ... The Oracle syntax for outer joins makes it possible to join to a table by ... In Oracle this is straigthforward. ...
    (microsoft.public.access.queries)
  • Re: Full Outer Join
    ... Usng the UNION ALL makes the query run a bit faster than just using UNION ... TableB AS B ON A.FieldName = B.FieldName ... FROM TableA AS C RIGHT JOIN ... >A full outer join is done by unioning a left outer join with a right outer ...
    (microsoft.public.access.queries)