The Difference Between Comma and Join in SQL

What's the difference between comma separated joins and join on syntax in MySQL?

There is no difference at all.

First representation makes query more readable and makes it look very clear as to which join corresponds to which condition.

Difference between INNER JOIN and , COMMA

One difference is that the first option hides the intent by expressing the join condition in the where clause.

The second option, where the join condition is written out is more clear for the user reading the query. It shows the exact intent of the query.

As far as performance or any other difference, there shouldn't be any. Both queries should return the exact same result and perform the same under most RDBMS.

And as @Tim Biegeleisen says in the comments:

the comma version is deprecated as of the ANSI-92 SQL standard

the difference between comma and join in sql

There's no real difference WHEN executing them, but there is a readability, consistency and error mitigating issue at work:

Imagine you had 4 tables
If you used the old fashioned way of doing an INNER JOIN, you would end up with:

SELECT col1, col2
FROM tab1, tab2, tab3,tab4
WHERE tab1.id=tab2.tab1_id
AND tab4.id = tab2.tab3_id
AND tab4.id = tab3.tab4_id;

Using explicit INNER JOINS it would be:

SELECT col1, col2
FROM tab1
INNER JOIN tab2 ON tab1.id = tab2.tab1_id
INNER JOIN tab3 ON tab3.id = tab2.tab3_id
INNER JOIN tab4 ON tab4.id = tab3.tab4_id;

The latter shows you right in front of the table exactly what is it JOINing with. It has improved readability, and much less error prone, since it's harder to forget to put the ON clause, than to add another AND in WHERE or adding a wrong condition altogether (like i did in the query above :).

Additionally, if you are doing other types of JOINS, using the explicit way of writing them, you just need to change the INNER to something else, and the code is consistently constructed.

What is the difference between using a cross join and putting a comma between the two tables?

They return the same results because they are semantically identical. This:

select * 
  from A, B

...is (wince) ANSI-89 syntax. Without a WHERE clause to link the tables together, the result is a cartesian product. Which is exactly what alternative provides as well:

    select * 
      from A 
cross join B

...but the CROSS JOIN is ANSI-92 syntax.

About Performance

There's no performance difference between them.

Why Use ANSI-92?

The reason to use ANSI-92 syntax is for OUTER JOIN support (IE: LEFT, FULL, RIGHT)--ANSI-89 syntax doesn't have any, so many databases implemented their own (which doesn't port to any other databases). IE: Oracle's (+), SQL Server's =*

what is the difference between ,(comma) and and in group by?

Comma separates expressions. group by subject, semester is creating a separate group for each distinct pair of values. I think that's what you want.

The example using AND is only one boolean expression, with only two distinct groups, one for true and one for false. AND is a boolean operator. It evaluates its two operands and then gives the boolean conjunction between them.

So it evaluates both subject and semester as booleans only, and then if both are true, then the whole boolean expression is true. That's the way a boolean AND works.

MySQL treats booleans as the same thing as integer values 1 for true and 0 for false. So it will evaluate subject and semester as numbers. This may give unexpected results.

You should use the comma if you want to group by two columns.

Cross Apply vs comma Join

These "comma joins" are, as SqlZim pointed out, an old-style way of joining tables called implicit join.

The join condition for this kind of join is in the where clause - for example -

SELECT *
FROM a, b
WHERE a.Id = b.aId

Is the same as

SELECT *
FROM a
INNER JOIN b ON a.Id = b.aId

For what I hope is obvious reasons, implicit (or old style) joins are frowned upon - as you can probably easily imagine, when you need to join more than two tables it becomes very unreadable compared to explicit joins.

Cross apply, however, is not a join at all.
What Cross apply means is you execute whatever is on the right side of the cross apply operator for each row on the left side.

A more accurate explicit join for the implicit join statement you posted would be

SELECT *
FROM #name
CROSS JOIN #letter

Another option would be to use an inner join with a join condition that will always evaluate to true - like this:

SELECT *
FROM #name
INNER JOIN #letter ON 1=1

Difference between these two joining table approaches?

Other than syntax, for the small snippet, they work exactly the same. But if at all possible, always write new queries using ANSI-JOINs.

As for semantically, the comma notation is used to produce a CARTESIAN product between two tables, which means produce a matrix of all records from table A with all records from table B, so two tables with 4 and 6 records respectively produces 24 records. Using the WHERE clause, you can then pick the rows you actually want from this cartesian product. However, MySQL doesn't actually follow through and make this huge matrix, but semantically this is what it means.

A JOIN syntax is the ANSI standard that more clearly defines how tables interact. By putting the ON clause next to the JOIN, it makes it clear what links the two tables together.

Functionally, they will perform the same for your two queries. The difference comes in when you start using other [OUTER] JOIN types.

For MySQL specifically, comma-notation does have one difference

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

However, it would not be wise to bank on this difference.

Is a query with table separated by comma a cross join query?

It would be a cross join if there wasn't a WHERE clause relating the two tables. In this case it's functionally equivalent to an inner join (matching records by id_rel and id)

It's an older syntax for joining tables that is still supported in most systems, but JOIN syntax is largely preferred.

---

Related Topics