The SQL Cross Join returns the Cartesian product of both the tables. Cartesian product means Number of Rows present in Table 1 Multiplied by Number of Rows present in Table 2.
TIP: Cross Join in SQL does not require any common column to join two table.
SQL Cross Join Syntax
The basic syntax of the Cross Join in SQL Server is as shown below:
|
1 2 3 4 5 6 7 8 9 |
-- SQL Server Cross Join Syntax SELECT Table1.Column(s), Table2.Column(s), FROM Table1 CROSS JOIN Table2 --OR We can Simply Write it as SELECT Table1. Column(s), Table2. Column(s), FROM Table1, Table2 |
In this article we will show you, How to write Cross Join in SQL Server. For this, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.
Data present in the Employee Table is as shown below:
Data present in the Department Table is:
SQL Cross Join Example
The following SQL Query will display the Cartesian product of the columns present in Employees and Department tables.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- SQL Server Cross Join Example SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp, CROSS JOIN [Department] AS Dept --OR We Can simply Write it as SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp, [Department] AS Dept |
OUTPUT
If you observe the above screenshot, It is displaying 120 records. It means 15 rows from the Employee multiplies by 8 rows in the Department table
Using Where Clause in SQL Cross Join
The SQL Server allows us to use the Where Clause to restrict the number of rows returned by the Cross Join. In this example, we will use that WHERE Clause along with the Cross Join.
T-SQL CODE
|
1 2 3 4 5 6 7 |
-- SQL Server Cross Join Example SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp, [Department] AS Dept WHERE Dept.[DepartmentName] = 'Software Developer' |
OUTPUT
Thank You for Visiting Our Blog
Leave a Reply