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
The common table expression ( WITH clause) wraps the first query that we want to execute no matter what. We then select from the first query, and use UNION ALL to combine the result with the result of the second query, which we're executing only if the first query didn't yield any results (through NOT EXISTS ).
Select * from any table will fetch and display all the column in that table, while Select 1 from any table will display one row with 1 without any column name.
By default, SELECT returns rows in no particular order. The ORDER BY clause returns the rows in a given sort order. Rows can be returned in ascending or descending sort order.
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'
)
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'
.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With