Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Antijoins

Re: Antijoins

From: Pavel Polcar <pavel.polcar_at_berit.cz>
Date: Mon, 26 Oct 1998 09:02:56 +0100
Message-ID: <711ai6$7km$1@cbu.pvtnet.cz>


From Oracle8 Server Concepts: (you'll find more information there)

An anti-join returns rows from the left side of the predicate for which there is no
corresponding row on the right side of the predicate. That is, it returns rows that
fail to match (NOT IN) the subquery on the right side. For example, an anti-join can
select a list of employees who are not in a particular set of departments: SELECT * FROM emp
WHERE deptno NOT IN
(SELECT deptno FROM dept
WHERE loc = 'HEADQUARTERS');

A semi-join returns rows that match an EXISTS subquery, without duplicating rows
from the left side of the predicate when multiple rows on the right side satisfy the
criteria of the subquery. For example:
SELECT * FROM dept
WHERE EXISTS
(SELECT * FROM emp
WHERE dept.ename = emp.ename
AND emp.bonus > 5000);

Hth,

Pavel,

Gocha Mchedlishvili wrote in message ...
>Does anybody know what are antijoin and semijoin ?
>
>Thanks
>Gocha
>
>
Received on Mon Oct 26 1998 - 02:02:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US