MySQL: SELECT inside SELECT IF() statement

Question

Can I have something like this?

SELECT IF((SELECT COUNT(id) FROM table WHERE id = 1) = 0, 1, <nothing_to_happen>) AS Available

My goal is select this:

+---------+
|Available|
+---------+
|1        |
+---------+

Only if there is no row selected from this query:

SELECT id FROM table WHERE id = 1

If the row with id = 1 exists in table, I want my query to return zero rows! Is that possible?

Quassnoi · Accepted Answer · 2013-01-13 01:16:19Z

5

SELECT  1
FROM    dual
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable
        WHERE   id = 1
        )

answered Jan 13, 2013 at 1:16

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

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

3 Comments

Using LEFT JOIN and filtering for NULL-s is another, probably faster solution.

It was marginally faster in 5.1, and become the same in 5.5.

Since when does MySQL have the dual table?

Justin · Accepted Answer · 2013-01-13 09:05:42Z

0

Query:

SELECT CASE
           WHEN COUNT(id) = 0 THEN 1
           ELSE 0
       END AS Available
FROM `TABLE`
WHERE id = 1

answered Jan 13, 2013 at 9:05

Justin

9,7336 gold badges39 silver badges49 bronze badges