How to Use Cross Join, Natural Join, and Condition Join in SQL
SQL supports a number of types of joins. The best one to choose in a given situation depends on the result you’re trying to achieve. Here are some details to help you choose which one you need.
CROSS JOIN is the keyword for the basic join without a WHERE clause. Therefore
SELECT * FROM EMPLOYEE, COMPENSATION ;
can also be written as
SELECT * FROM EMPLOYEE CROSS JOIN COMPENSATION ;
The result is the Cartesian product (also called the cross product) of the two source tables. CROSS JOIN rarely gives you the final result you want, but it can be useful as the first step in a chain of data-manipulation operations that ultimately produce the desired result.
The natural join is a special case of an equi-join. In the WHERE clause of an equi-join, a column from one source table is compared with a column of a second source table for equality. The two columns must be the same type and length and must have the same name.
In fact, in a natural join, all columns in one table that have the same names, types, and lengths as corresponding columns in the second table are compared for equality.
Imagine that the COMPENSATION table from the preceding example has columns EmpID, Salary, and Bonus rather than Employ, Salary, and Bonus. In that case, you can perform a natural join of the COMPENSATION table with the EMPLOYEE table. The traditional JOIN syntax would look like this:
SELECT E.*, C.Salary, C.Bonus FROM EMPLOYEE E, COMPENSATION C WHERE E.EmpID = C.EmpID ;
This query is a special case of a natural join. The SELECT statement will return joined rows where E.EmpID = C.EmpID. Consider the following:
SELECT E.*, C.Salary, C.Bonus FROM EMPLOYEE E NATURAL JOIN COMPENSATION C ;
This query will join rows where E.EmpID = C.EmpID, where E.Salary = C.Salary, and where E.Bonus = C.Bonus. The result table will contain only rows where all corresponding columns match. In this example, the results of both queries will be the same because the EMPLOYEE table does not contain either a Salary or a Bonus column.
A condition join is like an equi-join, except the condition being tested doesn’t have to be an equality (although it can be). It can be any well-formed predicate. If the condition is satisfied, then the corresponding row becomes part of the result table. The syntax is a little different from what you have seen so far: The condition is contained in an ON clause rather than in a WHERE clause.
Say that a baseball statistician wants to know which National League pitchers have pitched the same number of complete games as one or more American League pitchers. This question is a job for an equi-join, which can also be expressed with condition-join syntax:
SELECT * FROM NATIONAL JOIN AMERICAN ON NATIONAL.CompleteGames = AMERICAN.CompleteGames ;