CODEWITHSUNDEEP
sqlsql-serversubquerywhere-clause
Manali
Manali

Reputation: 53

Subquery in WHERE clause along with other conditions

Wondering why 2 queries independently execute fine and when clubbed together takes the query into an infinite loop

select * from TableA 
where TableA.ColumnA NOT IN (subquery..)   --> EXECUTES FINE

select * from TableA
where TableA.ColumnA IS NULL OR TableA.ColumnA = 0  --> EXECUTES FINE

But above queries when clubbed together, query goes into infinite loop

select * from TableA 
where TableA.ColumnA NOT IN (subquery..) OR TableA.ColumnA IS NULL OR 
      TableA.ColumnA = 0

Any help appreciated.

Thank you

Upvotes: 0

Views: 1897

Answers (2)

Dhana
Dhana

Reputation: 1658

Try using UNION ALL

select * from TableA 
where TableA.ColumnA NOT IN (subquery..)   --> EXECUTES FINE
AND TableA.ColumnA <> 0

UNION ALL

select * from TableA
where TableA.ColumnA IS NULL OR TableA.ColumnA = 0  --> EXECUTES FINE

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

The problem you are facing is one of optimization. The solution is to separate the queries into components. The best way to express this is:

select a.*
from TableA a
where a.ColumnA <> 0 and a.ColumnA NOT IN (subquery..)   -- I actually recommend `not exists`
union all
select a.*
from TableA a
where a.ColumnA IS NULL OR a.ColumnA = 0 ;

The condition a.ColumnA <> 0 in the first query ensures that the two subqueries return different sets -- so you can use union all rather than union. The use and should still allow this subquery to optimize the same way as the original query. If the execution plan changes, you can express the logic differently (such as in the subquery or using not exists) to still get an optimal plan.

Upvotes: 0

Related Questions