June 23, 2015 at 10:05 pm
Comments posted to this topic are about the item What is the Output?
Thanks,
Shiva N
Database Consultant
June 24, 2015 at 12:54 am
I guess the explanation is quite simple:
Divide by 0 is not causing a problem in the SELECT clause because of the presence of EXISTS. You can have pretty much anything in the SELECT and the result will be the same because all EXISTS does is evaluate for the existance of a record in the result set. Whether the record exists or not (the FROM and WHERE clauses) does not have any bearing on how it is represented (which is the job of the SELECT).
The divide by 0 will cause issues if it is in the WHERE clause, because that needs to be evaluated fully. Here's the snippet:
--Query 1:
if exists(select 1/0 from @tbl where id=10 AND (1/0)=1)
select 1
else
select 2
However, here's a twist to the tale. The following will *not* return an error:
--Query 1:
if exists(select 1/0 from @tbl where id=10 AND 1=0 AND (1/0)=1)
select 1
else
select 2
This is because the database engine evaluates the constant expression (1=0), finds it to be FALSE and therefore the logical AND acts as a short circuit operator because of which the part [(1/0)=1] is not evaluated at all.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
June 24, 2015 at 1:06 am
nicely put, good question. thanks for sharing ...
June 24, 2015 at 1:36 am
We cannot find a reference, but apparently the divide by zero doesn't cause an issue in the EXISTS() clause.
The examples in this question are actually quite common. They are very often posted in the ever-returning discussion on EXISTS (SELECT * ...) vs EXISTS (SELECT 1 ...) (or any other constant other than 1 - lately NULL appears to be popular). The myth that SELECT * is expanded into a column list still persists, even though that stopped being true several decades ago. During that discussion, there's always someone who posts the 1/0 example to prove that it doesn't matter what you put in the EXISTS.
There is no very explicit reference, but I think that this link is valuable: https://technet.microsoft.com/en-us/library/ms188336.aspx. It mentions that EXISTS returns true if the subquery contains any rows. From this, one can conclude that the content of those rows is irrelevant. This is somewhat reinforced by example A.
Nakul Vachhrajani (6/24/2015)
However, here's a twist to the tale. The following will *not* return an error:
--Query 1:
if exists(select 1/0 from @tbl where id=10 AND 1=0 AND (1/0)=1)
select 1
else
select 2
This is because the database engine evaluates the constant expression (1=0), finds it to be FALSE and therefore the logical AND acts as a short circuit operator because of which the part [(1/0)=1] is not evaluated at all.
Correction: that code **MIGHT** not return an error. Or it might. The optimizer determines in what order to evaluate the expressions, so short-circuiting is not guaranteed.
It is very common to see starting SQL developers get upset when a query that uses "SELECT ..., Something / Divisor, ... FROM ... WHERE Divisor <> 0" result in an error - but that is not a bug (it is a nuisance, though!), the optimizer is free to do the division before applying the filter.
June 24, 2015 at 2:44 am
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 24, 2015 at 2:45 am
Hugo Kornelis (6/24/2015)
The examples in this question are actually quite common. They are very often posted in the ever-returning discussion on EXISTS (SELECT * ...) vs EXISTS (SELECT 1 ...) (or any other constant other than 1 - lately NULL appears to be popular). The myth that SELECT * is expanded into a column list still persists, even though that stopped being true several decades ago. During that discussion, there's always someone who posts the 1/0 example to prove that it doesn't matter what you put in the EXISTS.
Ha, I learned about 1/0 in EXISTS because Gail mentioned this trick in a discussion with indeed the subject about what to put in an EXISTS statement 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 24, 2015 at 3:49 am
This was removed by the editor as SPAM
June 24, 2015 at 5:21 am
Great example to illustrate a point. Nice question and follow up discussion. I was always in the SELECT 1 camp myself, but admit that it came from Oracle many years ago. I just learned that it doesn't matter. Thank you.
June 24, 2015 at 6:58 am
Nakul Vachhrajani (6/24/2015)
I guess the explanation is quite simple:Divide by 0 is not causing a problem in the SELECT clause because of the presence of EXISTS. You can have pretty much anything in the SELECT and the result will be the same because all EXISTS does is evaluate for the existance of a record in the result set. Whether the record exists or not (the FROM and WHERE clauses) does not have any bearing on how it is represented (which is the job of the SELECT).
The divide by 0 will cause issues if it is in the WHERE clause, because that needs to be evaluated fully. Here's the snippet:
--Query 1:if exists(select 1/0 from @tbl where id=10 AND (1/0)=1)
select 1
else
select 2
However, here's a twist to the tale. The following will *not* return an error:
--Query 1:
if exists(select 1/0 from @tbl where id=10 AND 1=0 AND (1/0)=1)
select 1
else
select 2
This is because the database engine evaluates the constant expression (1=0), finds it to be FALSE and therefore the logical AND acts as a short circuit operator because of which the part [(1/0)=1] is not evaluated at all.
Pretty sure you can *not* rely on that short-circuiting. It may work 9 times out of 10 then fail.
Gerald Britton, Pluralsight courses
June 24, 2015 at 7:37 am
Hugo Kornelis (6/24/2015)
Correction: that code **MIGHT** not return an error. Or it might. The optimizer determines in what order to evaluate the expressions, so short-circuiting is not guaranteed.It is very common to see starting SQL developers get upset when a query that uses "SELECT ..., Something / Divisor, ... FROM ... WHERE Divisor <> 0" result in an error - but that is not a bug (it is a nuisance, though!), the optimizer is free to do the division before applying the filter.
This confused me greatly the first time I ran into it. A fellow developer had written a query similar to your example that was initially worked just fine, but suddenly stopped working as the table grew larger. It was a long time before I understood why.
June 24, 2015 at 7:39 am
Cool question, thanks.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
June 24, 2015 at 7:40 am
Hugo Kornelis (6/24/2015)
We cannot find a reference, but apparently the divide by zero doesn't cause an issue in the EXISTS() clause.
The examples in this question are actually quite common. They are very often posted in the ever-returning discussion on EXISTS (SELECT * ...) vs EXISTS (SELECT 1 ...) (or any other constant other than 1 - lately NULL appears to be popular). The myth that SELECT * is expanded into a column list still persists, even though that stopped being true several decades ago. During that discussion, there's always someone who posts the 1/0 example to prove that it doesn't matter what you put in the EXISTS.
There is no very explicit reference, but I think that this link is valuable: https://technet.microsoft.com/en-us/library/ms188336.aspx. It mentions that EXISTS returns true if the subquery contains any rows. From this, one can conclude that the content of those rows is irrelevant. This is somewhat reinforced by example A.
Nakul Vachhrajani (6/24/2015)
However, here's a twist to the tale. The following will *not* return an error:
--Query 1:
if exists(select 1/0 from @tbl where id=10 AND 1=0 AND (1/0)=1)
select 1
else
select 2
This is because the database engine evaluates the constant expression (1=0), finds it to be FALSE and therefore the logical AND acts as a short circuit operator because of which the part [(1/0)=1] is not evaluated at all.
Correction: that code **MIGHT** not return an error. Or it might. The optimizer determines in what order to evaluate the expressions, so short-circuiting is not guaranteed.
It is very common to see starting SQL developers get upset when a query that uses "SELECT ..., Something / Divisor, ... FROM ... WHERE Divisor <> 0" result in an error - but that is not a bug (it is a nuisance, though!), the optimizer is free to do the division before applying the filter.
Thanks, Hugo, for this additional explanation!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
June 24, 2015 at 8:38 am
I think its not because of divide by zero issue. The results appear as it matches condition 1 so results 1, condition no#2 does not match condition, so result = 2 (elsE)
Thanks.
June 24, 2015 at 9:57 am
Koen Verbeeck (6/24/2015)
Hugo Kornelis (6/24/2015)
The examples in this question are actually quite common. They are very often posted in the ever-returning discussion on EXISTS (SELECT * ...) vs EXISTS (SELECT 1 ...) (or any other constant other than 1 - lately NULL appears to be popular). The myth that SELECT * is expanded into a column list still persists, even though that stopped being true several decades ago. During that discussion, there's always someone who posts the 1/0 example to prove that it doesn't matter what you put in the EXISTS.
Ha, I learned about 1/0 in EXISTS because Gail mentioned this trick in a discussion with indeed the subject about what to put in an EXISTS statement 😀
I remember those discussions rather well. Those are the good ol' days. 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 24, 2015 at 9:58 am
EZ PZ lemon squeezy. That said, it is a rather good lesson as Hugo laid out for us.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply