What is cross join in SQL

Basic course SQL


This is largely meaningless in practice - we'll see why in a moment - it is only mentioned here for the sake of completeness.

We take our example from Chapter 2.3 again. There we tried to remove the line. The instruction then read:

SELECT first name, name, department name FROM tbl_department, tbl_employee

That's exactly what you call one. Each data record in one table is linked to each data record in the other table - everyoneEmployee is assigned all departments of the whole table. This is also called that Cross product (cross = English: Kreuz) of two tables - which in this, as well as in many other practical applications, delivers a completely nonsensical statement in special cases, but has its justification.

Let's leave out the comma here and instead write the keyword!

SELECT first name, name, department name FROM tbl_department CROSS JOIN tbl_employees

As you can see, we get the same result.

Try the queries in phpMyAdmin out. Also, experiment with the different JOIN types. Always take a critical look at the result set. A carelessly worded SQL query can lead to undesirable results.