top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

ORACLE: What is CROSS JOIN?

+2 votes
64 views
ORACLE: What is CROSS JOIN?
posted Aug 19, 2015 by Archana

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

1 Answer

+1 vote

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'

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'

answer Aug 20, 2015 by Manikandan J
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...