Can this query be optimized?

 ColumnA   ColumnB
    1         0
    2         0
    2         1
    2         2
    3         0
    3         0

Can this query below be optimized?

SELECT DISTINCT ColumnA 
  FROM TABLE 
 WHERE ColumnA NOT IN (SELECT ColumnA 
                         FROM TABLE
                        WHERE ColumnB = 2)
   AND ColumnB = 0

This should return 1 and 3.

开发者_如何学运维I want to select distinct values from ColumnA, but only if there is a value in ColumnB equal to 0... and there is no value in ColumnB equal to 2. Is there a more optimal way to rewrite this?

You could try this:

SELECT DISTINCT a.ColumnA 
  FROM TABLE a 
 WHERE a.CoulumnB = 0 
   AND NOT EXISTS (SELECT NULL 
                     FROM TABLE b 
                    WHERE b.ColumnA = a.ColumnA 
                      AND b.ColumnB = 2)

Having a not in can be bad for performance. You could try grouping instead, and count the number of zeroes and twos in each group:

select
  ColumnA,
  sum(case ColumnB when 0 then 1 else 0 end) as Zeroes,
  sum(case ColumnB when 2 then 1 else 0 end) as Twos
from TABLE
group by ColumnA
having Zeroes > 0 and Twos = 0

I'm not sure if this is more efficient without checking the execution plans, but as it's more straightforward it may very well be.

Also, this is SQL Server syntax, so it might need some tweaking to run in Oracle.

I'd say this is about as good as it gets. Only way really to get faster is to make an indexed view of this query and use it as your datasource.

I try to avoid NOT IN whenever possible

maybe this?

SELECT DISTINCT ColumnA FROM TABLE t
LEFT JOIN TABLE z ON t.ColumnA=z.ColumnA AND z.ColumnB=2 
WHERE t.ColumnB=0 AND z.ColumnA IS NULL

Is this a real world example? Will the table only ever have this very small number of rows? If so, then you aren't really likely to be able to optimise it much further, the table is just too small. Having said that, "not in" wont scale very well, you'd be better of to use "not exists".