CODEWITHSUNDEEP
sqlmybatisibatis
Nicolas De Nayer
Nicolas De Nayer

Reputation: 269

SQL: I want a row to be return with NULL even if there is no match to my IN clause

I would like my SQL query to return a row even if there is no row matching in my IN clause.

For exemple this query:

SELECT id, foo 
FROM table 
WHERE id IN (0, 1, 2, 3)

would return:

id|foo
 0|bar
 1|bar
 2|bar
 3|null

But instead I have (because no row with id 3):

id|foo
 0|bar
 1|bar
 2|bar

I have been able to find this trick:

SELECT tmpTable.id, table.bar
FROM (
    SELECT 0 as id
    UNION SELECT 1
    UNION SELECT 2
    UNION SELECT 3
) tmpTable
LEFT JOIN
( 
SELECT table.foo, table.id 
    FROM table 
    WHERE table.id IN (0, 1, 2, 3)
) table
on table.id = tmpTable.id

Is there a better way?

Bonus: How to make it work with myBatis's list variable?

Upvotes: 1

Views: 324

Answers (3)

Paurian
Paurian

Reputation: 1402

overslacked is right. Most SQL developers use an auxiliary table that stores integers (and one that stores dates). This is outlined in an entire chapter of Joe Celko's "SQL for Smarties".

Example:

CREATE TABLE numeri ( numero INTEGER PRIMARY KEY )

DECLARE @x INTEGER
SET @x = 0
WHILE @x < 1000
BEGIN
  INSERT INTO numeri ( numero ) VALUES ( @x )
  SET @x = @x + 1
END

SELECT
  numero AS id,
  foo
FROM
  numeri
  LEFT OUTER JOIN my_table
    ON my_table.id = numero
WHERE
  numero BETWEEN 0 AND 3

Upvotes: 3

Damian Anthony
Damian Anthony

Reputation: 25

What about just saying:

SELECT id, foo FROM table WHERE id >= 0 AND <= 3

Upvotes: -1

user4509170
user4509170

Reputation:

Main Goal of Programming minimal code high performance no need this things just remove id 3 from in clause

Upvotes: -1

Related Questions