With Oracle, we are used to joining datasets by combinations of datasources in the FROM clause and a WHERE clause to join them together. Datasets are usually tables, but can also be views, in-inline views, subqueries, table functions, nested tables and so on. Oracle join syntax is generally as follows: SELECT ... FROM dataset_one d1 , dataset_two d2 WHERE d1.column(s) = d2.column(s) AND ... With this syntax we separate datasources by commas and code a single WHERE clause that will include the join predicates together with any filter predicates we might require. ANSI join syntax is slightly different on two counts. First, we specify the type of join we require and second we separate the join predicates from the filter predicates. ASNI syntax can notionally be expressed as follows: SELECT ... FROM dataset_one d1 JOIN TYPE dataset_two d2 ON (d1.column(s) = d2.column(s)) --<-- can also use USING (column(s)) WHERE filter_predicates... As commented, the ON clause is where we specify our joins. If the column names are the same, we can replace this with a USING clause. We will see examples of both methods for expressing join predicates throughout this article. Given this pseudo-syntax, we will examples of the following join types in this article. * INNER JOIN * NATURAL JOIN * CROSS JOIN * LEFT OUTER JOIN * RIGHT OUTER JOIN * FULL OUTER JOIN inner join When we join two tables or datasets together on an equality (i.e. column or set of columns) we are performing an inner join. The ANSI method for joining EMP and DEPT is as follows. SQL> SELECT d.dname, d.loc, e.ename, e.job 2 FROM dept d 3 INNER JOIN 4 emp e 5 USING (deptno); DNAME LOC ENAME JOB -------------- ------------- ---------- --------- RESEARCH DALLAS SMITH CLERK SALES CHICAGO ALLEN SALESMAN SALES CHICAGO WARD SALESMAN RESEARCH DALLAS JONES MANAGER SALES CHICAGO MARTIN SALESMAN SALES CHICAGO BLAKE MANAGER ACCOUNTING NEW YORK CLARK MANAGER RESEARCH DALLAS SCOTT ANALYST ACCOUNTING NEW YORK KING PRESIDENT SALES CHICAGO TURNER SALESMAN RESEARCH DALLAS ADAMS CLERK SALES CHICAGO JAMES CLERK RESEARCH DALLAS FORD ANALYST ACCOUNTING NEW YORK MILLER CLERK
