Categories
sql

INNER JOIN vs multiple table names in “FROM”

Possible Duplicate:

INNER JOIN versus WHERE clause — any difference?

What is the difference between an INNER JOIN query and an implicit join query (listing multiple tables after the FROM keyword)? For example:

Given the following two tables:

CREATE TABLE Statuses(
id INT PRIMARY KEY,
description VARCHAR(50)
);
INSERT INTO Statuses VALUES (1, 'status');
CREATE TABLE Documents(
id INT PRIMARY KEY,
statusId INT REFERENCES Statuses(id)
);
INSERT INTO Documents VALUES (9, 1);

What is the difference between these two SQL queries? From the testing I’ve done, they return the same result. Do they do the same thing? Are there situations where they will return different result sets?

SELECT s.description FROM Documents d, Statuses s WHERE d.statusId = s.id AND d.id = 9;
SELECT s.description FROM Documents d INNER JOIN Statuses s ON d.statusId = s.id WHERE d.id = 9;

There is no reason to ever use an implicit join (the one with the commas). Yes for inner joins it will return the same results. However, it is subject to inadvertent cross joins especially in complex queries and it is harder for maintenance because the left/right outer join syntax (deprecated in SQL Server, where it doesn’t work correctly right now anyway) differs from vendor to vendor. Since you shouldn’t mix implicit and explict joins in the same query (you can get wrong results), needing to change something to a left join means rewriting the entire query.