MySQL check if multiple rows exist

Question

Here you can find how to check row existance:
SELECT EXISTS(SELECT 1 FROM table1 WHERE some_condition);

How to efficiently existance of multiple rows from table like:

SELECT EXISTS(SELECT 1 FROM table1 WHERE key = 0);
SELECT EXISTS(SELECT 1 FROM table1 WHERE key = 2);

from table:

key,username
0,foo
1,bar
2,boo

to return positive only if both rows (with key 0 and 2) was found?

Akhil · Accepted Answer · 2013-12-02 18:36:24Z

4

SELECT count(distinct key) = 2  FROM table1 WHERE key in (0, 2)

answered Dec 2, 2013 at 18:36

Akhil

2,60225 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2013-12-02 18:25:02Z

1

SELECT sum(`key` = 0) as key0_count,
       sum(`key` = 2) as key2_count 
FROM your_table

edited Dec 2, 2013 at 18:25

answered Dec 2, 2013 at 18:04

juergen d

205k40 gold badges304 silver badges377 bronze badges

5 Comments

Szymon Toda Over a year ago

And how is that better from SELECT EXISTS and why key0_count?

juergen d Over a year ago

It is one single query without any subqueries.

Szymon Toda Over a year ago

What is key0_count ?

juergen d Over a year ago

key0_count is the number of times key = 0 in your table. If it is 0 then it is not in your table.

Gordon Linoff Over a year ago

@Ulta . . . If you have an index on key, then multiple exists statements are probably faster. If you do not have an index, then this may be faster, because it only requires one full scan of the table.

Collectives™ on Stack Overflow

MySQL check if multiple rows exist

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related