CODEWITHSUNDEEP
sqloracle-databaseoracle10g
wvdz
wvdz

Reputation: 16641

Get a union-like result without using union

This is my table structure:

Product(ID, Barcode, Name)

ChildProduct(ProductID, Barcode)

ChildProduct(ProductID) REFERENCES Product(ID)

Here is some sample data:

Product
(1,100,'A')
(2,200,'B')
(3,300,'C')
(4,400,'D')

ChildProduct
(1,101)
(1,102)
(2,201)
(4,401)

This is the desired result:

(1,100,'A')
(1,101,'A')
(1,102,'A')
(2,200,'B')
(2,201,'B')
(3,300,'C')

This can be achieved quite easily with a union:

SELECT * FROM Product
WHERE ID IN (1,2,3)
UNION
SELECT * FROM ChildProduct cp
JOIN Product p ON (p.Id = cp.ProductID)
WHERE p.ProductID IN (1,2,3)

However, in reality, the WHERE part of this query is a complex construct of joins, so I would really like to avoid using UNION, so that I don't have to duplicate all that code.

How do I achieve the same results without duplicating my where clause?

Edit: Looks like I simplified it a little bit too much. I tweaked it a bit to show that I have to join ChildProduct and Product in any case.

What I'm really looking for is a way to just loose the union outright.

Upvotes: 0

Views: 69

Answers (2)

Ditto
Ditto

Reputation: 3344

Not that I recommend this as a faster option .. however, you can duplicate the results using FULL OUTER JOIN ... O.o

Not sure WHY you'd want to do this ... [edit] didn't notice the purpose of the OP for the simplified where ... tweaked a little - use David's answer .. it addresses your root question :) [/edit]

  select * from (
  select nvl(p.id,cp.productid) id,
         nvl(p.barcode, cp.barcode) barcode
    from product p
      full outer join 
           childproduct cp
        on p.id = cp.productid
          and p.barcode = cp.barcode
     )
   where id in (1,2,3)
  /

          ID    BARCODE
  ---------- ----------
           1        101
           1        102
           2        201
           2        200
           1        100
           3        300

  6 rows selected.

[edit] tweaked the query to "simplify" the final where .. again, though, this could be done with union as well: select * from ( select ) union select ) where

doesn't change it much ...

Upvotes: 2

David Hedlund
David Hedlund

Reputation: 129782

You could do a UNION into a derived table, and query that:

SELECT * FROM
    (SELECT Id, Barcode FROM Product
    UNION ALL
    SELECT ProductId, Barcode FROM PRODUCT) AS ProductAndChild
WHERE ProductAndChild.Id IN (1,2,3)

(UNION ALL is faster than UNION, so it is preferable if you know there aren't going to be duplicates, or if duplicates don't matter)

Upvotes: 4

Related Questions