[dba-SQLServer] interesting trick

You realize there is absolutely no benefit to 'exists (select 1' vs.
'exist(select column' vs. 'exist (select *'

The QP simply looks for the presence of a row. There is no real tip in this
thread. 

-Paul

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav
Brock
Sent: Sunday, February 10, 2008 3:51 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] interesting trick

Hi Susan and Paul

The tip is to use:

SELECT col1 FROM MyTable WHERE EXISTS 
  (SELECT 1 FROM Table2 WHERE MyTable.col1=Table2.col2)

to retrieve one column only and not:

SELECT col1 FROM MyTable WHERE EXISTS 
  (SELECT * FROM Table2 WHERE MyTable.col1=Table2.col2)

But if so why not retrieve zero columns as you can return a constant:

SELECT col1 FROM MyTable WHERE EXISTS 
  (SELECT 1 AS Expr1 FROM Table2 WHERE MyTable.col1=Table2.col2)

Further, the trick may not be a trick at all as at least SQL Server
Management Studio doesn't allow the "SELECT 1 FROM Table .. " syntax - it
corrects it at once to "SELECT 1 AS Expr1 FROM Table .."

/gustav

>>> ssharkins at gmail.com 10-02-2008 16:03 >>>
http://www.sqlskills.com/blogs/conor/2008/02/07/EXISTSSubqueriesSELECT1VsSEL
ECT.aspx 

I've never even checked to see if SQL Server supports this SELECT syntax. 
Never needed it and can't imagine using it, but in this one particular case,

seems like a good choice.

Susan H. 



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


__________ NOD32 2862 (20080210) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com