Cross Join in SQL Server

Cross Join in SQL Server with Example

In this article, I am going to discuss the Cross Join in SQL Server with Examples. Please read our previous article where we discussed the Self Join in SQL Server 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 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 Cartesian or cross join. In Cross Join, each record of a table is joined with each record of the 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 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.

Cross Join in SQL Server

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 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 the 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 Indexes in SQL Server with Examples. Here, in this article, I try to explain the Cross Join in SQL Server with Example. I hope you understood the need and use of Cross Join. 

Leave a Reply

Your email address will not be published. Required fields are marked *