Mysql Question posted in
The official MySQL documentation can be found here.

how to get the non existing record in a table using mysql

I have a table named ‘table1’ and it has some records like below.

enter image description here

Now i have written the query like below,

select value, name 
from table1 
where value in ('1a2b3c','8s9r3h','5h2j0m','4m5m6n','8i9o7l');

In the above query returns the records which are available in the table. But i have to return the data for the non existing records ('4m5m6n','8i9o7l') well.
The final output should look like below,

enter image description here

Kindly let me know is this possible?

Tags:

2

Answers


  1. 0 
    SELECT value, table1.name 
FROM (
    VALUES
    ROW('1a2b3c'), ROW('8s9r3h'), ROW('5h2j0m'), ROW('4m5m6n'), ROW('8i9o7l')
    ) all_values (value)
LEFT JOIN table1 USING (value);
    Login or Signup to reply.
  2. 0

    You can do it by creating a dataset with union all and then joining it using left join to your table:

    select s.value, t.name
from (
  select '1a2b3c' as value
  union all
  select '8s9r3h'
  union all
  select '5h2j0m'
  union all
  select '4m5m6n'
  union all
  select '8i9o7l'
) as s
left join mytable t on s.value = t.value

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search