Cross join in sql server

The following illustrates the syntax of SQL Server CROSS JOIN of two tables:
The CROSS JOIN joined every row from the first table (T1) with every row from the second table (T2). In other words, the cross join returns a Cartesian product of rows from both tables.
Unlike the INNER JOIN or LEFT JOIN, the cross join does not establish a relationship between the joined tables.
Suppose the T1 table contains three rows 1, 2, and 3 and the T2 table contains three rows A, B, and C.
The CROSS JOIN gets a row from the first table (T1) and then creates a new row for every row in the second table (T2). It then does the same for the next row for in the first table (T1) and so on.
SQL Server CROSS JOIN example
In this illustration, the CROSS JOIN creates nine rows in total. In general, if the first table has n rows and the second table has m rows, the cross join will result in n x m rows.

SQL Server CROSS JOIN examples

The following statement returns the combinations of all products and stores. The result set can be used for stocktaking procedure during the month-end and year-end closings:
Here is the partial output:
The following statement finds the products that have no sales across the stores:
The following picture shows the partial result set:
SQL Server Cross Join StockTaking example
In this tutorial, you have learned how to use the SQL Server CROSS JOIN to create Cartesian products of rows from the joined tables.

Post a Comment