Database Join Performance Comparison

Question

I am on a project where much of the queries are performed by including multiple tables in the FROM clause. I know this is legal, but I have always used explicit JOINs instead.

For example, two tables (using SQL Server DDL)

CREATE TABLE Manufacturers(
    ManufacturerID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Name varchar(100))

CREATE TABLE Cars (
    ModelID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    ManufacturerID INT CONSTRAINT FOREIGN KEY FK_Manufacturer REFERENCES Manufacturers(ManufacturerID),
    ModelName VARCHAR(100))

If I want to find the models for GM, I could do either:

SELECT ModelName FROM Cars c, Manufacturers m WHERE c.ManufacturerID=m.ManufacturerID AND m.Name='General Motors'

or

SELECT ModelName FROM Cars c INNER JOIN Manufacturers m ON c.ManufacturerID=m.ManufacturerID WHERE m.Name='General Motors'

My question is this: does one form perform better than the other? Aside from how the tables are defined in Oracle vs SQL Server, does one form of join work better than the other in Oracle or SQL Server? What if you include more tables, say 3 or 4? Does that change the performance characteristics, assuming the queries are constructed to return an equivalent record set?

Answer 1

My question is this: does one form perform better than the other?

They should not. You can check your execution plans to be certain, but every RDBMS I've worked with generates the same plans for comma (ANSI-89) joins as they do for ANSI-92 explicit joins. (Note that comma joins didn't stop being ANSI SQL, it's just that ANSI-92 is where the explicit syntax first appeared.)

Aside from how the tables are defined in Oracle vs SQL Server, does one form of join work better than the other in Oracle or SQL Server?

As far as the server is concerned, no.

What if you include more tables, say 3 or 4? Does that change the performance characteristics, assuming the queries are constructed to return an equivalent record set?

It's possible. With comma joins, I'm not sure it's possible to control the JOIN order with parentheses like you can with explicit joins:

SELECT *
FROM Table1 t1
INNER JOIN (
    Table2 t2
    INNER JOIN Table3 t3
        t2.id = t3.id)
    ON t1.id = t2.id

This can affect overall query performance (for better or worse). I'm not sure how to accomplish the same level of control with comma joins, but I've never fully learned comma join syntax. I don't know if you can say Table1 t1, (Table2 t2, Table3 t3), but I don't believe you can. I think you'd have to use subqueries to do that.

The primary advantages of explicit joins are:

1. Easier to read. It makes it very clear which conditions are used with which join. You won't ever see Table1 t1, Table2 t2, Table3 t3 and then have to dig into the WHERE clause to figure out if one of those joins is an outer join. It also means the WHERE clause isn't stuffed full of all these join conditions you know you don't care about changing when you modify a query.
2. Easier to use outer joins. In the case of outer joins, you can even specify literal filter values in the outer table without having to handle nulls from the outer join.
3. Easier to reuse existing joins. If you just want to query from the same relations, you can just grab the FROM clause. You don't have to worry about what bits from the WHERE clause you want and what bits you don't.
4. Identical syntax across RDBMSs. When you spend all day switching between Oracle and SQL Server, the last thing you want to worry about is confusing + and *= to get your outer joins right.

All of the above make the explicit join syntax more maintainable, which is a very important factor for software.