MySQL Cross Join

MySQL Cross Join returns the Cartesian product of both tables. The Cross Join does not require any common column to join two tables. The Cartesian product means the Number of Rows present in Table 1 Multiplied by the Number of Rows present in Table 2.

MySQL Cross Join Syntax

The basic syntax of the Cross Join is as shown below.

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 below screenshot, you can easily understand that the MySQL Cross join displays the Cartesian product of two tables. It means that every record in Table A is combined with every record in Table B

For this Cross Join example, we will use Employ and Department tables present in our company Database. Data present in the employ is:

Data present in the MySQL Department is:

MySQL Cross Join Examples

The following is the list of ways that we can use this to combine two or get information (records) from two or more tables.

MySQL Cross Join using Select *

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

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 multiplied by 9 rows in the Department table.

NOTE: The DeptID column is repeated twice, which might annoy the end user. To avoid unwanted columns, I suggest you select individual column names. Please avoid the SELECT * Statement in it.

Select Few Columns

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

USE company;
SELECT First_Name, Last_Name, Education, 
       DepartmentName, Standard_Salary,
       Yearly_Income, Sales
FROM employ
	CROSS JOIN department;

Ambiguous Columns in MySQL Cross Join

The above query works perfectly as long as the column names from both tables (employ and Department) differ. What happens if they have the same column names in both tables? Well, with the above-specified approach, you end up in a mess. Let us see how to resolve the issue.

Let me show you one practical example of cross Join ambiguous column. As you can see, we are using the same query. But, we added DepID from the department table as an additional column.

USE company;
SELECT First_Name, Last_Name, Education, 
	DeptID, DepartmentName, Standard_Salary,
        Yearly_Income, Sales
FROM employ
	CROSS JOIN department;

As you can see from the screenshot below, it is throwing an error: Ambiguous column DeptID. It is because the DeptID column is present in both tables, and the Cross Join query doesn’t know which column you are asking it to display.

To resolve this kind of issue, you must regularly use the table name before the column name. The following query uses the ALIAS table name before the column names. By this approach, we can inform the query that we are looking for DepID belonging to the department table.

We can simply write the above query as:

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;

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

MySQL Cross Join Where Clause Example

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

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;

Command prompt Example

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

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;