select 'test' where exists (select *) - by rgarvao

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 671475 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 5/30/2011 3:10:22 AM
Access Restriction Public


why does 
select 'test' where exists (select *)
not give any error since select * does?
Sign in to post a comment.
Posted by rgarvao on 6/1/2011 at 1:14 AM
Thank you very much for the explanation.

Keep up the good work

Ricardo Garvão
Posted by Microsoft on 5/31/2011 at 4:26 PM
Thanks for your feedback. The behavior you are seeing is by design. Before I explain the SQL Server behavior, let us look at the ANSI SQL behavior or specification regarding SELECT statement. In ANSI SQL, a SELECT statement without FROM clause is not permitted - you need to specify a table source. So the statement "SELECT 'test' WHERE EXISTS(SELECT *)" should give syntax error. This is the correct behavior.
With respect to the SQL Server implementation, the FROM clause is optional and it has always worked this way. So you can do "SELECT 1" or "SELECT @v" and so on without requiring a table. In other database systems, there is a dummy table called "DUAL" with one row that is used to do such SELECT statements like "SELECT 1 FROM dual;" or "SELECT @v FROM dual;". Now, coming to the EXISTS clause - the project list doesn't matter in terms of the syntax or result of the query and SELECT * is valid in a sub-query. Couple this with the fact that we allow SELECT without FROM, you get the behavior that you see. We could fix it but there is not much value in doing it and it might break existing application code.
So I am resolving and closing this as "by design".

Umachandar, SQL Programmability Team
Posted by Martin Smith on 5/31/2011 at 12:33 AM
I guess we can take this as evidence that there really is no difference between doing "SELECT 1" and "SELECT *" in an Exists query. You might find this post of interest