Alex Rivera | Logout

Is there something wrong with joins that don't use the JOIN keyword in SQL or MySQL?

Asked 2008-09-24T18:36:12.320
47

When I started writing database queries I didn't know the JOIN keyword yet and naturally I just extended what I already knew and wrote queries like this:

SELECT a.someRow, b.someRow 
FROM tableA AS a, tableB AS b 
WHERE a.ID=b.ID AND b.ID= $someVar

Now that I know that this is the same as an INNER JOIN I find all these queries in my code and ask myself if I should rewrite them. Is there something smelly about them or are they just fine?

My answer summary: There is nothing wrong with this query BUT using the keywords will most probably make the code more readable/maintainable.

My conclusion: I will not change my old queries but I will correct my writing style and use the keywords in the future.

sql mysql join
Edit
Report

1 Answer

4

The one problem that can arise is when you try to mix the old "comma-style" join with SQL-92 joins in the same query, for example if you need one inner join and another outer join.

SELECT *
FROM table1 AS a, table2 AS b
 LEFT OUTER JOIN table3 AS c ON a.column1 = c.column1
WHERE a.column2 = b.column2;

The problem is that recent SQL standards say that the JOIN is evaluated before the comma-join. So the reference to "a" in the ON clause gives an error, because the correlation name hasn't been defined yet as that ON clause is being evaluated. This is a very confusing error to get.

The solution is to not mix the two styles of joins. You can continue to use comma-style in your old code, but if you write a new query, convert all the joins to SQL-92 style.

SELECT *
FROM table1 AS a
 INNER JOIN table2 AS b ON a.column2 = b.column2
 LEFT OUTER JOIN table3 AS c ON a.column1 = c.column1;
answered 2008-09-24T19:49:26.247

Your Answer