SQL cross Join with Examples

3 min readJan 28, 2022

In a mathematical operation that returns multiple sets and pairs.

Let’s think we have two table m and n.and have three rows A, B, C, and the second table have x,y,z. ok, Now we are going to use the mathematical method of in SQL.

Now, you will little bit understand how the SQL cross join works. After the performing of the result of the cross join between the two tables that illustrate the cartesian product has 9 rows.

Let’s see the syntax of SQL cross join operation:

SELECT 
column1,column2.... 
FROM 
table1,table 2;

SQL CROSS JOIN Examples:

Here, we are create a two table Animals and Hungry table for demonstrate purpores and how it’s works on cross Join.

Animals Table:

CREATE TABLE animals
( 
id int primary key identity(1,1), 
name varchar(30) not null 
);

Insert data into animals Table:

Insert into animals (name) values('deer'); 
Insert into animals (name) values('cow'); 
Insert into animals (name) values('goat');

Hungry Table:

CREATE TABLE hungry 
( 
id int primary key identity(1,1), 
eat varchar(100) not null
 );

Insert data into hungry Table:

Insert into hungry(eat) values('eat grass'); 
Insert into hungry(eat) values('eat carrot');

Cross Join Examples:

Here, I just created three types of examples followed by SQL cross join

SELECT 
name ,eat 
FROM 
animals,hungry;

we have used the select statement command with the to retrieve all the columns in the animals and hungry tables. And then we are going to use SQL cross join operations on the tables. 3 records after the cross join, we will get 6 rows. It will not like inner join, left join, right join. cross join is a unique style to performing the table.

SELECT * 
FROM 
animals 
CROSS JOIN 
hungry;SELECT 
animals.name,hungry.eat 
FROM animals 
CROSS JOIN 
hungry ;

SQL Cross Join and SQL Union Operator:

Here, animals and hungry tables are using to get by sql union operator and let’s see what will happens. Union operator is used to combine the result-set and examples below.

SELECT 
name ,eat 
FROM 
animals,hungry 
UNION 
SELECT 
name ,eat 
FROM 
animals,hungry;

Originally published at https://www.dailyaspirants.com on January 28, 2022.