6 Replies Latest reply: Aug 20, 2011 12:53 PM by Frank Kulash RSS

    case expression doubt

    883038
      hi all,

      which is better to use in below two query's.


      select empno from emp where deptno=10
      union all
      select empno from emp where deprno=20


      or


      select
      case when deptno=10 then empno end case ,
      case when deptno=20 then empno end case
      from
      emp



      which is better?

      will case expression in select statement will effect the performance?


      regards
      shashank .k
        • 1. Re: case expression doubt
          Brian Bontrager
          "better" is the one that meets your requirements. These two queries return two different results.
          SQL> create table emp (empno number, deptno number);
          
          Table created.
          
          SQL> insert into emp values (1000,10);
          
          1 row created.
          
          SQL> insert into emp values (1010,10);
          
          1 row created.
          
          SQL> insert into emp values (2000,20);
          
          1 row created.
          
          SQL> insert into emp values (2020,20);
          
          1 row created.
          
          SQL>  select empno from emp where deptno=10
            2  union all
            3   select empno from emp where deptno=20;
          
               EMPNO
          ----------
                1000
                1010
                2000
                2020
          SQL> select
            2  case when deptno=10 then empno end case ,
            3  case when deptno=20 then empno end case
            4  from
            5  emp;
          
                CASE       CASE
          ---------- ----------
                1000
                1010
                           2000
                           2020
          • 2. Re: case expression doubt
            883038
            hi thanks for reply ,

            now consider these two
            select empno,null from emp where deptno=10
            union all
            select null,empno from emp where deptno=20

            and

            select case when deptno=10 then empno else null end case ,
            case when deptno=20 then empno else null end case from emp order by 1,2
            • 3. Re: case expression doubt
              Frank Kulash
              Hi,
              shashank .kura wrote:
              hi thanks for reply ,

              now consider these two
              select empno,null from emp where deptno=10
              union all
              select null,empno from emp where deptno=20
              The query above will only produce results for rows where deptno=10 or 20 (a total of 8 rows in the standard scott.emp table).
              and

              select case when deptno=10 then empno else null end case ,
              case when deptno=20 then empno else null end case from emp order by 1,2
              This second query will produce one output row for every row in the table (14 rows in the standatd scott.emp table. 8 of those rows will be the same as returned by the first query, and the other 6 will have NULL in both columns.) Also, both columns have the same alias, CASE. (The keyword to end a CASE expression is just END. If you say END CASE, then CASE is taken to be a column alais.)

              The following gets the same results as your first query:
              select  case 
                       when deptno=10 then empno 
                                     else null 
                   end                    AS empno_10
              ,     case w
                       when deptno=20 then empno 
                                     else null 
                   end                    AS empno_20 
              from      emp
              WHERE     deptno     IN (10, 20)
              ;
              This will be more efficient than a UNION, because it only has to make one pass through the table.
              • 4. Re: case expression doubt
                Frank Kulash
                Hi,
                shashank .kura wrote:
                hi thanks for reply ,

                now consider these two
                Please try the queries yourself before posting them. Ask specific questions, such as "Why does the first query produce ...?" or "I though the second query would produce ... using the standard scott.emp table. Why doesn't it?"
                select empno,null from emp where deptno=10
                union all
                select null,empno from emp where deptno=20
                The query above will only produce results for rows where deptno=10 or 20 (a total of 8 rows in the standard scott.emp table).
                and

                select case when deptno=10 then empno else null end case ,
                case when deptno=20 then empno else null end case from emp order by 1,2
                This second query will produce one output row for every row in the table (14 rows in the standatd scott.emp table. 8 of those rows will be the same as returned by the first query, and the other 6 will have NULL in both columns.) Also, both columns have the same alias, CASE. (The keyword to end a CASE expression is just END. If you say END CASE, then CASE is taken to be a column alais.)

                The following gets the same results as your first query:
                select  case 
                         when deptno=10 then empno 
                                       else null 
                     end                    AS empno_10
                ,     case
                         when deptno=20 then empno 
                                       else null 
                     end                    AS empno_20 
                from      emp
                WHERE     deptno     IN (10, 20)
                ;
                This will be more efficient than a UNION, because it only has to make one pass through the table.

                Edited by: Frank Kulash on Aug 19, 2011 10:26 AM
                • 5. Re: case expression doubt
                  883038
                  hi thanks for ur suggestion,
                  is this case will effect the performance?

                  in my report I have two subqeries and and a union operator so to avoid union I used case ans removed union and now execcution time also reduced but when I Kept in live database ,there is lot of variations in execution time sometimes it is execuing in 1 min and some times executing in 3minutes .
                  I think this is because of case ? am I correct ?


                  Regards
                  shashank .k
                  • 6. Re: case expression doubt
                    Frank Kulash
                    Hi,
                    shashank .kura wrote:
                    hi thanks for ur suggestion,
                    is this case will effect the performance?
                    Sorry, I don't understand what you're asking.

                    Will a CASE expression affect performance? Yes, of course. Everything you do in a query affects its performance.
                    in my report I have two subqeries and and a union operator so to avoid union I used case ans removed union and now execcution time also reduced but when I Kept in live database ,there is lot of variations in execution time sometimes it is execuing in 1 min and some times executing in 3minutes .
                    I think this is because of case ? am I correct ?
                    Depending on how you test it, that's not surprising. You might see the same kind of performace difference (siometimes 1 minute, other times 3 minutes) with or without a CASE expression.

                    For how to post perforance questions, see
                    When your query takes too long ...
                    HOW TO: Post a SQL statement tuning request - template posting

                    You'll probably get better response if you post your question in the SQL and PL/SQL forum:
                    PL/SQL
                    Mark this thread as "Answered" before you start another thread for the same problem.