Using SQL IN AND together

Question

Is there a way to use IN and AND together? The problem is that I want to match my query to multiple values using one column.

SELECT * FROM product INNER JOIN values USING(product_id) WHERE value = "large" AND value = "short"

The problem is I can't use the IN clause, because it defines as OR between the values.

Value can never be two different values as you have shown here. You would be trying to return when a single column is two different values, which cannot happen — jzworkman, Mar 02 '12 at 16:21
I don't think you understand how the `WHERE` clause works. Which record has a `value` of both `large` and `short`? — JNK, Mar 02 '12 at 16:22
Not sure what you mean, `value` can't be both `large` _and_ `short` at the same time. Please try to give another example. — Joachim Isaksson, Mar 02 '12 at 16:23
Your query in its current form will always return an empty set: no value can be equal to `large` and `short` at the same time. You need `OR` or `IN ('short', 'large')`. — Igor Korkhov, Mar 02 '12 at 16:24
I think the question would be clear if you posted the table structures. I guess you have a 1:M relationship between `product` and `values` and you want the product that have **both** `large` and `short` in the `values` table for a given product. Is it so? — Mikael Eriksson, Mar 02 '12 at 16:26
Yes Mikael Eriksson you're right, thats exactly how I have it now — stixx, Mar 02 '12 at 16:28

score 7 · Accepted Answer · answered Mar 02 '12 at 16:33

7

Try:

select product_id
from values
where value in ('large','short')
group by product_id
having count(distinct value) = 2

answered Mar 02 '12 at 16:33

Thank you, yours did the job! – stixx Mar 03 '12 at 10:09

score 2 · Answer 2 · answered Mar 02 '12 at 16:26

2

No, that's not possible, because it doesn't make any sense at all.

A field can not be equal to two different values at the same time. The expression x = y and x = z will always be false when y and z have different values.

(The exception would be some text values that could be considered equal in some cultures, like the and thé, but the comparison still doesn't make any sense in that case.)

answered Mar 02 '12 at 16:26

Guffa

687,336
108
737
1,005

1

"The expression x = y and x = z will always be false when y and z have different values" -- not the case in SQL (hint: when x is null). General hint: relational division: not widely name-checked therefore we necessarily get woolly descriptions about things being equal to more than one thing. – onedaywhen Mar 02 '12 at 17:03
@onedaywhen: Yes, null values is another expection, but the comparison still makes no sense. Just because you can use an expression doesn't mean that it's useful for anything. – Guffa Mar 02 '12 at 18:11
"the comparison still makes no sense" -- To aid your comprehension, I suggest you click on the `relational-division` tag and take a look of some of the titles/descriptions/answers. The fact is, it is not often given a name and [even when you are in the know it is a difficult one to explain](http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/) let alone formulate the SQL for. You just have to learn to spot it, ditto semi join and semi difference. – onedaywhen Mar 03 '12 at 07:57
@onedaywhen: That's something comepletely different, of course a set can contain more than one value. A single value though can not be two different values at once. Not even in in relational division is such a comparison used, take a look yourself at the questions tagged with it. – Guffa Mar 04 '12 at 19:37
You provided the answer to the literal question i.e. question is nonsense. However, Mikael Eriksson (in comments to question) correctly guessed what the OP was really asking and the accepted answer is a classic relational division 'pattern', albeit with a hard-coded 'set' and its cardinality. My question to you: why haven't you deleted this answer (which really should have been a comment in the first place)? – onedaywhen Mar 05 '12 at 08:47
@onedaywhen: My anwser to you is that it is a valid answer to the question that the OP did ask, eventhough the question might not be what the OP indented to ask. As it's an answer it should not be a comment. – Guffa Mar 05 '12 at 12:27

score -2 · Answer 3 · answered Mar 02 '12 at 16:24

-2

No. both IN and AND operators are different if you are using your queries in the below manner.

This SQL doesnt make any sense.I am just trying to compare the wrong one with the right one. SELECT * FROM product INNER JOIN values USING(product_id) WHERE value = "large" AND value = "short"

SELECT * FROM product INNER JOIN values USING(product_id) WHERE value IN ("large","short");

answered Mar 02 '12 at 16:24

Teja

13,214
36
93
155

This is wrong, the second statement checks for a value of either "large" or "small", that is how the `IN` operator works. – jzworkman Mar 02 '12 at 16:26

Using SQL IN AND together

3 Answers3