Query Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Query Question

  1. #1
    Join Date
    Mar 2001
    Posts
    286

    Query Question

    I ran into a wierd question in my project. Here I used a simple example to demo how it happen. The DEPT table does not have the column sal. Why it returns all rows when you do: select * from emp where sal in (select sal from dept);




    SQL> select * from emp;

    EMPNO ENAME JOB MGR HIREDATE SAL
    ---------- ---------- --------- ---------- --------- ----------
    7369 SMITH CLERK 7902 17-DEC-80 800
    7499 ALLEN SALESMAN 7698 20-FEB-81 1600
    7521 WARD SALESMAN 7698 22-FEB-81 1250
    7566 JONES MANAGER 7839 02-APR-81 2975
    7654 MARTIN SALESMAN 7698 28-SEP-81 1250
    7698 BLAKE MANAGER 7839 01-MAY-81 2850
    7782 CLARK MANAGER 7839 09-JUN-81 2450
    7788 SCOTT ANALYST 7566 19-APR-87 3000
    7839 KING PRESIDENT 17-NOV-81 5000
    7844 TURNER SALESMAN 7698 08-SEP-81 1500
    7876 ADAMS CLERK 7788 23-MAY-87 1100
    7900 JAMES CLERK 7698 03-DEC-81 950
    7902 FORD ANALYST 7566 03-DEC-81 3000
    7934 MILLER CLERK 7782 23-JAN-82 1300

    14 rows selected.

    SQL> select * from dept;

    DEPTNO DNAME LOC
    ---------- -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS BOSTON

    4 rows selected.

    SQL> select sal from dept;
    select sal from dept
    *
    ERROR at line 1:
    ORA-00904: "SAL": invalid identifier


    SQL> select * from emp where sal in (select sal from dept);

    EMPNO ENAME JOB MGR HIREDATE SAL
    ---------- ---------- --------- ---------- --------- ----------
    7369 SMITH CLERK 7902 17-DEC-80 800
    7499 ALLEN SALESMAN 7698 20-FEB-81 1600
    7521 WARD SALESMAN 7698 22-FEB-81 1250
    7566 JONES MANAGER 7839 02-APR-81 2975
    7654 MARTIN SALESMAN 7698 28-SEP-81 1250
    7698 BLAKE MANAGER 7839 01-MAY-81 2850
    7782 CLARK MANAGER 7839 09-JUN-81 2450
    7788 SCOTT ANALYST 7566 19-APR-87 3000
    7839 KING PRESIDENT 17-NOV-81 5000
    7844 TURNER SALESMAN 7698 08-SEP-81 1500
    7876 ADAMS CLERK 7788 23-MAY-87 1100
    7900 JAMES CLERK 7698 03-DEC-81 950
    7902 FORD ANALYST 7566 03-DEC-81 3000
    7934 MILLER CLERK 7782 23-JAN-82 1300

    14 rows selected.

    SQL>

  2. #2
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Because in subquery's scope emp.sal is still visible.
    Basically you are doing this:
    select * from emp e
    where e.sal in (select e.sal from dept) --which is always true

    But it really looks strange, doesn't it...
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by TomazZ
    But it really looks strange, doesn't it...
    It isnt strange...

    Subquery will actually go for Cartisian Product because you are trying to use Emp Col ( as SAL col aint in DEPT ), but aint joining with DEPT.

    PS below demo.

    Code:
    TEST:ABHAY> desc TEST_SUBQUERY
     Name                                   
     --------------------------------
     ID                                     
     ID1                                    
     ID2                                    
    
    TEST:ABHAY> desc TESTIN
     Name                                                                                                           
     ----------------------
     IP                                                                                                             
    
    TEST:ABHAY> select count(1) from TEST_SUBQUERY where id  in ( select id from testin  );
    
    
                count(1)
    --------------------
                  100000
    
    TEST:ABHAY> delete from TESTIN;
    
    1 row deleted.
    
    TEST:ABHAY> commit;
    
    Commit complete.
    
    TEST:ABHAY> select count(1) from TESTIN;
    
    
                count(1)
    --------------------
                       0
    
    TEST:ABHAY> select count(1) from TEST_SUBQUERY where id  in ( select id from testin  );
    
    
                count(1)
    --------------------
                       0

    So, if you didnt have any Records in DEPT table, subquery wont fetch you any rows to be compared with Sal col in main query.


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by abhaysk
    It isnt strange...
    Well, I meant that it really can look strange at first sight, but when you think about it...
    Originally posted by abhaysk
    Subquery will actually go for Cartisian Product
    It's not a carteisan product. Carteisan product would give you 200000 when you would have 2 rows in testin, this doesn't.
    Originally posted by abhaysk
    where e.sal in (select e.sal from dept) --which is always true
    You threw me a little light here. This condition is true only as long as dept has at least one row.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by TomazZ
    It's not a carteisan product. Carteisan product would give you 200000 when you would have 2 rows in testin, this doesn't.
    Count = 200000 , by this do u mean Main Query Result ???

    Well I aint told you Main Query will give a Cartisien Product..it the sub query i was talking about.

    I know when therz one record in a table Cartisien Product is of no meaning ( coz 1*1=1 or x*1=x ). Its only when you have more than a Rec...in 2 or more tables without JOIN or so.

    I just kept 1 rec and no rec in my TESTIN table to let you know the query will return data if you have some data in that TESTIN table.

    BTW, in any case may it be 2 or more RECS in TESTIN table, you will get COUNT 10000 ( MAIN QUERY RESULT ).
    Code:
    TEST:ABHAY> select count(1) from Testin;
    
    
                COUNT(1)
    --------------------
                       2
    
    TEST:ABHAY> Select count(*) from Test_Rollback where ID in ( select ID from Testin );
    
    
                COUNT(*)
    --------------------
                  100000
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Surely I meant Main Query Result.
    Subquery itself makes one set of records.
    There is no such thing as Cartesian product on one set.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    Subquery will actually go for Cartisian Product because ....
    There ain't any joins in the query, so how could there be a certesian product?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by TomazZ
    Surely I meant Main Query Result.
    As you said the Count would be 20000, if i had 2 recs in TESTIN table..


    It's not a carteisan product. Carteisan product would give you 200000 when you would have 2 rows in testin, this doesn't.

    That aint the case, as i showed you in my previous post.
    The Main Query Result ( 10000 ) remains same irresective of the number of recs in Testin table ( as you a IN clause for joining multiple Rec set ).

    Originally posted by TomazZ

    Subquery itself makes one set of records.
    I was mentioning that this record set ( in our case ), would be 2 times if 2 recs in TESTIN & 3 times if 3 recs in Testin table and so....

    Originally posted by TomazZ

    There is no such thing as Cartesian product on one set.
    I dont get you.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    errr do you know what is/causes cartisian product...?

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    There ain't any joins in the query, so how could there be a certesian product?
    My Mistake..
    Last edited by abhaysk; 06-13-2003 at 08:08 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

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



Click Here to Expand Forum to Full Width