MySQL Cross Join returns the Cartesian product of both the tables. The Cross Join in MySQL does not require any common column to join two tables. The Cartesian product means Number of Rows present in Table 1 Multiplied by Number of Rows present in Table 2.
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 MySQL 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
For this MySQL Cross Join example, we are going to use Employ, and Department tables present in our company Database.
Data present in employ table is:
Data present in the MySQL Department Table is:
MySQL Cross Join Examples
The following are the list of ways that we can use this MySQL Cross Join to combine two tables or get information (records) from two or more tables.
MySQL Cross Join – Using Select *
The following Query 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 repeated twice, which might be annoying to the end-user. To avoid unwanted columns, I suggest you 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
The above Cross Join query works perfectly as long as the column names from both tables (employ, and Department) are different. What happens if they had the same column names in both the 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 MySQL 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.
-- 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;
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 the tables, and MySQL query doesn’t know which column you are asking it to display.
To resolve this kind of issue, you regularly have to use the table name before the column name. The following Cross Join query is using 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:
-- 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 show you, How to use Where clause (to apply filter) along with the Cross Join. I suggest you 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
Cross Join Example – Command prompt
Let me show you how to write Cross Join query using the command prompt.
In this example, we show you how to use Order By clause along with Cross Join. I suggest you refer to 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