SQL cross Join with Examples

A SQL cross join is a join that produces a cartesian product combine one or two tables.

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 cartesian in SQL.

table 1- A,B,C
table 2- X,Y,Z

SQL CROSS JOIN

As a result of cross join:

(A,X),(A,Y),(A,Z),
(B,X),(B,Y),(B,Z),
(C,X),(C,Y),(C,Z);

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:

SQL CROSS JOIN Syntax:


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');


SELECT * 
FROM 
animals;

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');


SELECT *
FROM 
hungry;

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 asterisk '*' 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;