Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Unanswered: Subqueries with EXISTS

    Hello,
    Can anyone tell me the difference between the following sql statements:

    update aaron_table set user_dept='OK' where exists (select user_login,user_name from users where aaron_table.user_login=users.user_login and aaron_table.user_name=users.user_name)

    update aaron_table set user_dept='OK' where exists (select user_login from users where aaron_table.user_login=users.user_login and aaron_table.user_name=users.user_name)

    update aaron_table set user_dept='OK' where exists (select * from users where aaron_table.user_login=users.user_login and aaron_table.user_name=users.user_name)

    Is there a difference in what is returned depending on the column of the subselect or do all 3 generally produce the same results. I have seen a considerable perfromace increase by avoiding the '*' usage in Oracle. Oracle will allow multiple columns in subquery select list whereas Sybase will not. Trying to find the happy medium!

    Thank You

  2. #2
    Join Date
    Apr 2002
    Posts
    56
    From what I can see quickly (it's rather late now in The Netherlands) these 3 queries will give the same results.
    A common practice is to use 'where exists (select 1 from tablename etc...)'
    You just have to determine whether the (sub)selection gives results, not select all kinds of columns from it.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Right, with an exists, you're just expecting TRUE (I found at least one record) or FALSE (no records found).

    -cf

  4. #4
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    Re: Subqueries with EXISTS

    Originally posted by newjuicer
    Hello,
    Can anyone tell me the difference between the following sql statements:

    update aaron_table set user_dept='OK' where exists (select user_login,user_name from users where aaron_table.user_login=users.user_login and aaron_table.user_name=users.user_name)

    update aaron_table set user_dept='OK' where exists (select user_login from users where aaron_table.user_login=users.user_login and aaron_table.user_name=users.user_name)

    update aaron_table set user_dept='OK' where exists (select * from users where aaron_table.user_login=users.user_login and aaron_table.user_name=users.user_name)

    Is there a difference in what is returned depending on the column of the subselect or do all 3 generally produce the same results. I have seen a considerable perfromace increase by avoiding the '*' usage in Oracle. Oracle will allow multiple columns in subquery select list whereas Sybase will not. Trying to find the happy medium!

    Thank You
    Dont waste ur energy by selecting many columns when you are using exists because exists check just two conditions; ie whether the subquery returned any value or not.
    Thanks and Regards,

    Praveen Pulikunnu

Posting Permissions

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