You are here

SQL: CROSS JOIN

cross join

CROSS JOIN

  • Cross join combine every row from the left table with every row in the right table. This type of join is also called cartesian join.
  • It produce result set which is the number of rows in the first table multiplied by the number of rows in the second table.
  • This join can be used in situation where it is desired, to select all possible combinations of rows and columns from both tables.
  • Cross joins are useful for generating data for testing.
  • This join is not preferred as it may run for a very long time and produce a huge result set that may not be useful.
  • Syntax:  Select col1, col2,……, coln from <table1> CROSS JOIN <table2>;
  • If cross join contains the WHERE condition then it function like an Inner join.
  • Syntax:  Select col1, col2,……, coln from <table1> CROSS JOIN <table2> where <predicate>;

Example:

Table: persons(p_id is primary key)

p_id Firstname lastname city
1 Amisha patel Anand
2 Bina patel Baroda
3 Tina Doyle v.v.nagar
4 Riya patel Anand

Table: orders(o_id is primary key and p_id is foreign key)

o_id Orderno p_id
1 121 3
2 122 3
2 123 1
4 124 NULL

Retrieve all records from persons and orders table:
select p.p_id, p.firstname+’  ‘+p.lastname as [Name], p.city, od.orderno from persons as p  CROSS JOIN  orders as od order by p.p_id;
output:

p_id Name city orderno
1 Amisha patel Anand 121
1 Amisha patel Anand 122
1 Amisha patel Anand 123
1 Amisha patel Anand 124
2 Bina patel Baroda 121
2 Bina patel Baroda 122
2 Bina patel Baroda 123
2 Bina patel Baroda 124
3 Tina Doyle v.v.nagar 121
3 Tina Doyle v.v.nagar 122
3 Tina Doyle v.v.nagar 123
3 Tina Doyle v.v.nagar 124
4 Riya patel Anand 121
4 Riya patel Anand 122
4 Riya patel Anand 123
4 Riya patel Anand 124

Retrieve persons name that have place order from order table:
select p.firstname +’  ‘+p.lastname as [Name],od.orderno  from persons as p
CROSS JOIN orders as od where p.p_id=od.p_id ;
Output:

Name orderno
Tina Doyle 121
Tina Doyle 122
Amisha patel 123

Download Example [media-downloader media_id=”268″ texts=”Download Code”]

Keep Visiting TechwithR.com for more upcoming top technical articles and see my blog www.mag4info.com.

Detail course about DBA visit TechNet Consultancy .

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top