AND Operator in SQL

Question

AND Operator in SQL

jignesh209

SSC Eights!

Points: 959
More actions
March 5, 2012 at 12:15 pm

#258119

I have an Item table and it has ItemTypeId as on of the column.
Now when I write the following query,it does not return any rows.
There are Items with ItemTypeId 1 and 3.
SELECT ItemId FROM Item WHERE (ItemTypeId=1 AND ItemTypeId=3)
But,if i run the query with OR,it works
SELECT ItemId FROM Item WHERE (ItemTypeId=1 OR ItemTypeId=3)
(ItemTypeId=1 AND ItemTypeId=3) evaluates to True as both ItemTypeId are in DB,so why is the AND operator fails.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

jigsm_shah (3/5/2012)
I have an Item table and it has ItemTypeId as on of the column.
Now when I write the following query,it does not return any rows.
There are Items with ItemTypeId 1 and 3.
SELECT ItemId FROM Item WHERE (ItemTypeId=1 AND ItemTypeId=3)

What you are asking for here is for rows where ItemTypeID is both 1 and 3 at the same time. AND does not work across rows, it looks at the column values for a single row and for a single row a single column cannot have two values at the same time. It's either 1 or it's 3, but there is no way that for a single row (which is what the WHERE clause functions on) to have both true at the same time

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

The WHERE clause is applied to each row in a set, so while a set of rows may contain individual rows with ItemTypeID of 1 and 3, there is not a single row that contains 1 and 3 in ItemTypeID because each row only has one value in ItemTypeID.

ItemTypeID is a single column and no single column can contain 2 values. So here's your example kind of spelled out:

ItemIDItemTypeID

113

212

311

So in your example using AND there is no case where a single row contains both 1 and 3 in ItemTypeID so you get no rows returned.

When you want multuple values from a single column you need to use OR, when you want a combination of values from multiple columns you use AND.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Try it this way:

SELECT ItemId FROM Item WHERE ItemTypeId=1

INTERSECT

SELECT ItemId FROM Item WHERE ItemTypeId=3

----------------------------------------------------------------------------------
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?

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Matt Miller (#4) (3/5/2012)
Try it this way:
SELECT ItemId FROM Item WHERE ItemTypeId=1
INTERSECT
SELECT ItemId FROM Item WHERE ItemTypeId=3

I think the OP wants to find all items with either type. Just a guess but I suspect that ItemId is the primarykey which means the INTERSECT is not really what they are after.

Is this what you were looking for?

select ItemId from Item where ItemTypeId in (1, 3)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ard5karthick SSCommitted Points: 1603 More actions · Answer 5

The Way SQL Server Works is like this...

It will First Fetch The Rows Having ItemID = 1;

On This Result It will search for Rows Having ItemID = 3

So The Query you Write Fails...

Instead of this You can Use IN Operator.

Where ItemID IN ('1','3')

Jared SSC Guru Points: 61793 More actions · Answer 6

ard5karthick (3/6/2012)
The Way SQL Server Works is like this...
It will First Fetch The Rows Having ItemID = 1;
On This Result It will search for Rows Having ItemID = 3
So The Query you Write Fails...
Instead of this You can Use IN Operator.
Where ItemID IN ('1','3')

Umm... No. It first fetches rows having ItemID = 1 AND ItemID = 3, which is impossible.

Jared
CE - Microsoft