Home » SQL & PL/SQL » SQL & PL/SQL » query to find the values which are not in IN clause (Oracle, 10g, windows XP)
query to find the values which are not in IN clause [message #355025] Wed, 22 October 2008 05:29 Go to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi,

I need to find the values which are not in IN clause:
for ex:
SQL> select sal from emp where sal in(800,100);
 
       SAL
----------
       800
 


In the above ex: 100 is not in the emp table's sal column
Like that for thousands of rows i need to find that value which are not in the table but unknowingly typed in IN clause

Means in this example i need to get the output 100

How can that be achieved??
Re: query to find the values which are not in IN clause [message #355027 is a reply to message #355025] Wed, 22 October 2008 05:41 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Put the values in a view or table or select them from dual and then you have a set you can check against the emp table
select 100 from dual
union all
select 121 from dual
...
...
minus
select sal from emp


MHE

[Updated on: Wed, 22 October 2008 06:16]

Report message to a moderator

Re: query to find the values which are not in IN clause [message #355028 is a reply to message #355025] Wed, 22 October 2008 05:41 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Use MINUS

Smile
Rajuvan.
Re: query to find the values which are not in IN clause [message #355030 is a reply to message #355025] Wed, 22 October 2008 05:43 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Maaher,

Thanks,
but if in the IN clause if i've 100's of rows then for 100 times i need to write the union all clause..

Any simple query than this
Re: query to find the values which are not in IN clause [message #355031 is a reply to message #355025] Wed, 22 October 2008 05:47 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@jyothsna1612,

One way is, using a combination of UNION Query and MINUS Query

***Hint
SQL> SELECT 100 SALARY_CHECK FROM Dual
  2  UNION
  3  SELECT 800 FROM Dual;

SALARY_CHECK
------------
         100
         800



Hope this helps.

I am curious of any other logic too.

Regards,
Jo
Re: query to find the values which are not in IN clause [message #355032 is a reply to message #355025] Wed, 22 October 2008 05:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Is it a big deal ? Anyway in your case you need to type 99 commas . you can use any text editor to do the same .

Smile
Rajuvan.
Re: query to find the values which are not in IN clause [message #355037 is a reply to message #355032] Wed, 22 October 2008 06:02 Go to previous messageGo to next message
sundha
Messages: 6
Registered: November 2007
Junior Member
Hi,

Try the following query.

SELECT * FROM
(SELECT rn FROM
(SELECT ROWNUM rn FROM dual CONNECT BY LEVEL <=1000)
WHERE rn IN (800,100) --Give your IN clause values here
)WHERE rn NOT IN (SELECT sal FROM EMP)

Regards,
Sundari
Re: query to find the values which are not in IN clause [message #355040 is a reply to message #355025] Wed, 22 October 2008 06:09 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ Sundari ,

Not a bad idea ...

Smile
Rajuvan.
Re: query to find the values which are not in IN clause [message #355041 is a reply to message #355030] Wed, 22 October 2008 06:17 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
jyothsna1612 wrote on Wed, 22 October 2008 12:43
Maaher,

Thanks,
but if in the IN clause if i've 100's of rows then for 100 times i need to write the union all clause..

Any simple query than this
I tend to let SQL do the typing for me. I was merely explaining the basic idea.

MHE
Re: query to find the values which are not in IN clause [message #355061 is a reply to message #355037] Wed, 22 October 2008 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
sundha wrote on Wed, 22 October 2008 13:02
Hi,

Try the following query.

SELECT * FROM
(SELECT rn FROM
(SELECT ROWNUM rn FROM dual CONNECT BY LEVEL <=1000)
WHERE rn IN (800,100) --Give your IN clause values here
)WHERE rn NOT IN (SELECT sal FROM EMP)

Regards,
Sundari


rajavu1 wrote on Wed, 22 October 2008 13:09
@ Sundari ,

Not a bad idea ...

Smile
Rajuvan.


A bad idea.
Who say that sal can't exceed 1000?
Who say that sal can't be 0?
Who say that sal is an integer?
Who say that sal can't be null?
What about the performances?

Regards
Michel
Re: query to find the values which are not in IN clause [message #355063 is a reply to message #355061] Wed, 22 October 2008 07:31 Go to previous message
sundha
Messages: 6
Registered: November 2007
Junior Member
Hi,

It's not a exact solution, This is just a base to complete his requirement.......

Regards,
Sundari
Previous Topic: Index with max function (merged 3)
Next Topic: SQL GROUP BY
Goto Forum:
  


Current Time: Thu Feb 06 17:01:54 CST 2025