Here you find information about writing inner joins with the comma operator. It’s the most basic way to combine (join) two tables. There is an alternative syntax that can be used, because in MySQL you can write inner joins in two different ways. Another popular way is it to use the INNER JOIN command or synonymous keywords like CROSS JOIN and JOIN. Please make sure to read our dedicated documentation for more information when you understand the comma operator syntax.
The following examples are equivalent to the INNER JOIN examples, to make it easy to compare them. The first example builds the Cartesian product of two tables: Every row in the left table is combined with every row in the right table. In ANSI SQL, this is called a cross join. MySQL however doesn’t distinguish between inner joins and cross joins.
— inner join without a condition: a cross join
SELECT *
FROM <firstTable> a, <anotherTable> b
— inner join with WHERE-clause
SELECT *
FROM <firstTable> a, <anotherTable> b
WHERE a.<columnName> = b.<columnName>
When you write inner joins using the comma operator, there is only one way to specify the join condition: with a WHERE-clause.
Basics
An inner join combines all matching rows from two related tables. Two rows match if they are related, for example because they share a common column. When you write an inner join with the comma operator, the join condition which reflects a relationship between tables is added as a WHERE clause. You can use inner joins to SELECT, UPDATE or DELETE data that is stored within MySQL tables. Here are two more examples which are equivalent to the INNER JOIN examples:
SELECT *
FROM tableA a, tableB b
WHERE a.someColumn = b.otherColumn
SELECT *
FROM tableA a, tableB b
WHERE a.columnName = b.columnName
Further information
You have seen how to write inner joins with the comma operator. From our point of view, it’s “OK” to use this feature. However, using the alternative syntax makes it easier to read your joins. It’s considered as a good behavior to write statements where you can directly see if it’s an INNER JOIN or a CROSS JOIN (the Cartesian product of two tables).