Thanks for the question, Shailesh.

Asked: April 16, 2008 - 10:17 pm UTC

Last updated: June 06, 2011 - 8:49 am UTC

Version: 8

Viewed 1000+ times

You Asked

Hi
I need to displays the details regarding the subjects for which no title exists in the titles table.

SUBJECT table
column
subject_id
subject_name

TITLE table
column
title_id
title_nm
subject_nm
publisher_nm

I have two queries
1. select*
from subjects
where NOT Exists(select * from titles
where subject.subject_id = titles.title_id)

2. select*
from subjects
where subject_id NOT Exists (select * from titles )

both the query return same result then what is the benefit for correlated query

and Tom said...

those two queries are NOT EVEN REMOTELY similar.

the first one returns every row from subjects such that there is not a row in titles with titles.title_id = subject.subject_id

the second one returns either

a) every row from subjects, if and only if titles is EMPTY
b) no rows from subjects, if and only if titles has at least one record.

Rating

(11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

learnt something new

Sokrates, April 17, 2008 - 11:10 am UTC

never saw the syntax in 2.
Could you please explain it any further.
Furthermore, it seems, that this syntax isn't supported anymore in V10:

SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
SQL> create table SUBJECT(subject_id int, subject_name int);

Table created.
SQL> create table TITLE(title_id int, title_nm int, subject_nm int, publisher_nm int);

Table created.
SQL> select*
  2  from subjects 
  3  where subject_id NOT Exists (select * from titles ) ;
where subject_id NOT Exists (select * from titles )
                     *
ERROR at line 3:
ORA-00920: invalid relational operator

April 17, 2008 - 11:55 am UTC

i think the subject_id is "a mistake" there.

They set up the subjects and titles table.

put no data into titles

and observed "the same answer"

I think Shailesh has made a mistake

rsergio, April 17, 2008 - 5:31 pm UTC

Tom,

I'm not with my crystal ball right now, but I guess the author of the question intended to put the second query as

select *
from subjects
where subject_id not in (select title_id from titles)

Put this way the queries look more similar, don't they?

April 17, 2008 - 9:58 pm UTC

if so, they are still very different.

ops$tkyte%ORA9IR2> create table s ( subject_id number );

Table created.

ops$tkyte%ORA9IR2> create table t ( title_id number );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into s values ( 1 );

1 row created.

ops$tkyte%ORA9IR2> insert into t values ( null );

1 row created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from s where not exists (select null from t where t.title_id = s.subject_id);

SUBJECT_ID
----------
         1

ops$tkyte%ORA9IR2> select * from s where subject_id not in (select title_id from t );

no rows selected

if the column you select in the subquery is NOT NULL - then not in and not exists are equivalent - and the optimizer knows that - and treats them "the same" basically.

If the column you select in the subquery is nullable - then NOT IN and NOT EXISTS are NOT interchangeable.

NOT IN and NOT EXISTS

A reader, April 17, 2008 - 6:16 pm UTC

I believe he wants to know the difference between NOT IN and NOT EXISTS. The difference is to deal with NULL for subject_id. I hope this helps, Tom.

Query

Aditya, September 27, 2010 - 5:26 am UTC

employees whose salary greater than their average salary according to their dept in employee table

September 27, 2010 - 12:11 pm UTC

the sky is blue and grass is green, but only in the spring and summer.

now what? did you mean to ask something?

Johny Alex, September 28, 2010 - 9:54 am UTC

@Aditya,

I know that you meant the EMP and DEPT table. But please mention that very clearly when you ask questions in the futuere.
I used to ask this kind of "Silly" questions some times back

Just follow the link, you will get the answer. (Point 12 of KEYS)

http://www.techfaq360.com/viewTutorial.jsp?tutorialId=327

September 28, 2010 - 11:11 am UTC

well, not only that but:

"employees whose salary greater than their average salary according to their dept in employee table"

is a statement - not a question, not a specification.

I assume they mean:

for each employee in the EMP table, compare their salary to the average salary for the department they are in <and then something happens here>

Not sure what happens there - that is left out, the rest is an assumption. If they post the actual homework/quiz question they have - maybe we can take a look.

I just read those questions - ugh, that was painful. I could write a valid disagreement with many of them.

q14 pops out.
14. TRUNCATE TABLE EMP;
DELETE FROM EMP;
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP.

the delete MIGHT result in deleting all of the rows - might not. There could be rows you don't see (other sessions have inserted but not yet committed). So the table may or may not be empty right afterwards. It could be that as your delete was executing a commit happened - which you would not see with your delete and the end result would be that there is still data in the table after you delete - however, truncate would never let that happen - it locks the table.

5. What is the parameter substitution symbol used with INSERT INTO command?
&

well, there is no such THING as a substitution symbol to be used with INSERT. There is however this thing called sqlplus which by default allows you to do some substitution using & by default, but I turn it off frequently - or set it to ^ so & is not always the answer for sqlplus either. However, as written the only correct answer is "there is not one"

2. What operator performs pattern matching?
LIKE operator

what about contains?

23. What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.

or drop a column
or drop a partition
or drop a subpartition
or drop all unused columns
or drop values for list partitions
or deallocate storage when you truncate
or ............................................

and there are more.... :(

Bixapathi, September 28, 2010 - 11:16 am UTC

Hi Aditya ,

Here you go :

Q1:

select emp_name ,sal from emp e1 where e1.sal >(select avg(e2.sal) from emp e2 where e1.dept_id=e2.dept_id)

Q2:

select e1.emp_name,e1.sal from emp e1, (select avg(sal) sal ,dept_id from emp e2 group by dept_id ) e2
where e1.sal>e2.sal and e1.dept_id=e2.dept_id

Q3:

select e.emp_name,e.sal from (select emp_name,sal,avg(sal) over (partition by dept_id) avg_sal from emp ) e
where sal>avg_sal

all of the above work well BUT ......... just have a look at the Q3 - there is only ONE 'e' - no e1 and e2 - that's why Tom always says -"ANALYTICS ROCK and ROLL"

subquery

Bixapathi, September 29, 2010 - 5:30 am UTC

Hi Tom,

I have been under the impression that the optimizer will treat the below two queries (Q1 and Q2) in the same manner 
because the Optimizer will convert the Q1 to Q2 internally as part of Query transformation and infact if we see the two plans - both the plans are same - but why there are less logical i/os in the Q2 ??
Just wanted to know about Q1 and Q2 - I know Q3 will be better than Q2 and Q3 .....

this testing done on 11gR2.
(On 9i- there are same logical i/os for Q1 and Q2) 

Employee(emp_no pk,emp_name,sal,dept_no(fk references dept(dept_no))
Department(dept_no pk,dept_name)

they are very small tables (emp=11 rows and dept=6 rows)




Q1:
SQL> select emp_name ,sal from employee e1 where e1.sal >(select avg(e2.sal) from employee e2
  2  where e1.dept_no=e2.dept_no);

Execution Plan
----------------------------------------------------------
Plan hash value: 439651729

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    38 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |          |     1 |    38 |     8  (25)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1  |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |          |     3 |    18 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEE |    11 |    66 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMPLOYEE |    11 |   132 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - access("E1"."DEPT_NO"="ITEM_1")
       filter("E1"."SAL">"AVG(E2.SAL)")


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

Q2:

SQL> select emp_name ,sal from employee e1, (select avg(sal) avg_sal,dept_no from employee 
  2  group by dept_no )  e2 where e1.sal > e2.avg_sal and e1.dept_no=e2.dept_no
  3  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2300762642

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    38 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |          |     1 |    38 |     8  (25)| 00:00:01 |
|   2 |   VIEW               |          |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |          |     3 |    18 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEE |    11 |    66 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMPLOYEE |    11 |   132 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - access("E1"."DEPT_NO"="E2"."DEPT_NO")
       filter("E1"."SAL">"E2"."AVG_SAL")


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









Q3:

SQL> select emp_name ,sal from (select emp_name,sal,avg(sal) over (partition by dept_no) avg_sal fro
m employee) e
  2  where sal> avg_sal;

E
Execution Plan
----------------------------------------------------------
Plan hash value: 1555799450

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |    11 |   473 |     4  (25)| 00:00:01 |
|*  1 |  VIEW               |          |    11 |   473 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |          |    11 |   132 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEE |    11 |   132 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter("SAL">"AVG_SAL")


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

September 29, 2010 - 9:05 am UTC

use tkprof, it'll show you the IO's step by step in the row source operation. compare those.

average salary

ROSHAN SAWARBANDHE, October 03, 2010 - 2:51 am UTC

" select department number from emp who gets highest average salary from all the department"?

October 04, 2010 - 2:09 am UTC

"the blue sky is red in the evening, for all people"

now what?

are you trying to ask something? Or just making a statement.

amardeep, October 23, 2010 - 1:36 am UTC

hi tom i work in a big Software Company in India as a developer in pl sql.
i am looking for a job in ORACLE CORPORATION.
Can u help?

ReGARD'S
amardeep
amardeepasr@yahoo.co.in

October 25, 2010 - 5:29 pm UTC

"U" isn't available, "U" is dead as far as I know. Look it up, it is true.

http://en.wikipedia.org/wiki/U_of_Goryeo

But that said, I can help. I can tell you that using "text messaging speak" like "u" and such makes you sound like a 12 year old. It is not very professional and will get you laughed at (only you won't know you were laughed at since the companies you try to get employment with simply won't contact you).

So, I would work on the communication skills - something very important as you approach people to ask them for opportunities. You never know what the person on the other end of the communication line will think of you - unless you approach them with a degree of professionalism from the very beginning.

Using full words - not "text messaging speak". Things like that are oh so very important.

That is the extent of my help - you can check oracle.com for job postings, we have a job website (click on careers from the home page) there.

Not exists & Not in

A reader, June 03, 2011 - 3:05 am UTC

Hi Tom,

U gave this example for not exists and not in difference.

ops$tkyte%ORA9IR2> create table s ( subject_id number );

Table created.

ops$tkyte%ORA9IR2> create table t ( title_id number );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into s values ( 1 );

1 row created.

ops$tkyte%ORA9IR2> insert into t values ( null );

1 row created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from s where not exists (select null from t where t.title_id = 
s.subject_id);

SUBJECT_ID
----------
         1

ops$tkyte%ORA9IR2> select * from s where subject_id not in (select title_id from t );

no rows selected


But when fire the query like select * from s where not exists (select null from t) 

no rows selected.

Why is it so.. Does it mean even for null value not exists and not in behaves in the same manner. I am new to database area. My apologies if its a silly query.

June 06, 2011 - 8:49 am UTC

Where did this "U" person give this example? It looks a lot like my example - you must be confused?

see
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:442029737684#3459917700346085168

Correlated sub query with hierarchical

Somjit, March 07, 2012 - 6:59 am UTC

Hi Tom,

I am facing a performance issue with the following code:

SELECT TRS.CycleId
FROM current_1218232753 XIBT_OUT, current_2113697630 TRS
WHERE To_Number(To_Char(XIBT_OUT.CreateDate,'YYYYMMDD')||REPLACE(XIBT_OUT.Createtime,':'))
      BETWEEN To_Number('20120225000000') AND To_Number('20120225000020')
AND TRS.CycleId = (SELECT DISTINCT(Q.ParentCycleId) AS ROOT
                            FROM cyclelink Q
                            WHERE connect_by_isleaf = 1
                               AND Q.ParentObjectId = '2113697630'                                     -- TIX_EDIEL_TRS identity 
                             START WITH Q.ChildCycleId = XIBT_OUT.CycleId
                             CONNECT BY NOCYCLE PRIOR Q.ParentCycleId = Q.ChildCycleId
                                                  AND Q.ParentObjectId = '2113697630'       -- TIX_EDIEL_TRS identity 
                          ) ;

Execution plan:

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                         |     1 |    84 |  1242M  (7)|999:59:59 |
|   1 |  NESTED LOOPS                          |                         |     9 |   756 |    22   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID          | CURRENT_1218232753      |     9 |   315 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                    | IDX_CURR1218232753_DATE |     2 |       |     3   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN                    | CYCLEIDCUR_2113697630   |     1 |    49 |     2   (0)| 00:00:01 |
|   5 |    HASH UNIQUE                         |                         |     2 |   534 |    15   (7)| 00:00:01 |
|*  6 |     FILTER                             |                         |       |       |            |          |
|*  7 |      CONNECT BY WITH FILTERING (UNIQUE)|                         |       |       |            |          |
|   8 |       TABLE ACCESS BY INDEX ROWID      | CYCLELINK               |     1 |    79 |     5   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN                | ChildCycleIdCycle_1     |     1 |       |     4   (0)| 00:00:01 |
|  10 |       NESTED LOOPS                     |                         |     1 |   206 |     9   (0)| 00:00:01 |
|  11 |        CONNECT BY PUMP                 |                         |       |       |            |          |
|* 12 |        TABLE ACCESS BY INDEX ROWID     | CYCLELINK               |     1 |    79 |     4   (0)| 00:00:01 |
|* 13 |         INDEX RANGE SCAN               | ChildCycleIdCycle_1     |     1 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

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

   3 - access(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("CREATEDATE"),'YYYYMMDD')||REPLACE("CREATETIME",':'))>=2
              0120225000000 AND TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("CREATEDATE"),'YYYYMMDD')||REPLACE("CREATETIME",':')
              )<=20120225000020)
   4 - access("TRS"."CYCLEID"= (SELECT DISTINCT "Q"."PARENTCYCLEID" FROM "CYCLELINK" "Q" WHERE
              CONNECT_BY_ISLEAF=1 AND "Q"."PARENTOBJECTID"=2113697630 START WITH "Q"."CHILDCYCLEID"=:B1 CONNECT BY
              NOCYCLE "Q"."CHILDCYCLEID"=PRIOR "Q"."PARENTCYCLEID" AND "Q"."PARENTOBJECTID"=2113697630))
   6 - filter(CONNECT_BY_ISLEAF=1 AND "Q"."PARENTOBJECTID"=2113697630)
   7 - access("Q"."CHILDCYCLEID"=PRIOR "Q"."PARENTCYCLEID")
       filter("Q"."PARENTOBJECTID"=2113697630)
   9 - access("Q"."CHILDCYCLEID"=:B1)
  12 - filter("Q"."PARENTOBJECTID"=2113697630)
  13 - access("connect$_by$_pump$_004"."PRIOR Q.ParentCycleId "="Q"."CHILDCYCLEID")

Is there someway I can skip the correlated subquery and use some other alternatives?

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

learnt something new

I think Shailesh has made a mistake

NOT IN and NOT EXISTS

Query

subquery

average salary

Not exists & Not in

Correlated sub query with hierarchical

More to Explore

Live SQL

Dev Gym

Correlated Subquery

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

learnt something new

I think Shailesh has made a mistake

NOT IN and NOT EXISTS

Query

subquery

average salary

Not exists & Not in

Correlated sub query with hierarchical

More to Explore

Live SQL

Dev Gym