Multi-table queries in Oracle

Multi-Table QueryL Cartesian product : N*m

L use associated fields to eliminate redundant data from the Cartesian product :

SELECT emp.*,dept. Dname,dept. LOC from EMP, DEPT WHERE EMP. Deptno=dept. DEPTNO;

L multiple table queries must have associated fields

SELECT E.ename,e.job,e.hiredate,d.deptno,d.loc From EMP E, DEPT D WHERE E.deptno = D.deptno;

L union- Collection Operations

Union merges two or more two query results into one result, and all duplicate records are excluded from the query results.

SELECT * from EMP UNION SELECT * from EMP WHERE deptno=10;

UNION all places two or more query results in one result, preserving all records (relative to UNION).

SELECT * from EMP UNION All SELECT * from EMP WHERE deptno=10;

INTERSECT Finding common data (intersection) of multiple results

SELECT * from EMP INTERSECT SELECT * from EMP WHERE deptno=10;

Minus Subtract the second result (difference set) with the first result

SELECT * from EMP Minus SELECT * from EMP WHERE DEPTNO = 10;

Multi-table queries in Oracle