Re: UNION question

You are correct.

A UNION removes rows that are not distinct whereas a UNION ALL does not. In this case you are forcing the rows to be distinct by appending the table identifier ('_1','_2').

To select only one occurence of each part you would have to do something like this:

(SELECT partNo || '_1'
  FROM myPartsTblNo1
UNION
SELECT partNo || '_2'
  FROM myPartsTblNo2)
MINUS
 (SELECT partNo || '_2'
   FROM myPartsTblNo1,

         myPartsTblNo2
  WHERE myPartsTblNo1.partNo = myPartsTblNo2.partNo)

BTW, ORDER BY is usually redundant in a UNION since a sort must be done to produce the results anyway.

JJ wrote:
>
> Hi,
>
> I have the following SQL statement (example):
>
> SELECT tbl1.partNo || '_1' FROM myPartsTblNo1 tbl1
> UNION
> SELECT tbl2.partNo || '_2' FROM myPartsTblNo2 tbl2
> ORDER BY partNo
>
> Does the UNION work as it should here?
> If there is the same partNo in both tables and I'm appending '_1' and '_2'
> there will be two different "part numbers" and the UNION doesn't fill any
> function. Is this correct och wrong?
>
> --
> Best regards
> Joacim Jarkeborn
Received on Fri Sep 07 2001 - 07:58:15 CDT