CODEWITHSUNDEEP
phpmysqljoinleft-join
Vael Victus
Vael Victus

Reputation: 4122

LEFT JOIN or just select additional tables?

I'm wondering about the speed between explicitly saying LEFT JOIN, and then simply joining the values on each other. So here, we have:

SELECT example.id FROM example
LEFT JOIN finals ON example.id = finals.account_id";

vs.

SELECT example.id, finals.account_id
FROM example, finals
WHERE example.id = finals.account_id

I'm nearly certain the first is faster, but I cannot find an answer to suggest that. (I'm not actually even sure what to 'call' the second one, I just call it a soft join)

Upvotes: 0

Views: 234

Answers (4)

Explosion Pills
Explosion Pills

Reputation: 191749

You can't really compare the speed of the two because they will give different results. The second query requires an entry on both tables. The first query only requires an entry on the first table.

The second query is using a CROSS JOIN (no difference from JOIN in mysql .. comma is an alias for JOIN with no ON clause).

Upvotes: 0

Basic
Basic

Reputation: 26766

The second is an INNER JOIN (the default) and it's less a question of speed than output - an inner join will only return where the specified field exists in both tables.

eg if there was an example record #3 but no finals record with an account_id of 3, neither record would be shown

A LEFT JOIN would list records from example even where there's no matching finals record - The missing fields would be set to NULL

Upvotes: 2

Dylan Smith
Dylan Smith

Reputation: 22245

This second example does an INNER JOIN and will return potentially different results.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838216

There is a sematic difference between those queries.

The first join is an OUTER JOIN that includes all rows from the left table. The second is an INNER JOIN, and will not include rows where the match fails.

If you had written JOIN instead of LEFT JOIN the two queries would be identical, except the first syntax is preferred for readability and maintainability.

I'm not actually even sure what to 'call' the second one, I just call it a soft join)

The second syntax uses what MySQL calls the "comma operator". It is sometimes called an implicit join. It's also called an ANSI-89 join.

Upvotes: 2

Related Questions