background image
<< RIGHT OUTER JOIN operation | NATURAL JOIN operation >>

CROSS JOIN operation

<< RIGHT OUTER JOIN operation | NATURAL JOIN operation >>
Derby Reference Manual
105
-- countries without any cities
SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME
FROM CITIES
RIGHT OUTER JOIN COUNTRIES
ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
WHERE Countries.region = 'Africa'
-- use the synonymous syntax, RIGHT JOIN, to achieve exactly
-- the same results as in the example above
SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME
FROM CITIES
RIGHT JOIN COUNTRIES
ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
WHERE Countries.region = 'Africa'
Example 2
-- a TableExpression can be a joinOperation. Therefore
-- you can have multiple join operations in a FROM clause
-- List every employee number and last name
-- with the employee number and last name of their manager
SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME
FROM EMPLOYEE E RIGHT OUTER JOIN
DEPARTMENT RIGHT OUTER JOIN EMPLOYEE M
ON MGRNO = M.EMPNO
ON E.WORKDEPT = DEPTNO
CROSS JOIN operation
A CROSS JOIN is a
JOIN operation
that produces the Cartesian product of two tables.
Unlike other JOIN operators, it does not let you specify a join clause. You may, however,
specify a WHERE clause in the SELECT statement.
Syntax
TableExpression
CROSS JOIN {
TableViewOrFunctionExpression
| (
TableExpression
) }
Examples
The following SELECT statements are equivalent:
SELECT * FROM CITIES CROSS JOIN FLIGHTS
SELECT * FROM CITIES, FLIGHTS
The following SELECT statements are equivalent:
SELECT * FROM CITIES CROSS JOIN FLIGHTS
WHERE CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
SELECT * FROM CITIES INNER JOIN FLIGHTS
ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
The following example is more complex. The ON clause in this example is associated
with the LEFT OUTER JOIN operation. Note that you can use parentheses around a
JOIN operation.
SELECT * FROM CITIES LEFT OUTER JOIN
(FLIGHTS CROSS JOIN COUNTRIES)
ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'