Cross Join In SQL

Hey guys hope you are enjoying my articles. This one is all about cross outer join.

Definition
  Cross Join produces Cartesian product of the tables which are participating in the Join queries that’s why it’s also known by the name of Cartesian Join.

Generally we use CROSS JOIN when there is no relationship between participating tables.

Syntax

SELECT  column names  FROM  table1  CROSS JOIN  table 2  WHERE (expression)  ORDER BY  column names;

Or   

SELECT  column names  FROM  table1,  table 2  WHERE (expression)  ORDER BY  column names

In the first line we have our Select statement where you can specify the list of columns from both the participating tables. 

In the second line we have our tables and the CROSS JOIN clause. Here you can write either Cross Join or just put a comma in between the names of both tables. 

Also with cross join we do not have any ON or USING join condition. But you may, however, specify a WHERE and ORDER BY clause.

Note if you are using ORDER BY clause then make sure it must be the last statement of your SQL query.

Example:

I’ll be using the same table which we have been using in all our JOIN tutorials so far. These are the emp and dept tables.
(For tables you can refer to Table for SQL Joins tutorial)

Query 1: CROSS JOIN

SELECT  emp_name, dept_name  FROM  emp  CROSS JOIN  dept;

This query is fairly simple; we are selecting emp name and dept name from emp and dept tables respectively. 
On executing this query, the first record of emp name column of emp table that is – Steve, gets paired with all the rows of the second table dept. Similarly second record Nancy gets paired with all the rows of dept table and so on. 
Since this cross join produces a Cartesian product therefore the total number of rows in the result will be equal to total number of rows in table 1 multiplied by total number of rows in table 2. 
Since in our case we have total 5 rows in each table thus total number of rows in our result is 25.

Query 2: CROSS JOIN with WHERE clause.

Say you want to see only those records where dept name is IT. 

SELECT  emp_name,dept_name  FROM  emp  CROSS JOIN  dept  WHERE dept_name = ‘IT’;

On execution this query will return all the emp name which are corresponding to Department name IT

Query 3 : Cross Join with Order By clause

Similarly you can use ORDER BY clause if you want to sort the result returned by your query in Ascending or Descending order.

NOTE: Here ORDER BY clause must be the last statement of a QUERY.

ORDER BY clause by default sorts the results in ascending order. But if you want to arrange the result in Descending order then you have to specify it by using DESCENDING or DESC keyword with the ORDER BY clause.

SELECT  emp_name,dept_name  FROM  emp  CROSS JOIN  dept  WHERE dept_name = ‘IT’  ORDER BY emp_name;

And if you want to reverse the order meaning if you want to sort the result in Descending order, then you just need to add DESC or DESCENDING keyword after the column name of ORDER BY clause

SELECT  emp_name,dept_name  FROM  emp  CROSS JOIN  dept   WHERE dept_name = ‘IT’  ORDER BY emp_name  DESC;

In this tutorial I tried to sum up all the concepts of CROSS JOIN hope you enjoyed reading.
Do share this article with your friends on social network. You can also watch my video for the same. Thanks & have a great day!