OraFAQ Forum: SQL & PL/SQL » How To Select Deptno From Dept Were There Are No Employees ???

Home » SQL & PL/SQL » SQL & PL/SQL » How To Select Deptno From Dept Were There Are No Employees ???

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How To Select Deptno From Dept Were There Are No Employees ??? [message #121464]

Mon, 30 May 2005 00:42

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

suppose in our emp-dept table ,
We have a deptno in Dept table where there is no employees in Emp table.

I would like to select that deptno.

SQL> select deptno from dept where deptno not in(select distinct deptno from emp);

will work ofcourse ....

What are the other alternatives ?????

any respond is appreciated ....

Rajuvan.

[Updated on: Mon, 30 May 2005 00:49]

Report message to a moderator

Re: How To Select Deptno From Dept Were There Are No Employees ??? [message #121475 is a reply to message #121464]

Mon, 30 May 2005 02:55

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

scott@ORA92> select dept.deptno
  2  from   dept, emp
  3  where  dept.deptno = emp.deptno (+)
  4  and    emp.deptno is null
  5  /

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

scott@ORA92>

Report message to a moderator

Re: How To Select Deptno From Dept Were There Are No Employees ??? [message #121501 is a reply to message #121464]

Mon, 30 May 2005 06:17

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Thanx for your reply.....

I still want to know whether there is other alternatives(2 or 3).....

Rajuvan

Report message to a moderator

Re: How To Select Deptno From Dept Were There Are No Employees ??? [message #121526 is a reply to message #121464]

Mon, 30 May 2005 07:51

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

SELECT d.deptno
,      d.dname
FROM   dept        d
WHERE  NOT EXISTS (SELECT NULL
                   FROM   emp   e
                   WHERE  e.deptno = d.deptno)
/

For more on EXISTS, click this link to the documentation.

(Note, Rajavu1, in your example, the DISTINCT is unnecessary.)

Report message to a moderator

Re: How To Select Deptno From Dept Were There Are No Employees ??? [message #121530 is a reply to message #121464]

Mon, 30 May 2005 07:57

prathibha_1201
Messages: 11
Registered: May 2005

Junior Member

Hi Rajuvan,

select d.deptno from dept d,emp e where e.deptno(+)=d.deptno
minus
select d.deptno from dept d,emp e where e.deptno=d.deptno(+);

Hope this also may help u.

With Regards,
Prathibha.

Report message to a moderator

Re: How To Select Deptno From Dept Were There Are No Employees ??? [message #121531 is a reply to message #121464]

Mon, 30 May 2005 08:02

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Thanx for spending time for me....

Rajuvan

Report message to a moderator

Re: How To Select Deptno From Dept Were There Are No Employees ??? [message #121608 is a reply to message #121464]

Tue, 31 May 2005 01:33

nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait

Member

The best way to write this is:

select deptno from dept where deptno not in (select deptno from emp);

Period, plain and simple. IF deptno is NULLABLE in emp, you might select deptno from dept where deptno not in (select deptno from emp where deptno is not null);
(else, if there is one NULL deptno in EMP, the result set is always "empty")
You have "where not exists" as well as not in and the "anti join" you coded and yeah you always have a "just outer join and use HAVING COUNT(emp.empno) = 0" as well

EXAMPLES
========
SELECT deptno
FROM scott.dept
WHERE NOT (deptno IN (SELECT DISTINCT deptno
FROM scott.emp));
SELECT dept.deptno
FROM scott.dept, scott.emp
WHERE dept.deptno = emp.deptno(+) AND emp.deptno IS NULL;

SELECT d.deptno, d.dname
FROM dept d
WHERE NOT EXISTS (SELECT NULL
FROM emp e
WHERE e.deptno = d.deptno);
SELECT deptno
FROM dept
WHERE deptno NOT IN (SELECT deptno
FROM emp
WHERE deptno IS NOT NULL);

SELECT dept.deptno
FROM dept, emp
WHERE dept.deptno = emp.deptno(+)
GROUP BY dept.deptno
HAVING COUNT (emp.empno) = 0;

SELECT d.deptno, e.ename
FROM dept d LEFT OUTER JOIN emp e
ON d.deptno = e.deptno
WHERE e.ename IS NULL
ORDER BY d.deptno;

Report message to a moderator

Previous Topic:	Query regarding RPAD function.
Next Topic:	subquery in values clause (merged threads)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 04 11:33:30 CDT 2025