Cross Join in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Cross Join in Oracle with Example

In this article, I am going to discuss the Cross Join in Oracle with Examples. Please read our previous article where we discussed the Full Outer Join in Oracle with Examples. As part of this article, we are going to discuss the following pointers in detail.

1. What is Cross Join?
2. How to implement Cross Join in Oracle?

What is Cross Join in Oracle?

When we combine two or more tables with each other without any condition (where or on) then we call this type of join as Cartesian or Cross Join. In Cross Join, each record of a table is joined with each record of the other table. In Oracle, the Cross Join should not have either an ON or WHERE clause.

How to implement Cross Join in Oracle?

Let us understand how to implement the Cross Join in Oracle with Examples. To understand CROSS Join, we are going to use the following Company and Candidate tables.

Please use the below SQL Script to create Company and Candidate tables and populate these two tables with test data.

CREATE TABLE Company
(
    CompanyId INT,
    CompanyName VARCHAR(20)
);

INSERT INTO Company VALUES(1, 'DELL');
INSERT INTO Company VALUES(2, 'HP');
INSERT INTO Company VALUES(3, 'IBM');
INSERT INTO Company VALUES(4, 'Microsoft');

CREATE TABLE Candidate
(
    CandidateId INT,
    FullName VARCHAR(20),
    CompanyId INT
);

INSERT INTO Candidate VALUES(1, 'Ron',1);
INSERT INTO Candidate VALUES(2, 'Pete',2);
INSERT INTO Candidate VALUES(3, 'Steve',3);
INSERT INTO Candidate VALUES(4, 'Smith',NULL);
INSERT INTO Candidate VALUES(5, 'Ravi',1);
INSERT INTO Candidate VALUES(6, 'Raj',3);
INSERT INTO Candidate VALUES(7, 'Kiran',NULL);

CROSS JOIN Example:

A Cross Join produces the Cartesian product of the tables involved in the join. The Cartesian product means the number of records present in the first table is multiplied by the number of records present in the second table. Please have a look at the below SQL query which is an example of Cross Join for joining the Candidate and Company Table.

SELECT Cand.CandidateId,
  Cand.FullName,
  Cand.CompanyId, 
  Comp.CompanyId,
  Comp.CompanyName
FROM Candidate Cand
CROSS JOIN Company Comp;

When we execute the above cross join query, it produces the result set as shown in the image below.

Points to remember when working with Cross Join:

The Cross Join produces the Cartesian product of the tables involved in the join. This means every row in the Left Table is joined to every row in the Right Table. The candidate is LEFT Table and Company is the RIGHT Table. In our example, we have 28 total numbers rows in the result set. 7 rows in the Candidate table multiplied by 4 rows in the Company Table. 

In the next article, I am going to discuss SELF JOIN in Oracle with Examples. Here, in this article, I try to explain the CROSS JOIN in Oracle with an Example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.