MySQL Cross Join

MySQL Cross Join returns the Cartesian product of both the tables. Cartesian product means Number of Rows present in Table 1 Multiplied by Number of Rows present in Table 2.

TIP: Cross Join in MySQL does not require any common column to join two table.

SQL Cross Join Syntax

The basic syntax of the Cross Join in MySQL is as shown below:

-- MySQL Cross Join Syntax
SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
 CROSS JOIN
     Table2 

--OR We can Simply Write it as
SELECT Table1. Column(s), Table2. Column(s),
FROM Table1, Table2

Let us see the visual representation of the Cross join for better understanding.

From the above screenshot you can easily understand that, Cross join displays the Cartesian product of two tables. It means, every record in Table A is combining with every record Table B

In this article we will show you, How to write MySQL Cross Join with example. For this, we are going to use Employ, and Department tables present in our company Database.

Data present in employ table is:

Data present in the Department Table is:

MySQL Cross Join Examples

Following are the list of ways that we can use this Cross Join to combine two tables, or get information (records) from two or more tables.

MySQL Cross Join – Using Select *

The following Query will display all the columns present in employ, and Department tables

-- Cross Join in MySQL Example
USE company;
SELECT * FROM employ
   CROSS JOIN department;

If you observe the below screenshot, It is displaying 135 records. It means 15 rows from the Employ multiplies by 9 rows in the Department table

NOTE: The DeptID column was repeated twice, which might be annoying to end-user. In order to avoid unwanted columns, I suggest you to select individual column names. Please avoid SELECT * Statement in Cross Join

MySQL Cross Join – Select Few Columns

As we said before, please place the required columns after the SELECT Statement to avoid unwanted columns

-- Cross Join in MySQL Example
USE company;
SELECT First_Name, Last_Name, Education, 
       DepartmentName, Standard_Salary,
       Yearly_Income, Sales
FROM employ
	CROSS JOIN department;

OUTPUT

Ambiguous Columns in MySQL Cross Join

Above query will work perfectly as long as the column names from both tables (employ, and Department) are different like above. What happens if they had same column names in both the tables? Well, with the above specified approach, you will end up in a mess. Let us see how to resolve the issue.

Let me show you one practical example. As you can see we are using the same query but we added DepID from department table as an additional column.

-- Cross Join in MySQL Example
USE company;
SELECT First_Name, Last_Name, Education, 
	DeptID, DepartmentName, Standard_Salary,
        Yearly_Income, Sales
FROM employ
	CROSS JOIN department;

You can see from the below screenshot, it is throwing an error : Ambiguous column DeptID. This is because, DeptID column is present in both the tables, and MySQL query don’t know which column you are asking it to display.

To resolve these kind of issues you always have to use table name before the column name. The following query is using ALIAS table name before the column names. By this approach, we can inform the query that we are looking for DepID belonging to department table.

We can simply write the above query as:

-- Cross Join in MySQL Example
USE company;
SELECT  emp.First_Name,	emp.Last_Name, emp.Education, 
	    dept.DeptID,dept.DepartmentName, dept.Standard_Salary,
        emp.Yearly_Income, emp.Sales
FROM employ AS emp
	CROSS JOIN department AS dept;

OUTPUT

NOTE: It is always best practice to use the table name before the Column name in Cross Joins. For example, SELECT employ.First_Name

MySQL Cross Join – Where Clause Example

In this example we will show you, How to use Where clause (to apply filter) along with the Cross Join. I suggest you to refer MySQL Where Clause article.

-- Cross Join in MySQL Example
USE company;
SELECT  emp.First_Name,	emp.Last_Name, emp.Education, 
	dept.DepartmentName, dept.Standard_Salary,
        emp.Yearly_Income, emp.Sales
FROM employ AS emp
	CROSS JOIN department AS dept
WHERE Yearly_Income > 1000000;

OUTPUT

MySQL Cross Join Example – Command prompt

Let me show you, How to write Cross Join query using the command prompt. In this example we will show you, How to use Order By clause along with the Cross Join. I suggest you to refer MySQL Order By article.

-- Cross Join in MySQL Example
USE company;
SELECT  emp.First_Name, 
	emp.Last_Name, 
        emp.Education, 
	dept.DepartmentName, dept.Standard_Salary,
        emp.Yearly_Income, emp.Sales
FROM employ AS emp
	CROSS JOIN department AS dept
ORDER BY First_Name, Last_Name;

OUTPUT

Thank You for Visiting Our Blog