April 3, 2008 at 12:26 pm
Hi,
Recently, I ran a rather interesting query.
I have a tableA with column and data as under
ID Name
[p]
[/p]
Now I run a query
select *
from tableA
where ID = 1
This returns the row
Now, I run
select *
from tableA
where ID = 1
and ID != 1
In this case no rows are returned.
Why and how does this happen? What decides which filter to use when there are more than one filters that contradict each other?
Thanks,
Suhas
April 3, 2008 at 12:35 pm
How many rows in your table would you estimate meet the following condition?
where ID = 1 and ID != 1
April 3, 2008 at 12:46 pm
well there could be 10 rows with ID = 1 and another 10 rows with ID != 1
April 3, 2008 at 12:52 pm
How many would you estimate meet both conditions?
April 3, 2008 at 12:54 pm
suhas.wadadekar (4/3/2008)
well there could be 10 rows with ID = 1 and another 10 rows with ID != 1
Remember that LOGICAL AND is the "opposite" concept of what "conversational language AND" might mean.
In order for a row to qualify through a LOGICAL AND statement, both sides of the AND would have to be true for that row. So - "perfectly contradictory AND" statements will return 0 rows. Every time.
You're looking for an OR, not an AND. Meaning - "ID=1 OR ID!=1" will return both rows EQUAL to 1 and rows NOT equal to 1.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 3, 2008 at 12:55 pm
where ID = 1 and ID != 1
Okay, let's put this query into words. I want all rows where the ID equals 1 AND the ID does not equal 1. For this to be true, both must be true, but how can a single value in ID both equal and not equal 1?
If your table had 10 rows whaere ID = 1 and 10 rows where ID != 1, your query would be thus
select * from MyTable
if you insisted on a where clause, then this would be the where clause:
where ID = 1 OR ID != 1
Does all this make sense?
😎
April 3, 2008 at 1:30 pm
"What decides which filter to use when there are more than one filters that contradict each other?"
Since you used "and", it uses both filters at the same time on all rows. It doesn't decide which to use, it uses both.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 1:33 pm
Got it.
Thanks all. The explanation with AND/OR logic was a good one.
Made me feel completely stupid :hehe:
I did feel at the back of my mind that this question had something to do with utter basics than anything else..
Thanks again
April 3, 2008 at 1:39 pm
Don't feel stupid. The computer version of "and/or" throws a lot of people. Takes a while to get used to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 1:40 pm
Lynn Pettis (4/3/2008)
If your table had 10 rows whaere ID = 1 and 10 rows where ID != 1, your query would be thusselect * from MyTable
if you insisted on a where clause, then this would be the where clause:
where ID = 1 OR ID != 1
Does all this make sense?
😎
At the risk of being accused of splitting hairs, the following two queries are NOT the same:
Select * from mytable
vs.
Select * from mytable where ID=1 or ID!=1
The second one would evaluate
Select * from mytable where ID is not null
😀
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 3, 2008 at 1:46 pm
Matt Miller (4/3/2008)
Lynn Pettis (4/3/2008)
If your table had 10 rows whaere ID = 1 and 10 rows where ID != 1, your query would be thusselect * from MyTable
if you insisted on a where clause, then this would be the where clause:
where ID = 1 OR ID != 1
Does all this make sense?
😎
At the risk of being accused of splitting hairs, the following two queries are NOT the same:
Select * from mytable
vs.
Select * from mytable where ID=1 or ID!=1
The second one would evaluate
Select * from mytable where ID is not null
😀
Okay, fine. Let's add one more criteria to the mix, in the table definition ID is not null. Wait, wait, I have it! Another QOTD that everyone can complain about!
:hehe:
April 3, 2008 at 1:49 pm
Of course, to take this to an even more ridiculous level, if ID is the PK (or otherwise not nullable), you don't have to worry about it being null, which means they would all return the same records. On the other hand, all three also end up with the same execution plan in that case (SQL ignores the Where clauses in all three cases).
On a nullable collumn, the "=1 or !=1" gets changed to "is not null" in the execution plan, just like Matt said.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 1:50 pm
GSquared (4/3/2008)
Don't feel stupid. The computer version of "and/or" throws a lot of people. Takes a while to get used to.
And just wait until you start having to add NOT logic to get the answers you are looking for, it gets to be even more fun!
😎
April 3, 2008 at 1:51 pm
Lynn Pettis (4/3/2008)[/b
Okay, fine. Let's add one more criteria to the mix, in the table definition ID is not null. Wait, wait, I have it! Another QOTD that everyone can complain about!
:hehe:
Better watch out! You don't want us to go all "QOTD Lawyer" on you! 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 2:03 pm
No, No, really, this would be good:
Are these two queries equivalent?
select
*
from
dbo.MyTable
and
select
*
from
dbo.MyTable
where
ID = 1
or ID <> 1
Answers:
Yes (Wrong)
No (Wrong)
It Depends (Correct)
You don't think this would be good???
:hehe:
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply