How get a Value even if result is not found in the DB mysql

user_13110730 0
Viewed 64

I need to find the right query to get a value even if the data not exist in the db.

I have my table named "prova":

it en de
data data
riga row linie
parola

If I query:

SELECT en,de FROM `prova` WHERE `it` IN ("data","riga");

The resoult is:

en de
data
row linie

And it's ok!

BUT

SELECT en,de FROM `prova` WHERE `it` IN ("data","riga","ciao","parola");

The resoult is:

en de
data
row linie

Is as expected in the standard mysql but for me is a problem.

The the desired result is:

en de
data not found
row linie
not found not found
not found not found

Why I want this?

I need to perform a query with an array of string, actually I need to query one by one string, and check if the query is empty. The size and the order of the resulted array should be equal to the given array.

Actually I need to perform 8700 queries, this will help me to decrease the queries number to 8.

Thanks

union-all left-join common-table-expression mysql
2 Answers

You can use a cte that returns the array of strings with a number that corresponds to the order of the string in the results and a LEFT join of the table:

WITH cte(id, word) AS (VALUES
  ROW(1, 'data'), ROW(2, 'riga'), ROW(3, 'ciao'), ROW(4, 'parola')
)
SELECT COALESCE(p.en, 'not found') en,
       COALESCE(p.de, 'not found') de
FROM cte c LEFT JOIN prova p
ON p.it = c.word
ORDER BY c.id;

Or, with UNION ALL for versions of MySql prior to 8.0 without the cte support:

SELECT COALESCE(p.en, 'not found') en,
       COALESCE(p.de, 'not found') de
FROM (
  SELECT 1 id, 'data' word UNION ALL
  SELECT 2, 'riga' UNION ALL 
  SELECT 3, 'ciao' UNION ALL 
  SELECT 4, 'parola'
) t
LEFT JOIN prova p ON p.it = t.word
ORDER BY t.id;

See the demo.

user_104988280

Use coalesce to fill in a null value with a default.

select
  coalesce(en, 'not found'),
  coalesce(de, 'not found')
...

For the second part, how to make all the in values show up as rows, see this answer.

user_146600
Related