MySQL INNER JOIN Tutorial: the comma operator

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.

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). Also see:

This entry was posted in Join Tutorials and tagged , , , , . Bookmark the permalink.

5 Responses to MySQL INNER JOIN Tutorial: the comma operator

  1. Awesome post! I like it a lot!

  2. After research a number of of the weblog posts on your web site now, and I actually like your manner of blogging. I bookmarked it to my bookmark web site listing and will be checking again soon. Pls take a look at my web site as properly and let me know what you think.

  3. roopali says:

    really awesome…. i google alot but was not getting exactly what i want.

  4. Adrian says:

    Hi,

    Great tutorials…

    I have a baffling issue here. Maybe you can shed some light on this?

    I’m dynamically assembling a SQL join statement and the result is this:

    SELECT SQL_CALC_FOUND_ROWS k_services.id, service_status, service_tourno, service_date, service_cxlDate, service_difficultPeriod, service_priority, service_currency, service_key_so, service_price_so, service_key_ok, service_price_cfm, service_supplement
    FROM k_services
    INNER JOIN k_remarks ON k_remarks.remark_service = k_services.id
    WHERE k_services.service_market = 11 AND k_remarks.remark_type = 9
    LIMIT 0, 25

    On the the server (Linux server with PHP5 and MySQL5) I get an error: Unknown column ‘k_remarks.remark_type’ in ‘where clause’
    If I execute the very same query in SQLyog, it executes fine with the desired/expected result.

    Any ideas?

    • jbrinkmann says:

      Hi. Sound like a weird issue. Can you share the SQL Create statement and a few (sample) rows? Would love to help you out debugging.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>