Return nonexistent right table values as NULL

Question

I have two MySQL tables:

CREATE TABLE things (
  id INT
);

CREATE TABLE properties (
  thing_id INT,
  name VARCHAR(100),
  value VARCHAR(100)
);

INSERT INTO things (id) VALUES (1);

INSERT INTO properties (thing_id, name, value) VALUES
  (1, 'name', 'John'),
  (1, 'age', '123');

I want to be able to select properties for a thing based on their names, and return NULL if such a property doesn't exist. Here is what I have attempted:

SELECT
  p1.value AS name,
  p2.value AS age,
  p3.value AS foo
FROM
  things AS t
  LEFT JOIN properties AS p1 ON t.id = p1.thing_id
  LEFT JOIN properties AS p2 ON t.id = p2.thing_id
  LEFT JOIN properties AS p3 ON t.id = p3.thing_id
WHERE
  t.id = 1
  AND p1.name = 'name'
  AND p2.name = 'age'
  AND p3.name = 'foo';

I wanted the result to be

  name |   age | foo
---------------------
'John' | '123' | NULL

But unfortunately this query returns an empty result set, because I think such a p3 doesn't exist.

How can I write a query to do what I want? (And preferably without using explicit LEFT JOINs but commas and WHERE conditions instead, because this is a programmatically generated SQL query.) Thanks for any suggestions.

You can get idea from this question https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — Biki mallik, Feb 15 '20 at 08:20

score 3 · Accepted Answer · answered Feb 15 '20 at 08:19

You need to move the WHERE conditions on p1, p2 and p3 into their respective JOIN conditions, otherwise it turns those LEFT JOINs into INNER JOINs (see the manual).

SELECT
  p1.value AS name,
  p2.value AS age,
  p3.value AS foo
FROM
  things AS t
  LEFT JOIN properties AS p1 ON t.id = p1.thing_id AND p1.name = 'name'
  LEFT JOIN properties AS p2 ON t.id = p2.thing_id AND p2.name = 'age'
  LEFT JOIN properties AS p3 ON t.id = p3.thing_id AND p3.name = 'foo'
WHERE
  t.id = 1

Accepting this as the answer as it's the most direct answer to my question. The other answers are helpful as well! — Zizheng Tai, Feb 20 '20 at 01:05

score 3 · Answer 2 · edited Feb 15 '20 at 09:23

And preferably without using explicit LEFT JOINs but commas and WHERE conditions instead

It is considered bad style to use old comma-separated joins. Comma-separated joins were used before explicit joins (INNER JOIN, LEFT OUTER JOIN, etc.) were invented in 1992. One reason for inventing these was that there was no standard syntax for outer joins before. Another was that comma-separated joins are prone to errors and very often less readable. They only exist for compatibility reasons now.

My opinion: Don't even consider using them.

select properties for a thing

You are dealing with a key/value table here. They are a nuisance to work with. Is there a reason for your data model not just to have tables with explicit columns? A data model containing a table things and another properties is about the worst I can think of.

The typical approach to get values from a key/value table is conditional aggregagtion:

select
  any_value(case when p.name = 'name' then p.value end) as name,
  any_value(case when p.name = 'age' then p.value end) as age,
  any_value(case when p.name = 'foo' then p.value end) as foo
from things as t
join properties p on p.thing_id = t.id
where t.id = 1;

If this query is too slow because of too many properties in the table, add a WHERE clause:

where p.name in ('name', 'age', 'foo')

and provide an appropriate index:

create unique index idx on properties (thing_id, name);

in case you don't already have it.

If your MySQL version is old and doesn't support ANY_VALUE then you should consider upgrading. If you don't want to do this or cannot do this, then simply replace ANY_VALUE by MIN or MAX.)

I couldn't agree more with your comments in this; unfortunately EAV - while it has its place - has become very prevalent as an easy way out of proper database design. — Nick, Feb 15 '20 at 09:27
Thank you for your suggestions. Unfortunately, this schema is a pre-existing design decision that I have to base my solutions on. How does `ANY_VALUE`/`MIN`/`MAX` in this case compare with a left join, in terms of performance? — Zizheng Tai, Feb 15 '20 at 10:05
I think it shouldn't make much off a difference. Choose whichever approach you like better. In both cases you should have the mentioned index. The two columns probably form the table's primary key anyway, so it's rather likely this index already exists. — Thorsten Kettner, Feb 15 '20 at 10:16

score 1 · Answer 3 · answered Feb 15 '20 at 13:03

You can use aggregation:

SELECT MAX(CASE WHEN p.name = 'name' THEN p.value END) AS name,
       MAX(CASE WHEN p.name = 'age' THEN p.value END) AS age,
       MAX(CASE WHEN p.name = 'foo' THEN p.value END) AS foo
FROM things t LEFT JOIN
     properties p
     ON t.id = p1.thing_id
WHERE t.id = 1
GROUP BY t.id;

Note that this returns one row per t.id -- even if there are multiple values matching. If you can have multiple values, then use GROUP_CONCAT().

For one id the performance should be fine -- particularly if you have an index on properties(thing_id).

One nice thing about performance is that if you add additional columns, then the performance does not change very much -- the effort is more in the aggregation than in counting columns.

With the LEFT JOIN approach, the each new column is a new JOIN, which can affect performance. There is often a threshold where one approach is better than the other. By the way, Nick has the right answer for that approach, which is the more direct answer to your question.

Return nonexistent right table values as NULL

3 Answers3