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'
A CROSS JOIN operation can be replaced with an INNER JOIN where the join
clause always evaluates to true (for example, 1=1). It can also be replaced with
a sub-query. So equivalent queries would be:
SELECT * FROM CITIES LEFT OUTER JOIN
FLIGHTS INNER JOIN COUNTRIES ON 1=1
ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'
SELECT * FROM CITIES LEFT OUTER JOIN
(SELECT * FROM FLIGHTS, COUNTRIES) S
ON CITIES.AIRPORT = S.ORIG_AIRPORT
WHERE S.COUNTRY_ISO_CODE = 'US'