MYSQL, Include Null Results

Question

Want a null row where no data exist

Hello. This is in regards to MySQL Workbench 6.3.

I'm trying to return a list of results for every item listed in my select statement, which would include those items that don't actually exist. So if i list 5 items in my select statement and only 3 exist, i'd like to get 5 rows returned, 3 actual rows with data and 2 more rows that only show null. Can someone please show me how to edit my query below to show this ? Thank you !

select emails from table where email in (dog, frog, cat, tiger, lizard);

Actual Result (only the 3 emails that actual exist show)

dog
cat
tiger

Desired Result

dog
null
cat
tiger
null

The desired results are not possible.. You can't expect MySQL to return the selected records in the order they went in the IN() operator. — Raymond Nijland
– Raymond Nijland, Commented Feb 8, 2019 at 16:14

Raymond Nijland · Accepted Answer · 2019-02-08 16:17:52Z

1

The desired results are not possible.. You can't expect MySQL to return the selected records in the order they went in the IN() operator.

So i think you better off when you change the desired result to something you know sometiming was not found in the table, i think you are looking for.

Query

 SELECT 
    search_emails.email 
  , (
      CASE
         WHEN t.email IS NOT NULL
         THEN 'true' ELSE 'false' 
      END
    ) AS found
FROM (
  SELECT 'dog' AS email
  UNION
  SELECT 'frog' AS email
  UNION 
  SELECT 'cat' AS email
  UNION 
  SELECT 'tiger' AS email
  UNION 
  SELECT 'lizard' AS email
) AS search_emails 
LEFT JOIN 
 t
ON
 t.email = search_emails.email

Result

| email  | found |
| ------ | ----- |
| dog    | true  |
| cat    | true  |
| tiger  | true  |
| frog   | false |
| lizard | false |

see demo

edited Feb 8, 2019 at 16:17

answered Feb 8, 2019 at 16:03

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Philipp Sander · Accepted Answer · 2019-02-08 16:08:34Z

0

select emails from table where email in (dog, frog, cat, tiger, lizard) OR email IS NULL

be sure that the values provided for IN (...) are provided as strings.

edited Feb 8, 2019 at 16:08

answered Feb 8, 2019 at 16:02

Philipp Sander

10.3k7 gold badges49 silver badges80 bronze badges

4 Comments

Philipp Sander Over a year ago

why? it's valid SQL. yes "table" is a keyword. but I copied that from the question assuming it is a placeholder

Raymond Nijland Over a year ago

in (dog, frog, cat, tiger, lizard) is not valid SQL.. will give unknown column errors ..

Philipp Sander Over a year ago

also copied from the question... assuiming they are placeholders

Raymond Nijland Over a year ago

"be sure that the values provided for IN (...) are provided as strings" Besides the query won't generate the non matching results for 'frog' and 'lizard' the added OR will not change that.

Collectives™ on Stack Overflow

MYSQL, Include Null Results

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related