MySQL INNER JOIN With comma operator

My Sql Inner JoinHere 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.

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 a, b

– inner join with WHERE-clause
SELECT *
FROM a, b
WHERE a. = b.

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

Further information
You have seen how to write inner joins with the comma operator. From My 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).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s