Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: What is Select 1 For?

    Hey guys,

    This may be a stupid question, but I can't figure out what the SELECT 1 is actually used for. For example, the query statement shows it here:


    AND NOT EXISTS

    (SELECT 1 from MBR_HIS where HIST_CD = '10')


    I tried running the statement by itself and the display result is a bunch of 1's. Why not use SELECT * instead?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What difference does it make? The subselect doesn't return anything and is only used for an existence check. Using "SELECT 1" is the typical convention for that. You can also use "SELECT *" (but that is usually frowned upon because you should always specify exactly what you want). DB2 will optimize the "*" away because executing it would be more expensive than just returning the constant 1.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is a bad idea to use "select *" in any application program, even if the DB2 optimizer converts it to "select 1" or something similar.

    In the old days, the DB2 optimizer did not convert "select *" to "select 1" in a "where exists" or similar query, so you ended up having DB2 actually process all those columns in the sub-query, which was not very efficient. In fact, there were serious debates at the time about whether "select '1' " was more efficient than "select 1" or "select <primary-key-column>". That was awhile ago.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    instead of SELECT 1, i will often use either SELECT 937 or SELECT NULL

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by r937
    instead of SELECT 1, i will often use either SELECT 937 or SELECT NULL
    Dogs and consultants like to leave to leave their mark so everyone knows they were there.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Marcus_A
    Dogs and consultants like to leave to leave their mark so everyone knows they were there.
    not everyone -- just those who, in the immortal words of mike wilbon, can "slurp the love"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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