How Can I Show Elements Of Where Clause That Are Not Present In Table

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Table

SQL

SELECT *
FROM Table
WHERE ID IN (1234,9999,8888)

If ID is in where clause are not in table, then show those ids.

Ouput
9999,8888

Posted 28-Jul-15 23:04pm

Divs from Bangalore

Updated 28-Jul-15 23:22pm

Wendelius

v3

Add a Solution

3 solutions

Solution 2

I'd use a cte:

SQL

with mycte as (
  select 1234 as id,
  union select 9999,
  union select 8888)

select m.id 
from mycte m
where not exists (select * from table t where m.id = t.id)

it seems a long way around but it's VERY scaleable.

Hopefully it will at least push you in a new direction

Posted 28-Jul-15 23:19pm

Andy Lanng

Solution 3

There can be better ways to do this but check this and also wait for some better answers.
I have used EXCEPT

SQL

SELECT * FROM 
(
	SELECT 1234 AS ID
	UNION 
	SELECT 9999
	UNION
	SELECT 8888
) T1
EXCEPT
SELECT * FROM
(
	SELECT 1234 AS ID
	UNION
	SELECT 3456
	UNION
	SELECT 7890
) AS T2

Hope, it helps :)

Posted 28-Jul-15 23:23pm

Suvendu Shekhar Giri

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-28T23:28:00

Solution 4

A simple way would by to use EXCEPT to remove existing values from all values. Consider the following example

SQL

SELECT all_values.Id
FROM (SELECT 1234 AS Id UNION ALL
      SELECT 9999 AS Id UNION ALL
      SELECT 8888 AS Id ) all_values
EXCEPT
SELECT Id
FROM MyTable

For more information, see: EXCEPT and INTERSECT (Transact-SQL)[^]

Posted 28-Jul-15 23:28pm

Wendelius

Comments

Divs from Bangalore 29-Jul-15 6:46am

Thanks :)

Wendelius 29-Jul-15 6:47am

You're welcome

Animesh Datta 29-Jul-15 9:01am

Perfect one . My 5!

Wendelius 29-Jul-15 10:17am

Thank you.