SQL Server Cross Join

SQL Server Cross Join

In this article, I am going to discuss SQL Server Cross Join with one real-time example. Please read our previous article where we discussed the basics of SQL Server Joins. 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 SQL Server. 

In SQL Server Cross Join, each record of a table is joined with each record of other table involved in the join. The Cross Join in SQL Server should not have either ON or where clause.

Example:

Let us understand the SQL Server 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 in SQL Server 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 SQL Server 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.

SQL Server Cross Join example

Points to remember when working with SQL Server Cross Join:

An SQL Server 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. 

The SQL Server 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 will discuss Inner Join in SQL Server with one real-time example.

SUMMARY

In this article, I try to explain the SQL Server Cross Join with a real-time 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.

Leave a Reply

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