Alternative to "IN" that works like "AND" instead of "OR"

Question

From my understanding, IN works like this:

$arrayName = array(1, 2, 3);

SELECT * 
FROM tableName 
WHERE productID IN ($arrayName)

is the equivalent of:

SELECT * 
FROM tableName 
WHERE productID = 1 OR productID = 2 OR productID = 3

I'm wondering if there's a SQL function that works like IN but uses AND in place of OR to compare to an array. Something that would expand to this:

SELECT * 
FROM tableName 
WHERE productID = 1 AND productID = 2 AND productID = 3

Not that it's necessary, but for context I'm simply creating a sort list for some search results that are being populated on a PHP page via jQuery. I can do what I need with PHP, I'll simply create the query dynamically depending on what options the user has selected, but I'd rather use an intelligent SQL function if possible.

***EDIT: Thanks everyone for the help. I explained my problem very poorly and you were still able to sort it out, which I appreciate. I found that someone else had asked this question more eloquently and received an answer that I can use:

Is there something in MySQL like IN but which uses AND instead of OR?

I'm trying to figure out how to accept an answer and close this but I'm having a bit of trouble...

`productID` can't be 1,2 and 3 in a single row. I understand what you want though. You need a [relational divison](http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/) query. — Martin Smith, Sep 21 '12 at 14:18
If you do `$arrayName = array(1, 2, 3);` and then `"SELECT * FROM tableName WHERE productID IN ($arrayName)";` in PHP, which seems the language used, you'd get the query `"SELECT * FROM tableName WHERE productID IN (Array)";` — Matteo Tassinari, Sep 21 '12 at 14:19
possible duplicate http://stackoverflow.com/questions/4751361/using-mysql-in-clause-as-all-inclusive-and-instead-of-or — Gonzalo.-, Sep 21 '12 at 14:22
possible duplicate of [Is there something in MySQL like IN but which uses AND instead of OR?](http://stackoverflow.com/questions/2860374/is-there-something-in-mysql-like-in-but-which-uses-and-instead-of-or) — 0b10011, Sep 21 '12 at 14:24
@Bidbits Click on the checkmark under the right answer (on the left where the big number is with two arrows) — David Bélanger, Sep 21 '12 at 14:26
Thanks, got it, limits me from picking one for a certain time period. I'm amazed at how quickly everyone responded, very impressed. — Bidbits, Sep 21 '12 at 14:31

John Woo · Accepted Answer · 2012-09-21T14:29:52.157

3

You cannot possibly do this,

SELECT * 
FROM tableName 
WHERE productID = 1 AND productID = 2 AND productID = 3

the condition will always returns false because a row can have only one value on its column, the alternative way to do this is by grouping the result, ex.

SELECT colName
FROM tableName
WHERE productID IN (1,2,3)
GROUP BY colName
HAVING COUNT(DISTINCT colName) = 3

by having a condition HAVING COUNT(DISTINCT colName) = 3, this means that the instance of a record must be equal to the total count of parameters supplied on IN clause.

edited Sep 21 '12 at 14:29

answered Sep 21 '12 at 14:19

John Woo

258,903
69
498
492

Hi everyone, thanks a lot for the quick response. There isn't a problem with the code I'm using, I'm just an idiot at explaining things. I found another question were someone asked/answered better than I could: http://stackoverflow.com/questions/2860374/is-there-something-in-mysql-like-in-but-which-uses-and-instead-of-or?rq=1 – Bidbits Sep 21 '12 at 14:20
You would need `COUNT(DISTINCT productID)` – Martin Smith Sep 21 '12 at 14:22
@MartinSmith that's also correct if tableName allows duplicate productName which primary key is auto incremented. Actually it always depends on every situation. Anyway, thanks for the comment.Much appreciated :) – John Woo Sep 21 '12 at 14:24
There are two ways to implement relational division, one is the above answer and the other is via joins, [which is explained here](http://stackoverflow.com/a/2860425/4248167). According to [this test shown here](http://stackoverflow.com/a/31102803/4248167), the join implementation has better performance and is the faster of the two implementations. – true Jun 28 '15 at 20:22

score 3 · Answer 2 · answered Sep 21 '12 at 14:21

As written, your query will produce no rows. It is not possible for productID in a row to be equal to both 1 and 2 at the same time.

You are probably looking for a group of rows that contain these three products. Say you want to find orders that have all three products. You can use something like:

select orderid
from orderlines ol
group by orderid
havnig max(case when ol.productid = 1 then 1 else 0 end) > 0 and
       max(case when ol.productid = 2 then 1 else 0 end) > 0 and
       max(case when ol.productid = 3 then 1 else 0 end) > 0

The GROUP BY with the HAVING clause will find orders where all three products are present.

score 2 · Answer 3 · answered Sep 21 '12 at 14:23

2

SELECT orderid 
FROM tableName  
WHERE productID IN (1, 2, 3)
GROUP BY orderid
HAVING COUNT(DISTINCT productID) = 3 --this number must match the number of unique IDs in the IN clause

answered Sep 21 '12 at 14:23

D'Arcy Rittich

167,292
40
290
283

Alternative to "IN" that works like "AND" instead of "OR"

3 Answers3