EXPLAIN EXTENDED

How to create fast database queries

Empty row if condition does not match

with 5 comments

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 ON clause:

SELECT  m.*
FROM    values v
JOIN    mytable m
ON      m.id = v.value

, we could just rewrite an INNER JOIN to a LEFT JOIN.

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 values.

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

If 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.

Written by Quassnoi

January 3rd, 2013 at 11:00 pm

Posted in MySQL

5 Responses to 'Empty row if condition does not match'

Subscribe to comments with RSS

  1. Shouldn’the last join be a LEFT join?

    Massimo Morelli

    4 Jan 13 at 02:09

  2. @Massimo: *blushes* OK let’s pretend this never happened! :) Thanks!

    Quassnoi

    4 Jan 13 at 02:10

  3. Another option:

    (SELECT field FROM mytable WHERE id = 42)
    UNION
    (SELECT null)
    LIMIT 1;

    Scott

    5 Jan 13 at 20:31

  4. I’m glad you’re writing again. Thought you had abandoned the blog…

    Sérgio Saquetim

    7 Jan 13 at 04:52

  5. MySQL support full join or anyother way to get empty data?

    Bhumi

    4 Feb 15 at 15:10

Leave a Reply