SQL CROSS JOIN

The CROSS JOIN which is also known as Cartesian Join is used to return the set of records by the Cartesian product of the joined tables.
  • Syntax of CROSS JOIN –

SELECT Table1.column1,Table2.column1,Table3.column1,...,TableN.columnN FROM Table1,Table2,...;

Example – Consider these two tables the Product table and the Manager table. Demo Table – 

Product Table

Category Region City Country Customer_ID Customer_name
Office Supplies Central Chicago United States SM-20950 Suzanne McNair
Technology East New York City United States AH-10465 Amy Hunt
Office Supplies East New York City United States AH-10465 Amy Hunt
Office Supplies East Dover United States EP-13915 Emily Phan
Office Supplies East Dover United States EP-13915 Emily Phan
Technology West Aurora United States TP-21565 Tracy Poddar
Office Supplies West Aurora United States TP-21565 Tracy Poddar
Office Supplies East Long Beach United States AR-10825 Anthony Rawles
Office Supplies West Pasadena United States HA-14920 Helen Andreada
Furniture West Pasadena United States HA-14920 Helen Andreada

Manager Table

Region Regional Manager
West Sadie Pawthorne
East Chuck Magee
Central Roxanne Rodriguez
South Fred Suzuki
Now the below query will create a Cartesian Product in between both the tables –

SELECT A.Category,A.City,A.[Customer Name],B.Region,B.[Regional Manager] FROM [dbo].[Products] AS A CROSS JOIN [dbo].[Manager] AS B ;

This query will return the below output –
Category City Country Customer_ID Customer_name Region Regional Manager
Office Supplies Chicago United States SM-20950 Suzanne McNair West Sadie Pawthorne
Technology New York City United States AH-10465 Amy Hunt West Sadie Pawthorne
Office Supplies New York City United States AH-10465 Amy Hunt West Sadie Pawthorne
Office Supplies Dover United States EP-13915 Emily Phan West Sadie Pawthorne
Office Supplies Dover United States EP-13915 Emily Phan West Sadie Pawthorne
Technology Aurora United States TP-21565 Tracy Poddar West Sadie Pawthorne
Office Supplies Aurora United States TP-21565 Tracy Poddar West Sadie Pawthorne
Office Supplies Long Beach United States AR-10825 Anthony Rawles West Sadie Pawthorne
Office Supplies Pasadena United States HA-14920 Helen Andreada West Sadie Pawthorne
Furniture Pasadena United States HA-14920 Helen Andreada West Sadie Pawthorne

Other SQL topics to check out:

Follow us on TwitterFacebookLinkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau. Automate data analysis pipeline and create report ready dashboards If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.