Home » SQL & PL/SQL » SQL & PL/SQL » Inline query with not exists clause (oracle 10g)
Inline query with not exists clause [message #358253] Mon, 10 November 2008 06:18 Go to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
hi,

can we use inline query with not exists clause eg.

select * from ( select code,b_date from tab) m 
where not exists
(select code from tab2 n where
m.code=n.code)


Or any other solution if it is not possible

thanks.
Re: Inline query with not exists clause [message #358256 is a reply to message #358253] Mon, 10 November 2008 06:23 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes , You can ...


SQL> SELECT * FROM ( SELECT DEPTNO FROM DEPT) D;

    DEPTNO
----------
        10
        20
        30
        40

SQL> SELECT * FROM ( SELECT DEPTNO FROM DEPT) D
  2  WHERE EXISTS ( SELECT NULL
  3                 FROM   EMP E
  4                 WHERE  E.DEPTNO = D.DEPTNO);

    DEPTNO
----------
        10
        20
        30

SQL> SELECT * FROM ( SELECT DEPTNO FROM DEPT) D
  2  WHERE NOT EXISTS ( SELECT NULL
  3                 FROM   EMP E
  4                 WHERE  E.DEPTNO = D.DEPTNO);

    DEPTNO
----------
        40

SQL>


Smile
Rajuvan.

[Updated on: Mon, 10 November 2008 06:26]

Report message to a moderator

Previous Topic: exception handling
Next Topic: What is the best tool for generating DDL of entire database?
Goto Forum:
  


Current Time: Sun Dec 04 04:51:11 CST 2016

Total time taken to generate the page: 0.14411 seconds