Cross Join Vs Inner Join in SQL Server

How to write a Query to show the difference between Cross Join and Inner Join, or we can say, Cross Join Vs Inner Join in SQL Server with an example. For this Interview Question, We are going to use the below-shown data. Data present in the NewCustomer table inside our TEST database.

Left Table 1

Data present in the Department Table is:

Right Table Records 2

Cross Join Vs Inner Join in SQL Server

The definition behind the SQL Server Cross Join and Inner Join are:

  • INNER JOIN: It returns the records (or rows) present in both tables If there is at least one match between columns.
  • CROSS JOIN: It returns the Cartesian product of both tables. Cartesian product means the Number of Rows present in Table 1 Multiplied by the Number of Rows present in Table 2.

SQL Inner Join

The following INNER JOIN Query will display all columns present in the Employees and Department tables and the matched rows. Refer to the SQL INNER JOIN from the SQL Server tutorial page.

-- 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]
Cross Join Vs Inner Join 1

SQL Cross Join Example

The following SQL CROSS JOIN Query will display the Cartesian product of the columns present in the Employees and Department tables. Also, refer to the SQL LEFT JOIN and SQL RIGHT JOIN articles.

-- 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
Cross Join Vs Inner Join Example 2

If you observe the above SQL Server screenshot, it is displaying 80 records. It means 10 rows from the NewCustomers multiply by 8 rows in the Department table. Please refer to the SQL FULL JOIN and SQL SELF JOIN articles.

Categories SQL