CROSS JOIN is a join operation, that returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table. Unlike other JOIN operators, it does not let you specify a join clause. You may, however, specify a WHERE clause in the SELECT statement.
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.
Examples
The following SELECT statements are equivalent:
SELECT *
FROM TEACHER
CROSS JOIN COURSE
SELECT *
FROM TEACHER, COURSE;
The following SELECT statements are equivalent:
SELECT *
FROM TEACHER
CROSS JOIN COURSE
WHERE TEACHER.ID = COURSE.TEACHER_ID
SELECT *
FROM TEACHER
INNER JOIN COURSE
ON TEACHER.ID = COURSE.TEACHER_ID
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 TEACHER LEFT OUTER JOIN
COURSE INNER JOIN UNIVERSITY ON 1=1
ON TEACHER.ID = UNIVERSITIES.TEACHER_ID
WHERE UNIVERSITY.COUNTRY = 'US'
SELECT FROM TEACHER LEFT OUTER JOIN
(SELECT FROM COURSE, UNIVERSITY) S
ON TEACHER.ID = S.TEACHER_ID
WHERE S.COUNTRY = 'US'
Links
