SQL & PL/SQL

Hello Gurus,

I have simple question regarding Two queries. Both are from standard EMP/DEPT tables and both returns same output. DB version is 10.2.0.3 On SPARC 64 Bit

1st Query

select emp.ename,(select dept.dname from dept where dept.deptno=emp.deptno) as Dname from emp;

Execution Plan
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |    14 |   126 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL          | EMP     |    14 |   126 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPT"."DEPTNO"=:B1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        511  bytes sent via SQL*Net to client
        237  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

2nd Query

select emp.ename,dept.dname from dept,emp where dept.deptno=emp.deptno;

Execution Plan
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   308 |     7  (15)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   308 |     7  (15)| 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 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     5  (20)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        436  bytes sent via SQL*Net to client
        237  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

Well data is very very less so may be my observation is wrong but. I have few question

1. First Query has 13 and second with 11 Consistent Get does it means 2nd one is good. If so then what about Sorts it make.
2. Why it generates two plan i know first is correlated subquery but why 2nd one go for sorting and first won't as we had not said anything about sorting.