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_idFirstnamelastnamecity
1AmishapatelAnand
2BinapatelBaroda
3TinaDoylev.v.nagar
4RiyapatelAnand

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

o_idOrdernop_id
11213
21223
21231
4124NULL

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_idNamecityorderno
1Amisha patelAnand121
1Amisha patelAnand122
1Amisha patelAnand123
1Amisha patelAnand124
2Bina patelBaroda121
2Bina patelBaroda122
2Bina patelBaroda123
2Bina patelBaroda124
3Tina Doylev.v.nagar121
3Tina Doylev.v.nagar122
3Tina Doylev.v.nagar123
3Tina Doylev.v.nagar124
4Riya patelAnand121
4Riya patelAnand122
4Riya patelAnand123
4Riya patelAnand124

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:

Nameorderno
Tina Doyle121
Tina Doyle122
Amisha patel123

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