MySQL: SELECT existing and non-existing IDs-CodePudding

The table may look like this:

id | label
----------
 1 | red
 3 | green
 5 | blue

Now, there’s a given set of IDs, which have to be compared against the DB. Let’s say (3, 4, 5).

I’m looking for a SELECT statement that returns the existing IDs including the label and the non-existing with label = null.

So given (3, 4, 5) I’m looking for this result

id | label
----------
 3 | green
 4 | NULL
 5 | blue

How can I do this in MySQL/MariaDB?

In PostgreSQL it would be SELECT FROM (VALUES (3), (4), (5)) t(id) combined with a left join with the table. However, I didn’t find an alternative for MySQL.

CodePudding user response：

Use a sub-query as an inline-view, using UNION ALL.

SELECT
  *
FROM
(
            SELECT 3 AS id
  UNION ALL SELECT 4
  UNION ALL SELECT 5
)
  AS params
LEFT JOIN
  some_table
    ON some_table.id = params.id

Or fall back on having robust dimension tables.

For example, a user may or may not have an entry in a favourite_fruit table, but the fruit table can be relied upon to be fully populated.

SELECT
  *
FROM
  user
CROSS JOIN
  fruit
LEFT JOIN
  favourite_fruit
    ON  favourite_fruit.user_id  = user.id
    AND favourite_fruit.fruit_id = fruit.id
WHERE
      user.id  IN (1,2,3)
  AND fruit.id IN (3,4,5)

CodePudding user response：

Given the question was tagged with mariadb, note that it does support VALUES. Checked on MariaDB 10.3:

WITH u(id) AS (VALUES (3), (4), (5))
  SELECT u.id, t.label
  FROM u LEFT JOIN t ON u.id = t.id;

id	label
3	green
4	null
5	blue

CodePudding user response：

In MySql 8.0 you can do the same with the ROW() constructor:

SELECT c.id, t.label
FROM ((VALUES ROW(3), ROW(4), ROW(5))) c(id) 
LEFT JOIN tablename t 
ON t.id = c.id;

Or with a CTE:

WITH cte(id) AS (VALUES ROW(3), ROW(4), ROW(5))
SELECT c.id, t.label
FROM cte c LEFT JOIN tablename t 
ON t.id = c.id;

See the demo.