Where one or another column exists in a sub select

Question

I'm looking to do something like this:

SELECT a, b, c, d FROM someTable WHERE
    WHERE a in (SELECT testA FROM otherTable);

Only I want to be able to test if 2 columns exist in a sub select of 2 columns.

SELECT a, b, c, d FROM someTable WHERE
    WHERE a OR b in (SELECT testA, testB FROM otherTable);

We are using MS SQL Server 2012

score 4 · Accepted Answer · edited May 23 '17 at 12:21

4

Try this

SELECT a, b, c, d 
FROM someTable WHERE
WHERE a IN (SELECT testA FROM otherTable) 
   OR b IN (SELECT testB FROM otherTable)

or

 SELECT a, b, c, d 
        FROM someTable WHERE
        WHERE EXISTS 
          (SELECT NULL 
           FROM otherTable 
           WHERE testA = a OR testB = a 
             OR testA = b OR testB = b)

UPDATE: Maybe you need to add index on testB column, if you have bad performance. Also another option to use CROSS APPLY for MS SQL

SELECT a, b, c, d 
        FROM someTable ST
           CROSS APPLY (
               SELECT 1
               FROM otherTable OT
               WHERE OT.testA = ST.a OR OT.testB = ST.b
           )

If any of this won't work, try using UNION. Mostly UNION gives better performance than OR

SELECT a, b, c, d 
    FROM someTable WHERE
    WHERE a IN (SELECT testA FROM otherTable)
 UNION
    SELECT a, b, c, d 
    FROM someTable WHERE
    WHERE b IN (SELECT testB FROM otherTable)

UPDATE 2:

For further reading on OR and UNION differences

Why is UNION faster than an OR statement

edited May 23 '17 at 12:21

Community

1
1

answered Jun 17 '14 at 17:34

hgulyan

8,099
8
50
75

I had actually tried your first option but it was giving us incredibly bad performance. Just tried the WHERE EXISTS and same thing, very very slow. If I only compare one value, like testA = a its super fast. As soon as I add the OR testB = b it takes 34 seconds. – Sean256 Jun 17 '14 at 19:00
Interestingly the union is much much faster. Though our actual use is much more complicated than my simple example. Our main query is 72 lines and we are actually comparing 6 values not just 2. So union makes us having to do those 72 lines 6 times. I'm not an SQL expert but that seems dirty and inefficient. – Sean256 Jun 17 '14 at 20:06
@Rookie Agree, union looks much more complicated, but we had the same issue and solved it by changing or to union. You can read about that http://stackoverflow.com/questions/15361972/why-is-union-faster-than-an-or-statement – hgulyan Jun 18 '14 at 06:30

Codeman · Answer 2 · 2014-06-17T17:54:49.317

If I'm understanding your question correctly, LEFT JOIN is probably the way to go here:

SELECT a, b, c, d
FROM TableA ta
LEFT JOIN TableB tb
ON ta.a = tb.a
    AND ta.b = tb.b
WHERE tb.a IS NOT NULL
    AND tb.c IS NOT NULL

You could also use UNION and INNER JOIN:

SELECT a, b, c, d
FROM someTable
INNER JOIN OtherTable OT on someTable.B = OT.testB
UNION
SELECT a, b, c, d
FROM someTable
INNER JOIN OtherTable OT ON someTable.A= OT.testA

Note that the JOIN approach should be orders of magnitude faster if you have an index on the column

score 1 · Answer 3 · answered Jun 17 '14 at 17:37

1

Try this..

SELECT a, b, c, d 
FROM someTable 
WHERE Exists 
(
    SELECT 1
    FROM otherTable 
    Where a = testA OR b = testB 
)

answered Jun 17 '14 at 17:37

Raj More

47,048
33
131
198

score 0 · Answer 4 · answered Jun 17 '14 at 17:51

Joins seems to be one option, have you thought about using them with a Union?

SELECT a, b, c, d 
FROM someTable 
INNER JOIN OtherTable OT on someTable.B = OT.testB 
UNION 
SELECT a, b, c, d 
FROM someTable 
INNER JOIN OtherTable OT ON someTable.A= OT.testA

Where one or another column exists in a sub select

4 Answers4