Cross Join in SQL Server with Real-time Example
In this article, I am going to discuss the Cross Join in SQL Server with one real-time example. Please read our previous article where we discussed the basics of SQL Server Joins. As part of this article, we are going to discuss the following pointers in detail.
- What is Cross Join?
- How to implement Cross Join in SQL Server?
What is Cross Join?
When we combine two or more tables with each other without any condition (where or on) then we call this type of joins as Cartesian or cross join. In Cross Join, each record of a table is joined with each record of other table involved in the join. In SQL Server, the Cross Join should not have either ON or where clause.
How to implement Cross Join in SQL Server?
Let us understand how to implement the Cross Join with an example. Let’s create two Tables with the name Company and Candidate.
Please use the below SQL Script to create Company and Candidate tables and populate these two tables with some test data. Please note that the CompanyId column of the Candidate table is a foreign key referencing to the CompanyId column of the Company Table.
CREATE TABLE Company ( CompanyId TinyInt Identity Primary Key, CompanyName Nvarchar(50) NULL ) GO INSERT Company VALUES('DELL') INSERT Company VALUES('HP') INSERT Company VALUES('IBM') INSERT Company VALUES('Microsoft') GO CREATE TABLE Candidate ( CandidateId tinyint identity primary key, FullName nvarchar(50) NULL, CompanyId tinyint REFERENCES Company(CompanyId) ) GO INSERT Candidate VALUES('Ron',1) INSERT Candidate VALUES('Pete',2) INSERT Candidate VALUES('Steve',3) INSERT Candidate VALUES('Steve',NULL) INSERT Candidate VALUES('Ravi',1) INSERT Candidate VALUES('Raj',3) INSERT Candidate VALUES('Kiran',NULL) GO
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 will 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 RIGHT Table. In our example, we have 28 total numbers of rows in the result set. 7 rows in the Candidate table multiplied by 4 rows in the Company Table.
In SQL Server, the Cross Join does not have an ON clause with a Join condition. All other JOINS use ON clause with a Join Condition. If you try to use an ON clause on a CROSS JOIN then it will give you a syntax error.
In the next article, I am going to discuss Inner Join in SQL Server with one real-time example. Here, in this article, I try to explain the Cross Join with a real-time example. I hope you understood the need and use of Cross Join.