RE: [sqlite] UNION?

[Lee Crain]

Tom,

Thanks for taking the time to document for my benefit more efficient
implementations.

Lee Crain

______________________

-----Original Message-----
From: T&B [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 09, 2007 4:08 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] UNION?

Hi Lee,

> This query does not work in SQLite but works in MS SQL Server:
>
> sqlite> SELECT items_idx, [name], active FROM Items
>    ...> WHERE active = 'T' AND Items.items_idx IN
>    ...> ((SELECT related_item FROM RelatedItems WHERE item = 1777)
>    ...> UNION
>    ...> (SELECT item FROM RelatedItems WHERE related_item = 1777))
>    ...> ORDER BY Items.name ASC;
> SQL error: near "UNION": syntax error

Of course the problem is the brackets you have around each SELECT  
statement, which separates them from the UNION operator.

So, it works fine like this:

SELECT items_idx, [name], active FROM Items
WHERE active = 'T' AND Items.items_idx IN
(
     SELECT related_item FROM RelatedItems WHERE item = 1777
     UNION
     SELECT item FROM RelatedItems WHERE related_item = 1777
)
ORDER BY Items.name ASC;

Also, you may want to consider avoiding performing an IN on a UNION.  
As far as I know, SQLite doesn't optimize that, so will build the  
entire union before performing the IN. If you instead do the  
following, it should be a lot faster (if you have lots of data). But  
I may be wrong.

SELECT items_idx, [name], active
FROM Items
WHERE active = 'T'
     AND
         (
             items_idx IN
                 ( SELECT [related_item] FROM RelatedItems WHERE item  
= 1777 )
             OR items_idx IN
                 ( SELECT item FROM RelatedItems WHERE related_item =  
1777 )
         )
ORDER BY [name] ASC
;


or by using a JOIN:

SELECT items_idx, [name], active
FROM Items
JOIN RelatedItems AS RI
WHERE active = 'T' AND
     (
         RI.related_item = Items.items_idx AND RI.item = 1777
         OR
         RI.item = Items.items_idx AND RI.related_item = 1777
     )
ORDER BY Items.name ASC
;

Tom


--------------------------------------------------------------------------
---
To unsubscribe, send email to [EMAIL PROTECTED]
--------------------------------------------------------------------------
---



-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------