Tutorial Gateway

Cross Join Vs Inner Join in SQL Server

by

In this article we will show you, How to write a SQL Query to show the difference between Cross Join and Inner Join in SQL Server, or we can simply say, Cross Join Vs Inner Join in SQL Server with example. This is one of the common SQL Interview Question that you might face in the interviews. For this example, We are going to use the below shown data

Data present in the NewCustomer table inside our SQL TEST database.

Cross Join Vs Inner Join in SQL Server 1

Data present in the Department Table is:

Cross Join Vs Inner Join in SQL Server 2

Cross Join Vs Inner Join in SQL Server

The definition behind these two joins 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 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

T-SQL CODE

-- 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]

OUTPUT

Cross Join Vs Inner Join in SQL Server 3

SQL Cross Join Example

The following SQL CROSS JOIN Query will display the Cartesian product of the columns present in Employees and Department tables.

T-SQL CODE

-- 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

OUTPUT

Cross Join Vs Inner Join in SQL Server 4

If you observe the above screenshot, It is displaying 80 records. It means 10 rows from the NewCustomers multiplies by 8 rows in the Department table

Thank You for Visiting Our Blog