Database Reference
In-Depth Information
Anti-joins
An
anti-join
is a special type of semi-join, where only rows from one table without matching rows in the other
table are returned. The join condition is usually written with
NOT IN
or
NOT EXISTS
. The following two queries are
examples:
SELECT deptno, dname, loc
FROM dept
WHERE deptno NOT IN (SELECT deptno FROM emp)
SELECT deptno, dname, loc
FROM dept
WHERE NOT EXISTS (SELECT deptno FROM emp WHERE emp.deptno = dept.deptno)
Lateral Inline Views
A
lateral inline view
is an inline view (a query specified in the
FROM
clause of another query) that contains a correlation
referring to other tables that precede it in the
FROM
clause. From version 12.1 onward, lateral inline views are
supported through the
LATERAL
keyword. The following query shows an example:
SELECT dname, ename
FROM dept, LATERAL(SELECT * FROM emp WHERE dept.deptno = emp.deptno)
Note that if the
LATERAL
keyword is missing, the following error is raised:
SQL> SELECT dname, empno
2 FROM dept, (SELECT * FROM emp WHERE dept.deptno = emp.deptno);
FROM dept, (SELECT * FROM emp WHERE dept.deptno = emp.deptno)
*
ERROR at line 2:
ORA-00904: "DEPT"."DEPTNO": invalid identifier
For outer joins and cross joins, a similar functionality is provided through the
OUTER APPLY
and
CROSS APPLY
keywords.
Restrictions vs. Join Conditions
To choose a join method, it's essential to understand the difference between
restrictions
(also known as
filtering
conditions
) and
join conditions
. From a syntactical point of view, the two might be confused only when the legacy join
syntax is used. In fact, with the legacy join syntax, the
WHERE
clause is used to specify both the restrictions and the join
conditions. With the new join syntax, the restrictions are specified in the
WHERE
clause, and the join conditions are
specified in the
FROM
clause. The following pseudo SQL statement illustrates this:
SELECT <columns>
FROM <table1> [OUTER] JOIN <table2> ON ( <join conditions> )
WHERE <restrictions>
