ansaurus

Question

Converting nested Selects in WHERE .. OR .. to INNER JOINS

Answer 1

+3 A:

This one's tricky because it's an "OR" condition rather than an "AND". But I think this would do it:

SELECT 
        [Name].[NameValueID],
        [Name].[NameTypeID],
        [Name].[NameID],
        [Name].[Value]
FROM [Name] 
INNER JOIN  ( SELECT NameTypeID FROM @tbNameType ) t ON t.NameTypeID=Name.NameTypeID

UNION

SELECT 
        [Name].[NameValueID],
        [Name].[NameTypeID],
        [Name].[NameID],
        [Name].[Value]
FROM [Name]
INNER JOIN ( SELECT NameID FROM @tbName) t ON t.NameID = Name.NameID

Joel Coehoorn 2009-10-23 14:05:44

Wow, that was quick.. I'll give it a blast!

Gribbler 2009-10-23 14:07:18

This would do it... but it looks kludgy to me, much more so than the original query. Why does it *have* to be an inner join? For performance, I'd try out some outer joins, or maybe NOT EXISTS clasues.

Philip Kelley 2009-10-23 14:13:46

It does't have to be an iner join, but i've been asked to remove the Where In statements and use INNER JOINS instead, i guess if there was something neater using a different join then I could use that.I'm just learing SQL and finding it a bit tricky!

Gribbler 2009-10-23 14:20:12

[I am not knocking this solution, I am merely questioning the need for it.] Just because you can replace it with an inner join doesn't mean you should. Here, you replaced one query on the NAME table with two queries against it, and as it's not a UNION ALL, SQL will perform a DISTINCT operation when merging them. Meaning the query will almost certainly perform more poorly than the original. You won't notice the difference on small tables, but if it is (or when it beomces) large, it may become a bit of a bottleneck.

Philip Kelley 2009-10-23 14:44:30

These are, alas, fairly advanced concepts, and not stuff that I'd except any new database developer to be familiar with. I guess what upsets me here is (whoever) saying "do it this way", and the end result, through no fault of your own, appears to be worse.

Philip Kelley 2009-10-23 14:46:53

The DISTINCT operation is necessary, because both queries might return the same row and even each of the queries can return duplicates if one row from Name maps to multiple rows in @tbNameType or @tbName.

Lukáš Lalinský 2009-10-23 14:47:11

Maybe Gribbler needs to go back to whoever asked to change WHERE IN to INNER JOINS and ask why he/she can't use WHERE EXISTS or other types of JOINs. E.g. maybe the person asking for this has the misconception that INNER JOINs are universally better.

Aaron Bertrand 2009-10-23 15:00:02

Answer 2

+2 A:

SELECT DISTINCT
    Name.NameValueID,
    Name.NameTypeID,
    Name.NameID,
    Name.Value
FROM
    Name
    LEFT JOIN @tbNameType a ON a.NameTypeID=Name.NameTypeID
    LEFT JOIN @tbName b ON b.NameID=Name.NameID
WHERE a.NameTypeID IS NOT NULL OR b.NameID IS NOT NULL

Lukáš Lalinský 2009-10-23 14:18:33

Hi,Thanks, I've decided to go with this in my code, although the other answer is marked as the answer as that was the question that I asked!Sorry!!

Gribbler 2009-10-23 14:31:34

I know, I posted it only because it should be more efficient than inner joins + union.

Lukáš Lalinský 2009-10-23 14:43:36

ansaurus

tags:

views:

answers:

Converting nested Selects in WHERE .. OR .. to INNER JOINS

related questions