Alex Rivera | Logout

INNER JOIN vs multiple table names in "FROM"

Asked 2011-02-25T14:46:31.710
35

Possible Duplicate:
INNER JOIN versus WHERE clause — any difference?

What is the difference between an INNER JOIN query and an implicit join query (i.e. 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 the below 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?

-- Using implicit join (listing multiple tables)
SELECT s.description
FROM Documents d, Statuses s
WHERE d.statusId = s.id
      AND d.id = 9;

-- Using INNER JOIN
SELECT s.description
FROM Documents d
INNER JOIN Statuses s ON d.statusId = s.id
WHERE d.id = 9;
Edit
Report

1 Answer

3

The first one does a Cartesian product on all record within those two tables then filters by the where clause.

The second only joins on records that meet the requirements of your ON clause.

EDIT: As others have indicated, the optimization engine will take care of an attempt on a Cartesian product and will result in the same query more or less.

answered 2011-02-25T14:49:30.743

Your Answer