none
while using exist shoud i use * or 1 if col name RRS feed

  • Question

  • hi,

    Please tel me which method is good and fast

    1) select a,b from t1 where exists (select 1 from t2 where t1.t1id = t2.t1id  )

    2) select a,b from t1 where exists (select * from t2 where t1.t1id = t2.t1id  )

    3) select a,b from t1 where exists (select t2.t1id from t2 where t1.t1id = t2.t1id  )

    yours sincerely

     <html:div data-url="https://social.msdn.microsoft.com/Forums/en-US/newthread?category=sqlserver&forum=transactsql" id="link64_adl_tabid" style="display:none;">403</html:div>
    <html:div data-url="https://social.msdn.microsoft.com/Forums/en-US/a4996eb1-c9a5-4542-ac3f-5c9e87172ddf/while-using-exist-shoukld-i-use-or-1-if-col-name?forum=transactsql" id="link64_adl_tabid" style="display:none;">405</html:div>
    • Edited by rajemessage Tuesday, March 8, 2016 11:53 AM
    Tuesday, March 8, 2016 11:52 AM

Answers

  • It does not matter what you use with SELECT inside EXISTS ()

    Because, as soon as EXIST get 1 row it exits, and it just ignores the column's list in SELECT clause.

    You can check by this by simple example:

    select a,b from t1 where exists (select 1/0 from t2 where t1.t1id = t2.t1id)

    The SELECT 1/0 ideally should give divide-by-zero error, but here inside EXISTS() it does not.

    So, I use "SELECT *" happily within EXISTS() clause :)

    select a,b from t1 where exists (select * from t2 where t1.t1id = t2.t1id)


    ~manoj | email: http://scr.im/m22g
    http://SQLwithManoj.com
    MCCA 2011 | My FB Page

    Tuesday, March 8, 2016 2:18 PM
  • The optimizer is smart enough to figure out the exist () predicates are the same. However, this was not always true in early versions of SQL. Oracle originally liked using a constant or an actual column name, because it is it did not have a good optimizer. The original SQL–86 specs implied (but did not require) that the table be materialized. We really were not very smart in those days.

    Today, for stylistic reasons and to make searching easier, we prefer exists (SELECT * FROM ..) In code. This makes it easier to locate the exist predicates with this text search, and shows that the operation is performed on an entire table rather than a single column.

    SQL is much like a natural language; you can be understood if you speak with poor grammar, but people respect you more if you have good grammar and style. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by rajemessage Saturday, April 30, 2016 5:19 AM
    Tuesday, March 8, 2016 5:04 PM

All replies

  • They are equal.

    The optimizer does generate an operation for SELECT, cause it is not necessary for evaluating the EXISTS() predicate.


    Tuesday, March 8, 2016 12:10 PM
  • All of those are OK.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, March 8, 2016 12:30 PM
    Answerer
  • Hi Raj,

    It doesn't matter if we use SELECT 1 /SELECT * /SELECT Column, EXISTS will always acts like a subquery and it returns boolean value. 

    But most of Code snippets,Microsoft will use EXISTS(SELECT *)

    1) select a,b from t1 where exists (select 1 from t2 where t1.t1id = t2.t1id  )

    2) select a,b from t1 where exists (select * from t2 where t1.t1id = t2.t1id  )

    3) select a,b from t1 where exists (select t2.t1id from t2 where t1.t1id = t2.t1id  )

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit 


    Srinivasarao G, MCSE(Business Intelligence) Blog:http://sqlcart.blogspot.in

    Tuesday, March 8, 2016 12:55 PM
  • It does not matter what you use with SELECT inside EXISTS ()

    Because, as soon as EXIST get 1 row it exits, and it just ignores the column's list in SELECT clause.

    You can check by this by simple example:

    select a,b from t1 where exists (select 1/0 from t2 where t1.t1id = t2.t1id)

    The SELECT 1/0 ideally should give divide-by-zero error, but here inside EXISTS() it does not.

    So, I use "SELECT *" happily within EXISTS() clause :)

    select a,b from t1 where exists (select * from t2 where t1.t1id = t2.t1id)


    ~manoj | email: http://scr.im/m22g
    http://SQLwithManoj.com
    MCCA 2011 | My FB Page

    Tuesday, March 8, 2016 2:18 PM
  • The optimizer is smart enough to figure out the exist () predicates are the same. However, this was not always true in early versions of SQL. Oracle originally liked using a constant or an actual column name, because it is it did not have a good optimizer. The original SQL–86 specs implied (but did not require) that the table be materialized. We really were not very smart in those days.

    Today, for stylistic reasons and to make searching easier, we prefer exists (SELECT * FROM ..) In code. This makes it easier to locate the exist predicates with this text search, and shows that the operation is performed on an entire table rather than a single column.

    SQL is much like a natural language; you can be understood if you speak with poor grammar, but people respect you more if you have good grammar and style. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by rajemessage Saturday, April 30, 2016 5:19 AM
    Tuesday, March 8, 2016 5:04 PM