Keep the row even when there is no value found SQL

Question

I'm trying to keep a row even when it is missing a value. But I just can't figure it out.

10.4.14-MariaDB

The example query:

SELECT
    data.name,
    am.value AS color
FROM
    color_data cd

    INNER JOIN color_data am
     ON cd.value = am.id

    INNER JOIN data
     ON cd.id = data.id

WHERE
    data.type = "vehicles"
    AND data.slug LIKE "%something%"
    AND cd.key = "color_id"
    AND am.key = "color"

What I get:

----------------------
   name   |  color   |
----------------------
 car      | red
 bicycle  | blue

What I want:

----------------------
   name   |  color   |
----------------------
 car      | red
 boat     |         <-- When this is empty keep the row.
 bicycle  | blue

SO when a color's ID is missing the row gets removed. I want to still keep that row. It's probably something fairly basic I'm missing.

And see https://stackoverflow.com/questions/47449631/return-default-result-for-in-value-regardless/47449788#47449788 for how to put conditions on the `color` table when joining. — Barmar, Mar 22 '21 at 22:50

score 0 · Answer 1 · answered Mar 22 '21 at 22:50

Use LEFT JOINs starting with the table where you want to keep all the rows:

SELECT data.name, am.value AS color
FROM data
     color_data cd LEFT JOIN
     ON cd.id = data.id AND
        cd.key = 'color_id' LEFT JOIN
     color_data am
     ON cd.value = am.id AND
        am.key = 'color'
WHERE data.type = 'vehicles' AND
      data.slug LIKE '%something%';

Notes:

data is now first in the FROM clause because you want to keep those rows.
Filtering conditions on data are in the WHERE clause.
Filtering conditions on the other tables are in the appropriate ON clause.
Single quotes are the SQL standard for strings, not double quotes.

Nick · Accepted Answer · 2021-03-22T23:23:21.893

You need to rewrite your query with the JOINs starting from data, use LEFT JOIN instead of INNER JOIN, and put the conditions on the color tables in the join conditions:

SELECT
    data.name,
    am.value AS color
FROM
    data
LEFT JOIN
    color_data cd ON cd.id = data.id AND cd.key = 'color_id'
LEFT JOIN
    color_data am ON am.id = cd.value AND am.key = 'color'
WHERE
    data.type = 'vehicles'
AND data.slug LIKE '%something%'

Keep the row even when there is no value found SQL

2 Answers2