[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/EXISTSSubqueriesSELECT1VsSELECT.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.