SQL in clause with dummy rows

Question

Hi,

I'd really like know if it's possible to do a select statement, which returns exactly same records, that we put into in clause?

Sample:

select * from table
where table_id in (1, 2, 3, 666);

This table for an example has only id-s from 1 to 100, so this select will return only three rows. What I need to do, to get also one (probably null or dummy) row for 666?

Thanks!

Answer 1

Assuming a table numbers containing all numbers from, say, 1 to 1000000 (in fact enough that your range of input values is covered), you could run the following SQL:

SELECT *
  FROM numbers left outer join table on table.table_id = numbers.number
 WHERE numbers.number in (1, 2, 3, 666)

If you use a DBMS that offers a better solution, like e. g. SQL Anywhere with it's sa_rowgenerator procedure, you can replace the table numbers with a procedure call, and do not have a limit of a maximum number.

Answer 2

You can select without a table

Just do a UNION with your query

select table_id, some_column from table
where table_id in (1, 2, 3, 666);
union
select 666, 'dummy_data'

Answer 3

One way to think of it is: you'd need to have that data "enter" the query as a data set. Data found in where clauses is never "added" to the query, they are only used to filter existing data out.

A quick example:

DECLARE @MustInclude (Value  int  not null)

INSERT @MustInclude (Value) values (1)
INSERT @MustInclude (Value) values (2)
INSERT @MustInclude (Value) values (3)
INSERT @MustInclude (Value) values (666)

SELECT *
 from @MustInclude mi
  left outer join MyTable mt
   on mt.Value = mi.Value

Answer 4

You could use union:

select * from table
where table_id in (1, 2, 3);
union
select 666 as table_id, other_fields_with_dummy_values_in_table from dual;

is how you could do it in Oracle. The from dual might vary depending on what database system you're using.

Just be aware that if you use union, your dummy query MUST select the same records as the real query.

Answer 5

IN clause is a boolean predicate, so you'll need to replace it with a dummy recordset:

SELECT  m.*
FROM    (
        SELECT  1 AS id
        UNION ALL
        SELECT  2 AS id
        UNION ALL
        SELECT  3 AS id
        UNION ALL
        SELECT  666 AS id
        ) q
LEFT JOIN
        mytable m
ON      m.id = q.id

In SQL Server 2008, you can run this query:

SELECT  *
FROM    @mydata d
LEFT JOIN
        mytable t
ON      t.id = d.id

with @mydate is a table variable, passed as a parameter from the client.

In PostgreSQL, you can run this query:

SELECT  *
FROM    (
        SELECT  :arr[s] AS id
        FROM    generate_series(1, array_upper(:arr, 1)) s
        ) q
LEFT JOIN
        mytable t
ON      t.id = q.id

where :arr is an array [1, 2, 3, 666], also passed from the client as the parameter.

In Oracle, you can do:

SELECT  *
FROM    TABLE(:mycol) q
LEFT JOIN
        mytable t
ON      t.id = q.id

, where :mycol is a variable of collection type, passed from the client.