Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Need to build SQL Query

    Hi All,

    I have two table Dept & Emp with following records.

    Dept -- Table
    Deptno Dname Loc
    10 ACCT ABC
    20 SALES XYZ
    30 OPT PQR

    Emp -- Table
    Empno Ename Deptno
    101 AAA 10
    102 BBB 30

    I would like to write an sql query to display the data in below format, display Y for the department where employee exists and N for non exists.

    Deptno Dname Emp_Exists
    10 ACCT Y
    20 SALES N
    30 OPT Y


    Regards,
    JD

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Code:
    select d.deptno, d.dname,
           nvl((select 'Y' 
                  from emp 
                 where emp.deptno = d.ddeptno 
                   and rownum <=1), 
               'N') emp_exists
      from dept d
    or
    Code:
    select d.deptno, d.dname, 
           decode(count(e.empno), 0, 'N', 'Y') emp_exists
      from dept d
           left outer join emp e on (e.deptno = d.ddeptno)
    group by d.deptno, d.dname
    I would assume the first one to be far more efficient due to the assumption that DEPT table is typically very small (<100 rows) compared with EMP which is typically relatively large (could be 1000's of rows).

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's another option:
    Code:
    SQL> select distinct
      2    d.deptno,
      3    d.dname,
      4    decode(e.deptno, null, 'N', 'Y') yn
      5  from dept d,
      6       emp e
      7  where d.deptno = e.deptno (+)
      8  order by d.deptno;
    
        DEPTNO DNAME          Y
    ---------- -------------- -
            10 ACCOUNTING     Y
            20 RESEARCH       Y
            30 SALES          Y
            40 OPERATIONS     N
    
    SQL>

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some more variations.
    (Not tested on Oracle. Just ideas came in my mind.)

    Code:
    SELECT DISTINCT
           d.deptno
         , d.dname
         , NVL2(e.deptno , 'Y' , 'N') AS emp_exists
     FROM  dept d
     LEFT  OUTER JOIN
           emp  e
      ON   e.deptno = d.deptno
     ORDER BY
           d.deptno
    ;

    Code:
    SELECT d.deptno
         , d.dname
         , CASE
           WHEN EXISTS
               (SELECT 0
                 FROM  emp  e
                 WHERE e.deptno = d.deptno
               ) THEN
                'Y'
           ELSE 'N'
           END  AS emp_exists
     FROM  dept d
     ORDER BY
           d.deptno
    ;

    Code:
    SELECT d.deptno
         , d.dname
         , NVL(
              (SELECT DISTINCT
                      'Y'
                 FROM  emp  e
                 WHERE e.deptno = d.deptno
              )
            , 'N'
           ) AS emp_exists
     FROM  dept d
     ORDER BY
           d.deptno
    ;

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Lightbulb

    Based on the various forms of SQL provided so far, it seems the last two that were submitted by tonkuma seem to be the most performant in terms of resource cost and seem to be the best answer.
    Code:
    dayneo@DEVELOPM> set autotrace traceonly explain
    dayneo@DEVELOPM> select d.deptno, d.dname,
      2  	    nvl((select 'Y'
      3  		   from emp
      4  		  where emp.deptno = d.deptno
      5  		    and rownum <=1),
      6  		'N') emp_exists
      7    from dept d
      8  /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3586326530
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     4 |    52 |     3   (0)| 00:00:01 |
    |*  1 |  COUNT STOPKEY     |      |       |       |            |          |
    |*  2 |   TABLE ACCESS FULL| EMP  |     2 |     6 |     2   (0)| 00:00:01 |
    |   3 |  TABLE ACCESS FULL | DEPT |     4 |    52 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM<=1)
       2 - filter("EMP"."DEPTNO"=:B1)
    
    dayneo@DEVELOPM> select d.deptno, d.dname,
      2  	    decode(count(e.empno), 0, 'N', 'Y') emp_exists
      3    from dept d
      4  	    left outer join emp e on (e.deptno = d.deptno)
      5  group by d.deptno, d.dname
      6  /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 595454831
    
    --------------------------------------------------------------------------------
    ---------
    
    | Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| T
    ime     |
    
    --------------------------------------------------------------------------------
    ---------
    
    |   0 | SELECT STATEMENT              |         |     9 |   180 |     7  (29)| 0
    0:00:01 |
    
    |   1 |  HASH GROUP BY                |         |     9 |   180 |     7  (29)| 0
    0:00:01 |
    
    |   2 |   MERGE JOIN OUTER            |         |    14 |   280 |     6  (17)| 0
    0:00:01 |
    
    |   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 0
    0:00:01 |
    
    |   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 0
    0:00:01 |
    
    |*  5 |    SORT JOIN                  |         |    14 |    98 |     4  (25)| 0
    0:00:01 |
    
    |   6 |     TABLE ACCESS FULL         | EMP     |    14 |    98 |     3   (0)| 0
    0:00:01 |
    
    --------------------------------------------------------------------------------
    ---------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access("E"."DEPTNO"(+)="D"."DEPTNO")
           filter("E"."DEPTNO"(+)="D"."DEPTNO")
    
    dayneo@DEVELOPM> select distinct
      2  	    d.deptno,
      3  	    d.dname,
      4  	    decode(e.deptno, null, 'N', 'Y') yn
      5  	  from dept d,
      6  	       emp e
      7  	  where d.deptno = e.deptno (+)
      8  	  order by d.deptno
      9  /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4013148873
    
    --------------------------------------------------------------------------------
    ---------
    
    | Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| T
    ime     |
    
    --------------------------------------------------------------------------------
    ---------
    
    |   0 | SELECT STATEMENT              |         |    14 |   224 |     7  (29)| 0
    0:00:01 |
    
    |   1 |  SORT UNIQUE                  |         |    14 |   224 |     7  (29)| 0
    0:00:01 |
    
    |   2 |   MERGE JOIN OUTER            |         |    14 |   224 |     6  (17)| 0
    0:00:01 |
    
    |   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 0
    0:00:01 |
    
    |   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 0
    0:00:01 |
    
    |*  5 |    SORT JOIN                  |         |    14 |    42 |     4  (25)| 0
    0:00:01 |
    
    |   6 |     TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 0
    0:00:01 |
    
    --------------------------------------------------------------------------------
    ---------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access("D"."DEPTNO"="E"."DEPTNO"(+))
           filter("D"."DEPTNO"="E"."DEPTNO"(+))
    
    dayneo@DEVELOPM> SELECT DISTINCT
      2  	    d.deptno
      3  	  , d.dname
      4  	  , NVL2(e.deptno , 'Y' , 'N') AS emp_exists
      5   FROM  dept d
      6   LEFT  OUTER JOIN
      7  	    emp  e
      8    ON   e.deptno = d.deptno
      9   ORDER BY
     10  	    d.deptno
     11  /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4013148873
    
    --------------------------------------------------------------------------------
    ---------
    
    | Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| T
    ime     |
    
    --------------------------------------------------------------------------------
    ---------
    
    |   0 | SELECT STATEMENT              |         |    14 |   224 |     7  (29)| 0
    0:00:01 |
    
    |   1 |  SORT UNIQUE                  |         |    14 |   224 |     7  (29)| 0
    0:00:01 |
    
    |   2 |   MERGE JOIN OUTER            |         |    14 |   224 |     6  (17)| 0
    0:00:01 |
    
    |   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 0
    0:00:01 |
    
    |   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 0
    0:00:01 |
    
    |*  5 |    SORT JOIN                  |         |    14 |    42 |     4  (25)| 0
    0:00:01 |
    
    |   6 |     TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 0
    0:00:01 |
    
    --------------------------------------------------------------------------------
    ---------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access("E"."DEPTNO"(+)="D"."DEPTNO")
           filter("E"."DEPTNO"(+)="D"."DEPTNO")
    
    dayneo@DEVELOPM> SELECT d.deptno
      2  	  , d.dname
      3  	  , CASE
      4  	    WHEN EXISTS
      5  		(SELECT 0
      6  		  FROM	emp  e
      7  		  WHERE e.deptno = d.deptno
      8  		) THEN
      9  		 'Y'
     10  	    ELSE 'N'
     11  	    END  AS emp_exists
     12   FROM  dept d
     13   ORDER BY
     14  	    d.deptno
     15  /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2228519608
    
    --------------------------------------------------------------------------------
    -------
    
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
    e     |
    
    --------------------------------------------------------------------------------
    -------
    
    |   0 | SELECT STATEMENT            |         |     4 |    52 |     2   (0)| 00:
    00:01 |
    
    |*  1 |  TABLE ACCESS FULL          | EMP     |     2 |     6 |     2   (0)| 00:
    00:01 |
    
    |   2 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:
    00:01 |
    
    |   3 |   INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:
    00:01 |
    
    --------------------------------------------------------------------------------
    -------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("E"."DEPTNO"=:B1)
    
    dayneo@DEVELOPM> SELECT d.deptno
      2  	  , d.dname
      3  	  , NVL(
      4  	       (SELECT DISTINCT
      5  		       'Y'
      6  		  FROM	emp  e
      7  		  WHERE e.deptno = d.deptno
      8  	       )
      9  	     , 'N'
     10  	    ) AS emp_exists
     11   FROM  dept d
     12   ORDER BY
     13  	    d.deptno
     14  /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4020325384
    
    --------------------------------------------------------------------------------
    -------
    
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
    e     |
    
    --------------------------------------------------------------------------------
    -------
    
    |   0 | SELECT STATEMENT            |         |     4 |    52 |     2   (0)| 00:
    00:01 |
    
    |   1 |  SORT UNIQUE NOSORT         |         |     5 |    15 |     4  (25)| 00:
    00:01 |
    
    |*  2 |   TABLE ACCESS FULL         | EMP     |     5 |    15 |     3   (0)| 00:
    00:01 |
    
    |   3 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:
    00:01 |
    
    |   4 |   INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:
    00:01 |
    
    --------------------------------------------------------------------------------
    -------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("E"."DEPTNO"=:B1)
    It is interesting to see how the optimizer changes the access plans when using such subtle differences in the query.

  6. #6
    Join Date
    Jan 2004
    Posts
    153
    Thank you very much for your reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •