Second SELECT query if first SELECT returns 0 rows

Question

I am trying to speed up a PHP script and I am currently pushing some PHP logic in the Mysql domain of the thing. Is there a way to make a different select query if the first Select returns no rows, or a count of zero ?

Keeping in mind that the first query needs to run first, and the second should only be activated if the first one returns an empty set.

SELECT * FROM proxies WHERE (A='B') || SELECT * FROM proxies WHERE (A='C')

For the above 2 queries I have this code, but it seems to run each query twice (once to count, and once to return). Is there a better way to do this?

IF (SELECT count(*) FROM proxies WHERE A='B')>0
    THEN SELECT * FROM proxies WHERE A='B'
ELSEIF (SELECT count(*) FROM proxies WHERE A='C')>0
    THEN SELECT * FROM proxies WHERE A='C'
END IF

You're overcomplicating this. The `count(*)` are a waste. Simply `SELECT * FROM proxies WHERE A='B'` first. If you get zero rows back, then issue `SELECT * FROM proxies WHERE (A='C')`. — Asaph, Dec 08 '14 at 20:19
BTW: I believe you have a typo in the question. The last query should end in `WHERE A='C'`, instead of `WHERE A='B'`. — Asaph, Dec 08 '14 at 20:21
How many rows are possible for the first SELECT - 1 or more? — i486, Dec 08 '14 at 20:26

score 28 · Accepted Answer · answered Dec 08 '14 at 20:27

28

One option would be to use UNION ALL with EXISTS:

SELECT * 
FROM proxies 
WHERE A='B'
UNION ALL
SELECT * 
FROM proxies 
WHERE A='C' AND NOT EXISTS (
    SELECT 1
    FROM proxies 
    WHERE A='B'
)

SQL Fiddle Demo

This will return rows from the proxies table where A='B' if they exist. However, if they don't exist, it will look for those rows with A='C'.

answered Dec 08 '14 at 20:27

sgeddes

62,311
6
61
83

I will try this out and get back to you, but it looks great thnx. – Evan Dec 08 '14 at 20:30
I'd be curious to know if this is actually faster than letting php handle the logic. I guess that would partially depend on how frequently A='B' returns nothing. – quid Dec 08 '14 at 20:32

Fabien TheSolution · Answer 2 · 2014-12-08T20:54:27.547

8

SELECT * 
FROM proxies 
WHERE A=(CASE WHEN
             (SELECT COUNT(*) FROM proxies WHERE A='B') > 0 THEN'B' 
              ELSE 'C' END)

UPDATE

SELECT * 
FROM proxies 
WHERE (
    CASE WHEN (SELECT COUNT(*) FROM proxies WHERE A='B' LIMIT 1) > 0 THEN
            (A='B')
         WHEN (SELECT COUNT(*) FROM proxies WHERE A='C' LIMIT 1) > 0 THEN
            (A='C')
         WHEN (SELECT COUNT(*) FROM proxies WHERE A='D' LIMIT 1) > 0 THEN
            (A='D')
         ELSE 1=2 END)

edited Dec 08 '14 at 20:54

answered Dec 08 '14 at 20:28

Fabien TheSolution

5,055
1
18
30

1

Thanks Fabien thats a great solution for what i asked, but i was misleadingly simplifying it i guess. The WHERE conditions are more numerous and complicated. – Evan Dec 08 '14 at 20:33
1

Thanks!Great Solution! – Teknoville Apr 07 '20 at 10:34

score 1 · Answer 3 · answered Dec 08 '14 at 20:39

1

Another way:

SELECT A INTO @v FROM proxies ORDER BY A LIMIT 1;
SELECT * FROM proxies WHERE A=@v

answered Dec 08 '14 at 20:39

i486

6,491
4
24
41

abl · Answer 4 · 2014-12-08T20:55:43.513

1

In a general case where you have multiple values for A (lets say, 'B', 'C', 'D', 'E', etc.) and you want to retrieve only the rows that belong to the lowest value that exists, then you would use the following query. This will work also for the particular case you exposed.

SELECT p1.*
FROM proxies p1
LEFT JOIN proxies p2
    ON p1.A > p2.A
WHERE p2.A IS NULL

Fiddle

edited Dec 08 '14 at 20:55

answered Dec 08 '14 at 20:49

abl

5,970
4
25
44

score 0 · Answer 5 · answered Dec 08 '14 at 20:24

I think this more or less covers what you're trying to do. If you need the count of rows returned just get the value from mysqli->num_rows

$db = new mySQLi(etc);

//run your first query

$query = "SELECT * FROM proxies WHERE A='B'";
$result = $db->query($query);

//check for rows returned

if($result->num_rows > 0){

    //if there are rows get them

    while($row = $result->fetch_assoc()){

        //deal with the results

    }

} else {

    //otherwise run your other query and get those results

    $query = "SELECT * FROM proxies WHERE A='C'";
    $result = $db->query($query);
    while($row = $result->fetch_assoc()){

        //deal with the results

    }
}

@Evan Don't bother. Just do it in php. It's more straight forward and it won't perform any worse. — Asaph, Dec 08 '14 at 20:27

Second SELECT query if first SELECT returns 0 rows

5 Answers5

Linked

Related