52

Let's assume the following:

Table A

id | value
----------
1   | red
2   | orange
5   | yellow
10  | green
11  | blue
12  | indigo
20  | violet

I have a list of id's (10, 11, 12, 13, 14) that can be used to look up id's in this table. This list of id's is generated in my frontend.

Using purely SQL, I need to select the id's from this list (10, 11, 12, 13, 14) that do not have entries in Table A (joining on the 'id' column). The result should be the resultset of id's 13 and 14.

How can I accomplish this using only SQL? (Also, I'd like to avoid using a stored procedure if possible)

The only approach I can think of is something that would create an inline SQL table on the fly to temporarily hold my list of id's. However, I have no idea how to do this. Is this possible? Is there a better way?

Thanks! :)

rinogo
  • 8,491
  • 12
  • 61
  • 102

5 Answers5

108

You can do this from SQL Server 2008 onwards using a table value constructor.

SELECT * FROM (
   VALUES(1, 'red'),
         (2, 'orange'),
         (5, 'yellow'),
         (10, 'green'),
         (11, 'blue'),
         (12, 'indigo'),
         (20, 'violet'))
   AS Colors(Id, Value)

More information here: Table Value Constructor

phillip voyle
  • 1,863
  • 2
  • 15
  • 18
33

You can create an "inline table" with a UNION subquery:

(
            SELECT 10 AS id
  UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
  -- etc.
) AS inline_table
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Will this be a problem if my list is actually a few hundred items long (instead of 5 items as in the question)? – rinogo May 22 '12 at 20:16
  • @rinogo: Well, it would make your query quite long (which might mean it takes a while to parse), but I'm not aware of any inherent problems that might cause. – eggyal May 22 '12 at 20:17
  • 2
    @rinogo: with really large lists you can hit `max_allowed_packet` limit which won't allow you to submit the query, though it requires hundreds of thousands records with default settings. – Quassnoi May 22 '12 at 20:19
  • Well this is exactly what I needed! PS: I had a very limited need for the number of entries: just from 0 to 6 so this worked out pretty well – Hassen Ch. Dec 07 '17 at 16:13
  • ILLEGAL USE OF KEYWORD UNION. TOKEN , FROM INTO WAS EXPECTED. – john k Oct 29 '21 at 19:07
8
CREATE TEMPORARY TABLE ids (id INT NOT NULL PRIMARY KEY);

INSERT
INTO    ids
VALUES
(10),
(11),
(12),
(13),
(14);

SELECT  *
FROM    ids
WHERE   id NOT IN
        (
        SELECT  id
        FROM    a
        );
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • @mortezakavakebi: what do you mean? – Quassnoi Jul 26 '13 at 15:55
  • creating a table is not session base. when different users call a url containing this Query makes conflict on the "ids" table. – morteza kavakebi Jul 26 '13 at 17:11
  • 5
    @mortezakavakebi: from the docs: *A `TEMPORARY` table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-`TEMPORARY` table of the same name.* – Quassnoi Jul 26 '13 at 17:16
  • I am having a syntax error in : CREATE TEMPORARY TABLE ids (id INT NOT NULL PRIMARY KEY); Here's the message: Unknown object type 'TEMPORARY' used in a CREATE, DROP, or ALTER statement. – FrenkyB Jan 26 '16 at 17:00
  • 1
    @FrenkyB: that's normal when you try to run a MySQL query on SQL Server. – Quassnoi Jan 26 '16 at 17:27
  • You're right, I've solved a problem in sql server with declaring temporary table like this: declare @mytable table(a int not null, name nvarchar null) – FrenkyB Jan 27 '16 at 13:26
  • @FrenkyB: that's not a temporary table, that's a table variable (there is a number of differences between those things). But you're right, that would work there as well. – Quassnoi Jan 27 '16 at 13:30
  • Sorry, I've meant table variable. Of course, temporary table is something different. – FrenkyB Jan 27 '16 at 13:52
  • Isn't the `PRIMARY KEY` constraint automatically `NOT NULL` anyway? – Snackoverflow Oct 23 '18 at 22:44
  • @anddero yes it is – Quassnoi Oct 23 '18 at 22:49
  • `SQL Error [42601]: ILLEGAL USE OF KEYWORD TEMPORARY. TOKEN TRIGGER AUXILIARY AUX JAR PROCEDURE TABLESPACE UNIQUE INDEX WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=4.26.14` :( – john k Oct 29 '21 at 18:50
  • @johnktejik: why are you running MySQL code on DB2? – Quassnoi Oct 29 '21 at 20:43
0

Something like this will work too

    
    SELECT * FROM (
    SELECT 'ds' AS source
    UNION ALL
    SELECT 'cache' AS source
    ) as dataSource

----------
| source |
----------
| ds     |
----------
| cache  |
----------
user3654542
  • 76
  • 1
  • 4
-1
create table B (id int)
insert into B values (10),(11),(12),(13),(14)

select *
from B
left join A 
on A.id=B.id
where A.id is null

drop table B

http://sqlfiddle.com/#!6/6666c1/30

user3417837
  • 1
  • 1