Database Reference
In-Depth Information
The following query shows that, as for the theta join, the keyword
OUTER
might be added to explicitly specify that
it's an outer join:
SELECT emp.ename, mgr.ename
FROM emp LEFT OUTER JOIN emp mgr ON emp.mgr = mgr.empno
In addition, with the new join syntax, it's possible to specify that all rows of both tables be returned by means of
a
full outer join.
In other words, rows of both tables that have no matching row in the other table are preserved. The
following query is an example:
SELECT mgr.ename AS manager, emp.ename AS subordinate
FROM emp FULL OUTER JOIN emp mgr ON emp.mgr = mgr.empno
Another possibility is to specify a
partitioned outer join
. Be careful: the word
partitioned
isn't related to the
physical partitioning of objects discussed in Chapter 13. Instead, its meaning is that data is divided at runtime into
several subsets. The idea is to perform an outer join not between two tables but between one table and subsets of
another table. For example, in the following query, the
emp
table is divided into subsets based on the
job
column.
Then each subset is outer joined with the
dept
table:
SELECT dept.dname, count(emp.empno)
FROM dept LEFT JOIN emp PARTITION BY (emp.job) ON emp.deptno = dept.deptno
WHERE emp.job = 'MANAGER'
GROUP BY dept.dname
Semi-joins
A
semi-join
between two tables returns rows from one table when matching rows are available in the other table.
Contrary to a theta join, rows from the left input are returned once at most. In addition, data from the right input isn't
returned at all. The join condition is written with
IN
,
EXISTS
,
ANY
, or
SOME
. The following queries are examples:
SELECT deptno, dname, loc
FROM dept
WHERE deptno IN (SELECT deptno FROM emp)
SELECT deptno, dname, loc
FROM dept
WHERE EXISTS (SELECT deptno FROM emp WHERE emp.deptno = dept.deptno)
SELECT deptno, dname, loc
FROM dept
WHERE deptno = ANY (SELECT deptno FROM emp)
SELECT deptno, dname, loc
FROM dept
WHERE deptno = SOME (SELECT deptno FROM emp)
