How to write a SQL Query to show the difference between Cross Join and Inner Join in SQL Server, or we can say, Cross Join Vs Inner Join in SQL Server with example. For this SQL Interview Question, We are going to use the below-shown data. Data present in the NewCustomer table inside our SQL TEST database.
Data present in the Department Table is:
Cross Join Vs Inner Join in SQL Server
The definition behind the SQL Server Cross Join and Inner Join are:
- SQL INNER JOIN: It returns the records (or rows) present in both tables If there is at least one match between columns.
- SQL CROSS JOIN: It returns the Cartesian product of both the SQL Server tables. Cartesian product means Number of Rows present in Table 1 Multiplied by Number of Rows present in Table 2.
SQL Inner Join
The following SQL INNER JOIN Query will display all the columns present in Employees and Department tables, and the matched rows
-- Example for Cross Join Vs Inner Join in SQL Server
USE [SQLTEST]
GO
SELECT Cust.[FirstName] AS [First Name]
,Cust.[LastName] AS [Last Name]
,Cust.[Education]
,Cust.[Occupation]
,Dept.[DepartmentName] AS [Department Name]
,Cust.[YearlyIncome]
FROM [NewCustomers] AS Cust
INNER JOIN
[Department] AS Dept ON
Cust.[DeptID] = Dept.[DeptID]
SQL Cross Join Example
The following SQL CROSS JOIN Query will display the Cartesian product of the columns present in Employees and Department tables. It is one of the SQL Server Frequently Asked Question.
-- Example for Cross Join Vs Inner Join in SQL Server
USE [SQLTEST]
GO
SELECT Cust.[FirstName] AS [First Name]
,Cust.[LastName] AS [Last Name]
,Cust.[Education]
,Cust.[Occupation]
,Dept.[DepartmentName] AS [Department Name]
,Cust.[YearlyIncome]
FROM [NewCustomers] AS Cust
CROSS JOIN
[Department] AS Dept
If you observe the above SQL Server screenshot, It is displaying 80 records. It means 10 rows from the NewCustomers multiplies by 8 rows in the Department table