CODEWITHSUNDEEP
sql-server
ihf
ihf

Reputation: 91

Combining subselects in SQL query

How can I simplify this query by combining the subselects?

SELECT * 
FROM table 
WHERE id1 IN (SELECT id1 FROM table WHERE [keyid] = 123)
  AND id2 IN (SELECT [id2] FROM table WHERE [keyid] = 123)

I naively tried:

SELECT * 
FROM table 
WHERE id1 = t.id1 
  AND id2 = t.id2
   IN (SELECT id1, id2 FROM table WHERE keyid = 123) AS t

Upvotes: 1

Views: 62

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

From your tablenames, I assume that both IN Clauses refer to same table and refer to same key field. 

SELECT t1.* FROM Table AS t1
INNER JOIN 
(
SELECT Id1, Id2 FROM Table WHERE keyid = 123
) as t2
ON t1.id1 = t2.id1 AND t1.id2 = t2.id2

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

There is no real need to rewrite your query, it is fine as-is. You could rewrite the subqueries using exists logic:

SELECT t1.*
FROM yourTable t1
WHERE
    EXISTS (SELECT 1 FROM yourTable t2 WHERE t2.id1 = t1.id1 AND t2.keyid = 123) AND
    EXISTS (SELECT 1 FROM yourTable t3 WHERE t3.id2 = t1.id2 AND t3.keyid = 123);

The exists logic would let SQL Server stop scanning your table as soon as it finds a single match. This might mean improved performance over the version you currently have.

If you wanted to rewrite using a series of self joins, here is what you could try:

SELECT DISTINCT t1.*
FROM yourTable t1
INNER JOIN yourTable t2 ON t2.id1 = t1.id1
INNER JOIN yourTable t3 ON t3.id2 = t1.id2
WHERE t2.keyid = 123 AND t3.keyid = 123;

Upvotes: 5

Related Questions