Difference of "FROM a LEFT JOIN b" vs. "FROM a, b"

Question

Do the queries do the same? What is the standard?

Will I lose performance if I change one of these ways of write the query?

Query 1

   SELECT a.*, b.id AS b_id
     FROM table_a AS a
LEFT JOIN table_b AS b
       ON a.id = b.id

Query 2

   SELECT a.*, b.id AS b_id
     FROM table_a a, table_b b
    WHERE a.id = b.id

What happens when you try them? (Doing so will answer your first question, and will probably answer your second one as well.) — Ken White, Nov 06 '12 at 02:34

score 5 · Accepted Answer · edited May 23 '17 at 12:27

They return different results.

A LEFT JOIN statement will return rows even if there is no associated records in table_b that match table_a id. So it will return all rows in table_a, paired with EITHER a matching row in table_a OR a blank/null table_b row (if for that row in table_a there isn't any matching row in table_b).

The second query is a shortcut for an INNER JOIN. This query will ONLY exclusively return rows that match the condition a.id = b.id. The second query can also be written as:

SELECT a.*, b.id AS b_id
FROM table_a a
INNER JOIN table_b b 
ON a.id = b.id

To answer your performance question, please see the answers on a related SO thread here.

Thanks for explanation! Never thought that the 2nd mode of writing was an abbreviation of INNER JOIN. — Gabriel Santos, Nov 06 '12 at 02:45

Difference of "FROM a LEFT JOIN b" vs. "FROM a, b"

1 Answers1

Linked