Purpose

You can use a Join as a way to associate multiple tables within a Select statement. Joins may be either explicit or implicit. For example, the following is the example from the previous section restated as an explicit inner join:

SELECT * FROM EMP INNER JOIN DEP ON ID=EMPID
SELECT E.NAME, D.DEPTNAME 
FROM EMP E INNER JOIN DEP D ON E.DEPTID = D.ID;

whereas the following is the same statement as an implicit inner join:

SELECT * FROM EMP, DEP WHERE EMP.DEPTID=DEP.ID

Syntax

FROM table_name {RIGHT OUTER | INNER | LEFT OUTER | CROSS | FULL OUTER} JOIN table.key ON search-condition

Example

In this example, two tables are joined using LEFT OUTER JOIN. t1, the first table named includes nonmatching rows.

SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.KEY = T2.KEY

If you use a CROSS JOIN, no ON expression is allowed for the join.