• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • Index
    • Views
    • Triggers
    • Stored Procedures
    • User-defined Functions
  • FUNCTIONS
Home / SQL Server Basics / SQL Server CROSS JOIN

SQL Server CROSS JOIN

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use CROSS JOIN to combine rows from two or more unrelated tables.

What is CROSS JOIN in SQL Server?

CROSS JOIN is used to combine the rows of two or more unrelated tables such that operation produces a resultset which is the Cartesian Product of the participating tables. Since the tables are unrelated, we do not have a to specify any column on which to JOIN the tables. CROSS JOINS are used to generate an exhaustive combination of datasets using data from the participating tables.

What is a CARTESIAN PRODUCT?

A CARTESIAN PRODUCT is a set operation which combines every element of a set with every other element of the other set. Suppose we have a SET A with x elements and another set B with y elements the result of the CARTESIAN PRODUCT will have x*y elements. To be more explicit, if set A has 2 elements a and b and set B has 3 elements 1, 2 and 3 then the CARTESIAN PRODUCT of A and B will be a1, a2, a3, b1, b2, b3.

It can be represented as a Venn Diagram like below.

Operation

A CROSS JOIN is implemented using the CROSS JOIN SQL clause in a SELECT statement and can be used to combine the datasets of two or more tables.

SQL Server CROSS JOIN Syntax

The basic syntax of CROSS JOIN is given below.

SELECT column_list
FROM table1
CROSS JOIN
table2

In this syntax,

  • column_list – the list of columns to be used from the participating tables by the SELECT statement.
  • table1 – the first or left table.
  • table2 – the second or right table.
  • CROSS JOIN – SQL keyword combination to implement a CROSS JOIN (where the resultset is a CARTESIAN PRODUCT of the participating tables).

SQL Server CROSS JOIN Examples

Let us see how we can use CROSS JOIN practically with an example. Suppose we have 3 tables in the database – a breakfast table containing breakfast item descriptions, a beverage table containing the list of beverages available and an additional table for additional or add-on items to complete a breakfast menu. The tables are represented below.

item_noitem_name
1French Toast
2Bread Butter
3Pasta
Table: breakfast

item_noitem_name
1French Toast
2Bread Butter
3Pasta
Table: beverage
item_noitem_name
1Scrambled Eggs
2Sausages
Table: additional

1) SQL Server CROSS JOIN  –  two table example

We will do a CROSS JOIN on the first two tables (breakfast and beverage) with the following SQL query. We have aliased the breakfast table as b1 and beverage table as b2 and sorted the output by the first column (i.e. b1.item_name) of the resultset using the ORDER BY clause.

SELECT
b1.item_name, b2.item_name
FROM breakfast b1
CROSS JOIN
beverage b2
ORDER BY 1;

It will produce the following output. We can see that Bread Butter has been combined with Tea, Coffee and Juice -Choice of Fruit (i.e. with every row of the other table). Likewise, for French Toast and Pasta and we have a total of 3*3=9 which is the cartesian product of the number of rows or records in the two tables.

item_nameitem_name
Bread ButterTea
Bread ButterCoffee
Bread ButterJuice – Choice of Fruit
French ToastTea
French ToastCoffee
French ToastJuice – Choice of Fruit
PastaTea
PastaCoffee
PastaJuice – Choice of Fruit

2)  SQL Server CASE  –  three table example

Now let us try and CROSS JOIN all the 3 tables with the following query. We have aliased the breakfast table as b1, the beverage table as b2 and the additional table as a and sorted the output by the first column (i.e. b1.item_name) and second column (i.e. b.item_name) of the resultset using the ORDER BY clause.

SELECT
b1.item_name, b2.item_name, a.item_name
FROM breakfast b1
CROSS JOIN beverage b2
CROSS JOIN additional a
ORDER BY 1, 2;

It will produce the following output. We can see that Bread Butter has been combined with Coffee and the combination has been combined with Scrambled Eggs and Sausages to produce 2 rows. Likewise, for the combination of Bread Butter and Juice and Bread Butter and Tea producing 2+2+2=6 rows and 6+6+6=18 rows taking into account all the 3 items (Bread Butter, French Toast and Pasta) of the first (breakfast) table. The same is equivalent to the cartesian product of the number of rows in the 3 tables i.e. 3*3*2=18.

item_nameitem_nameitem_name
Bread ButterCoffeeScrambled Eggs
Bread ButterCoffeeSausages
Bread ButterJuice – Choice of FruitSausages
Bread ButterJuice – Choice of FruitScrambled Eggs
Bread ButterTeaScrambled Eggs
Bread ButterTeaSausages
French ToastCoffeeSausages
French ToastCoffeeScrambled Eggs
French ToastJuice – Choice of FruitScrambled Eggs
French ToastJuice – Choice of FruitSausages
French ToastTeaSausages
French ToastTeaScrambled Eggs
PastaCoffeeScrambled Eggs
PastaCoffeeSausages
PastaJuice – Choice of FruitSausages
PastaJuice – Choice of FruitScrambled Eggs
PastaTeaScrambled Eggs
PastaTeaSausages

Thus, we can see how CROSS JOIN generates an exhaustive list of options combining the datasets of all the participating tables. This can be useful in stock taking at month-end, year-end or to generate a list of business options by a business application for planning and decision making.

Was this tutorial helpful?
YesNo
« Previous: SQL Server JOINS
SQL Server INNER JOIN :Next »

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server DROP VIEW
  • SQL Server Indexed View
  • Check view definition
  • SQL Server Rename View
  • SQL Server List Views

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • SQL Server Views
  • Terms of Use

Copyright © 2023 www.sqlservertutorial.org. All Rights Reserved.