Home Dashboard Directory Help
Search

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


Status: 

Closed
 as By Design Help for as By Design


3
0
Sign in
to vote
Type: Bug
ID: 671475
Opened: 5/30/2011 3:10:22 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

why does
select 'test' where exists (select *)
not give any error since select * does?
Details
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
Hello,
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 http://bradsruminations.blogspot.com/2009/09/age-old-select-vs-select-1-debate.html
Sign in to post a workaround.