Just found that in a Google referer to the blog:
I want SQL to return blank row even if the condition does not match
This may be useful for certain ORMs which always expect a single row as a result of a query.
Say, we have a query like that:
SELECT * FROM mytable WHERE id = 42
and want it to return a single row (possibly consisting of NULL values) no matter what.
If we had a join and the condition in the
SELECT m.* FROM values v JOIN mytable m ON m.id = v.value
, we could just rewrite an
INNER JOIN to a
SELECT m.* FROM values v LEFT JOIN mytable m ON m.id = v.value
This way, we would have at least one record returned for each entry in
In our original query we don't have a table to join with. But we can easily generate it:
SELECT m.* FROM ( SELECT 42 AS value ) v LEFT JOIN mytable m ON m.id = v.value
id is a
PRIMARY KEY on
mytable, this query would return exactly one record, regardless of whether it such an id exists in
mytable or not.