April 5, 2007 at 8:06 am
April 5, 2007 at 8:20 am
you mean
select *
from tableA
where cola = case when colb = 'b' then 1 else 0 end?
You can, but if you explain what you're looking to do, we might be able to help.
April 9, 2007 at 3:32 pm
CASE is a function, even if it looks like a statement, and it can be used anywhere an expression is legal including WHERE, GROUP BY, ORDER BY, and so on. In some cases the query may be more readable and maintainable with a CASE function in the WHERE clause, but it can have a moderate to severe performance penalty. Using any function (including CASE) in a WHERE clause will hamper index usage and cause table or index scans. If the same condition can be written entirely with sargable arguments you will see better performance, assuming the tables have indexes that the sargable arguments can use.
April 9, 2007 at 11:30 pm
thanks scott.
my scenario is like this...
i've two tables say tbl1 (empid, empname, deptno, yearofjoining) and tbl2 (empid, deptno, deptname).
i need to write the following query...
if the yearofjoining = '2006' then
select a.empid, a.empname from tbl1 a , tbl 2 b where a.deptno = b.deptno
else
select a.empid, a.empname from tbl1 a , tbl 2 b where a.empid = b.empid
As the where condition changes based on the yearofjoining, i just wanted to know whether i can write the above qry in a single statement using CASE WHEN.
Thanks again.
April 10, 2007 at 3:02 am
select a.empid, a.empname
from tbl1 a , tbl 2 b
where (
yearofjoining = '2006'
and a.deptno = b.deptno
)
or (
a.empid = b.empid
)
April 10, 2007 at 7:41 am
Yes, you could use a case function. Something like:
SELECT a.empid, a.empname
FROM tbl1 a, tbl2 b
WHERE CASE WHEN yearofjoining = '2006' and a.deptno = b.deptno then 1
WHEN yearofjoining <> '2006' and a.empid = b.empid then 1 else 0 end = 1
Just because you can write that doesn't mean you should, because that WHERE clause will cause a complete cross join between the two tables and no indexes can be used. If these tables have a significant number of rows, the query above will perform much worse than this version:
SELECT a.empid, a.empname
FROM tbl1 a
INNER JOIN tbl2 b ON a.deptno = b.deptno AND a.yearofjoining = '2006'
UNION ALL
SELECT a.empid, a.empname
FROM tbl1 a
INNER JOIN tbl2 b ON a.empid = b.empid AND (a.yearofjoining < '2006' OR a.yearofjoining > '2006')
The first query is a cross join because all tbl1 records must be compared to all tbl2 records and the CASE function evaluated to filter the results. If both tables have 10 million rows, this will require 100 trillion rows to be processed.
The second query uses two separate joins that can make use of indexes on deptno and empid in both tables. It may still use a table scan to pick up the empname column instead of an index seek and bookmark lookup, depending on the index clustering and data distribution, but at least you have a shot at using an index. It will not degrade into a cross join.
There is always a possibility that the query optimizer will create a good execution plan in spite of badly-written SQL. To see the real difference between the two versions, put them both in the same Query Analyzer window and press CTRL-L.
April 10, 2007 at 10:24 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply