SQL - Difference between WHERE with two tables and a JOIN

Question

Here are these two SQL queries:

SELECT `o`.`date`, `i`.`description`, `o`.`quantity`
FROM `orders` AS `o`, `items` AS `i`
WHERE `i`.`itemID` = `o`.`itemID`;

and

SELECT `o`.`date`, `i`.`description`, `o`.`quantity`
FROM `orders` AS `o`
JOIN `items` AS `i`
ON `i`.`itemID` = `o`.`itemID`;

I get one result less with the first one. Can someone explain the difference between the two?

Possible duplicat http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins — Vucko, Oct 13 '13 at 12:54
These queries are functionally identical. Could you provide some sample data or a fiddle to reproduce the issue? — geomagas, Oct 13 '13 at 12:56
They are functionally equivalent. I have never experienced a situation where the same results aren't returned. FYI: The first style is often referred to as 'theta' style. The 2nd is the ANSI style — AgRizzo, Oct 13 '13 at 12:59
Google it. I learned about the difference in the Pro MySQL book by Jay Pipes and Michael Kruckenberg — AgRizzo, Oct 13 '13 at 13:02
Perhaps you have `LEFT JOIN` in the second query? Otherwise, this shouldn't happen and the two queries should return the same results. — ypercubeᵀᴹ, Oct 13 '13 at 13:22

score 0 · Answer 1 · edited May 23 '17 at 10:31

0

Here is a usefull discussion around what you want to do

SQL left join vs multiple tables on FROM line?

Hope it helps !

edited May 23 '17 at 10:31

Community

1
1

answered Oct 13 '13 at 13:08

S.Thiongane

6,883
3
37
52

score 0 · Accepted Answer · answered Oct 13 '13 at 13:25

They are functionally equivalent because MySQL optimizer rewrites theta join into an ANSI JOIN (atleast MySQL 5.6 does)

You can check this with EXPLAIN EXTENDED your_query ; SHOW WARNINGS;

theta style join

SELECT `o`.`date`, `i`.`description`, `o`.`quantity`
FROM `orders` AS `o`, `items` AS `i`
WHERE `i`.`itemID` = `o`.`itemID`;

ANSI JOIN

SELECT `o`.`date`, `i`.`description`, `o`.`quantity`
FROM `orders` AS `o`
JOIN `items` AS `i`
ON `i`.`itemID` = `o`.`itemID`;

The only real difference is readability the theta style makes it easier to mis that it is an INNER JOIN, so make an habit out off it to write the ANSI JOIN

SQL - Difference between WHERE with two tables and a JOIN

2 Answers2