CODEWITHSUNDEEP
mysqlsql
user3368724
user3368724

Reputation: 61

Return default value for non-existing rows

To the very basic query

SELECT id, column1, column2
FROM table1
WHERE id IN ("id1", "id2", "id3")

in which the the arguments in the where statement are passed as a variable, I need to return values also for rows where the id doesn't exist. In general, this is a very similar problem as outlined here: SQL: How to return an non-existing row? However, multiple parameters are in the WHERE statement

The result right now when id2 doesn't exist:

-------------------------------
| id  | column1   | column2   |
-------------------------------
| id1 | some text | some text |
| id3 | some text | some text |
-------------------------------

Desired outcome when id2 doesn't exist

-----------------------------------
| id  | column1     | column2     |
-----------------------------------
| id1 | some text   | some text   |
| id2 | placeholder | placeholder |
| id3 | some text   | some text   |
-----------------------------------

My first thought was to create a temporary table and join it against the query. Unfortunately, I don't have the rights to create any kind of temporary table so that I am limited to a SELECT statement.

Is there way to do that in with a SQL SELECT query?

Edit: Indeed, the above mentioned is a hypothetical situation. In the WHERE clause can be hundreds of ids where the amount of missing in unknown.

Upvotes: 6

Views: 1864

Answers (3)

StuartLC
StuartLC

Reputation: 107347

Project a derived table containing all the candidate ids you want, then left join to it:

select ids.id, coalesce(table1.column1,'placeholder') 
From
(Select 'id1' as id
Union
Select 'id2'
Union
Select 'id3') ids
left join table1
on ids.id1 = table1.id1
and table1.id in (...);

If you are producing the list of candidate ids from an external source (e.g. an application), you could insert the ids into a temporary table and then join to it (MySql doesn't support table variables yet).

Upvotes: 0

Nishant Shrivastava
Nishant Shrivastava

Reputation: 523

Create a stored procedure that would take as input id1, id2 and so on...

DELIMITER //
CREATE PROCEDURE P1(IN p_in varchar(5))
BEGIN
DECLARE count integer;
SELECT count(id) INTO count FROM TABLE1 
WHERE id = p_in;

IF count = 1 THEN
    SELECT * from table1 where id = p_in;
ELSE
    select p_id, 'some text', 'some text';
END IF;
END//

DELIMITER ;

The call the procedure to get desired output..

CALL P1('id1'); 
CALL P2('id2');

.. and so on from your program..

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562731

You can do a derived table to create something like a temp table, but it can only be used for this one query:

SELECT t.id, COALESCE(t.column1, _dflt.column1) AS column1
FROM (
  SELECT 'id1' AS id, 'placeholder text 1' as column1
  UNION ALL
  SELECT 'id2', 'placeholder text 3'
  UNION ALL
  SELECT 'id3', 'placeholder text 3'
) AS _dflt
LEFT OUTER JOIN table1 t USING (id);

Re comments:

I just tested the method above on MySQL 5.6.15 to see how many distinct SELECTs I can get with a series of UNION ALLs, one row per SELECT.

I got the derived table to return 5332 rows, but I think I could go higher if I had more RAM.

If I try one more UNION ALL, I get: ERROR 1064 (42000): memory exhausted near '' at line 10665. I only have 2.0GB of RAM configured on this VM.

It doesn't matter how many ids are unknown for this solution to work. Just put them all in the derived table. By using LEFT OUTER JOIN, it automatically finds those that exist in your table1, and for the ones that are missing, the entry from the derived table will be matched up with NULLs.

The COALESCE() function returns its first non-null argument, so it'll use columns from the matched rows if those are present. Where none is found, it'll default to the columns in the derived table.

Upvotes: 4

Related Questions