Re: SQL exception join: Left side now needs data combined from two tables

[Dan <dan27649@xxxxxxxxx>]

On Wed, Apr 2, 2008 at 2:16 PM, Elvis Budimlic <
ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

> CTE is a good option, as others have pointed out.  You will want to
> specify
> UNION ALL instead of UNION, to avoid DISTINCT processing.
>
> Nested table expression you have tried should have worked as well, perhaps
> with a slight modification to name (alias) the temp table (i.e. a1a2):
>
>  select * from EE exception join
>          (select * from A1 union ALL
>           select * from A2       ) a1a2 on EEPOL# = AAPOL#


Thanks to all!!  CTE did the trick!  Although I am a bit surprised at how
slowly the union of the two A1/A2 files takes to complete.  A1 has over 1
million records, A2 approx. 5000.  Seems like a CPYF the two files into a
temp file would have been a LOT faster.  Will have to test this.

Elvis, it seems odd that UNION would, by default (DISTINCT), weed out
duplicates.  Is that the SQL standard?  In this case, I don't think it
affects this query, as all records between the two tables are unique.

Also, you are right about the nested table expression.  It worked as you
suggested.  The union appeared to run faster here than using the CTE.  Neat
stuff!!!

- Dan