Module 3: Cross Join Explained

Previous Tutorial - Next Tutorial

A cross join is the simplest type of join, though not the most commonly used one. This join performs the Cartesian product of the two input tables. In other words, it performs a multiplication between the tables and returns a row for each combination of rows from both sides of the table. For example, if you have X rows in table T-1 and Y rows in table T-2, then the result of a cross join between tables T1 and T2 is a virtual table with X multiplication Y rows. Needless to say, this can produce a large volume of rows and could be an effective way to populate a test database table with some simple data.

In most situations, the result of a cross join will not make sense except if you have some precise requirement. This is the general syntax of cross join. It's very straightforward and there is no need to define a predicate with ON keyword. In the diagram, the left table has three rows with key values A, B, and C. The right table has four rows with the key values, B1, B2, C1, and D1. If I use a cross join for these two tables, then the result of this query is a table with 12 rows containing all possible combinations of rows from the two input tables.

Now let's open the SQL Server Management Studio to see this cross join in the action. To easily understand this join, I'm going to use the product category table as this table contains only four records. If I execute this query, I'm getting four rows. Now I'm going to do the cross join with the same table. The cross join between these two tables will give us four multiplication four equal to sixteen (4x4=16)rows. To use the same table, I need to give the alias on this table.

Now join the same table using the cross join. So for that, I need to write(CROSS JOIN Production.ProductCategory AS pc2). Use this alias in the column name. Now, add the order by clause to easily understand the result. The product category table contains four records, and if I execute this query, then you clearly see that for every product category, four rows are added to the result set.

If you do the math, then the result will contain four multiplications; four is equal to 16 (4x4=16)rows. This is how a cross join works. You can rewrite the same query again using the older style syntax where you specify a comma between these two tables. You can simply omit the cross join syntax and separate the table reference with the comma. First of all, copy the same query. I just need to specify a comma between these two tables.

If I execute this query, then it will give me the same result, 16 rows. However, it is recommended to stick to the cross join syntax as it is less prone to errors and allows for more consistent code.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials:

• General SQL Introduction
• Module 1: Getting Started with SQL Server
• Module 1: SQL Server Management
• Module 1: Essential SQL Commands
• Module 1: SQL Introduction Essentials
• Module 2: Learning SQL Overview
• Module 2: Logical Query Processing Order
• Module 2: Select Statement Fundamentals
• Module 2: Filtering Data with WHERE Clause Overview
• Module 2: Filter Data with WHERE Clause - Part 1
• Module 2: How to Filter Data with WHERE Clause - Part 2
• Module 2: Filter PDF Data Using WHERE clause - Part 3
• Module 2: Sorting Data Using ORDER BY Clause
• Module 2: Grouping Data with GROUP BY Clause Part 1
• Module 2: Grouping Data with GROUP BY Clause Part 2
• Module 2: Grouping Data with GROUP BY Clause Part 3
• Module 2: Limiting Data With TOP Clause
• Module 2: Summary and Quiz
• Module 3: SQL Joins and SET Operators
• Module 3: Understanding Inner Join
• Module 3: Understanding Outer Join
• Module 3: Cross Join Explained
• Module 3: Self Join Explained
• Module 3: UNION and UNION ALL
• Module 3: INTERSECT and EXCEPT
• Module 3: Understanding Sub-Queries
• SQL Analytic Functions