SQL CROSS JOIN or Cartesian Join with Examples

What is SQL CROSS JOIN

The SQL CROSS JOIN is the join which results out all the possible combination of the rows of two tables. It means the number of rows in the first table multiplies by the number of rows in the second table. You can call this product as the cartesian product of the rows of both the tables.

SQL CROSS JOIN Image

All the rows of the first table combine with all the rows of the second table. If there are more rows in both the tables, the Cross join results out a large table.

How to Use SQL CROSS JOIN

Now, if you want to know how to use full outer join in SQL, you have to use the two tables and also the syntax given below. From these two tables, you have to make all the possible combinations for rows of these tables.

Syntax

SELECT column_name
FROM table1
CROSS JOIN table2
ON table1.column_name = table2.column_name;

SELECT column_name

FROM table1

CROSS JOIN table2

ON table1.column_name = table2.column_name;

The above syntax result out all the possibilities rows from both the tables.

Example of SQL RIGHT OUTER JOIN

Now, let us create the two tables for Purchaser and Seller as given below to understand the example of full outer join in SQL server.

Below are the examples that contains the statement and the output of the full outer join of the given tables.

SQL CROSS Join Example Using the Select Statement

There are two tables given below to show the join. The first table is Purchaser table and second is the Seller table.

The first table contains the list of sellers.

Table 1: Seller

Id	Seller_Name	Seller_Email
1001	Big Show	[email protected]
1002	Gem	[email protected]
1003	Matt	[email protected]

The second table contains the list of the purchasers.

Table 2: Purchaser

Purchaser_ID	Purchaser_Name	Plot_No	Service_Id
1	Sam	12	1001
2	Pill	13	1002
3	Don	14	1003
4	Brock	15	1004

Now, let’s combine these two tables using the example given below.

Example

SELECT Service_Id, Seller_Name, Purchaser_Name, Plot_No 
FROM Purchaser 
CROSS JOIN Seller;

SELECT Service_Id, Seller_Name, Purchaser_Name, Plot_No

FROM Purchaser

CROSS JOIN Seller;

Output

Service_Id	Seller_Name	Purchaser_Name	Plot_No
1001	Big Show	Sam	12
1001	Big Show	Pill	13
1001	Big Show	Don	14
1001	Big Show	Brock	15
1002	Gem	Sam	12
1002	Gem	Pill	13
1002	Gem	Don	14
1002	Gem	Brock	15
1003	Matt	Sam	12
1003	Matt	Pill	13
1003	Matt	Don	14
1003	Matt	Brock	15

You must also read.

Reference
Cross Join Doc

SQL Tutorial

SQL CROSS JOIN

What is SQL CROSS JOIN

How to Use SQL CROSS JOIN

Example of SQL RIGHT OUTER JOIN

SQL CROSS Join Example Using the Select Statement

Related Posts: