CODEWITHSUNDEEP
mysqlsql
Fragile
Fragile

Reputation: 71

How to merge these 2 MySQL Select queries?

I would like to merge 2 MySQL queries into 2 because of some performance increasement. Basically I'd like the query to search for an element in 2 tables. If it doesn't find in one, it should search in the other one. This is the reason I have created 2 queries, but I'm sure it will be slower in the log run than 1 query.

I have the following 2 queries:

SELECT a_id FROM table1 WHERE name = 'George'
SELECT b_id FROM table2 WHERE name = 'George'

How is it possible to create one query of these? It should only return 1 id where it found the match.

Upvotes: 1

Views: 53

Answers (2)

valex
valex

Reputation: 24144

Try to use COALESCE

SELECT COALESCE(a_id,
       (SELECT b_id FROM table2 WHERE name = 'George'))
FROM table1 WHERE name = 'George'

Upvotes: 2

fthiella
fthiella

Reputation: 49089

You could use a UNION ALL query:

SELECT a_id FROM table1 WHERE name = 'George'
UNION ALL
SELECT b_id FROM table2
WHERE
  name = 'George'
  AND NOT EXISTS (SELECT a_id FROM table1 WHERE name = 'George')

or this:

SELECT a_id, 1 as t FROM table1 WHERE name = 'George'
UNION ALL
SELECT b_id, 2 as t FROM table2 WHERE name = 'George'
ORDER BY t
LIMIT 1

but I don't think it will be faster than running two separated queries.

Upvotes: 2

Related Questions