Subject Joins, left joins, and plans Rick Debay 2005-10-11T17:04:30Z
Given three tables [A,B,C] and two join tables [A2B,B2C] and the fact
that rows in table C are optional, these two queries should be
equivalent.

SELECT
*
FROM
A JOIN A2B ON A.ID = A2B.A
JOIN B ON B.ID = A2B.B
LEFT JOIN B2C ON B.ID = B2C.B
LEFT JOIN C ON C.ID = B2C.C

Resulting plan:
PLAN JOIN (JOIN (JOIN (B NATURAL,A2B INDEX (FK_A2B_B),A INDEX
(PK_A)),B2C INDEX (FK_B2C_B)),C INDEX (PK_C))

SELECT
*
FROM
A JOIN A2B ON A.ID = A2B.A
JOIN B ON B.ID = A2B.B
LEFT JOIN (
B2C JOIN C ON C.ID = B2C.C
) ON B.ID = B2C.B

Resulting plan:
PLAN JOIN (JOIN (B NATURAL,A2B INDEX (FK_A2B_B),A INDEX (PK_A)),JOIN (C
NATURAL,B2C INDEX (PK_B2C)))

The second is more explicit as it states that rows in the join table B2C
can't exist without a corresponding row in C. I would expect to be a
more efficient query as well.
As you can see, the plans I get from the optimizer don't reflect this.
Where is my logic incorrect?

Thanks, Rick DeBay

-- script --

RECREATE TABLE A
(
ID INTEGER NOT NULL,
CONSTRAINT PK_A PRIMARY KEY (ID)
);

RECREATE TABLE A2B
(
A INTEGER NOT NULL,
B INTEGER NOT NULL,
CONSTRAINT PK_A2B PRIMARY KEY (A,B)
);

RECREATE TABLE B
(
ID INTEGER NOT NULL,
CONSTRAINT PK_B PRIMARY KEY (ID)
);

RECREATE TABLE B2C
(
B INTEGER NOT NULL,
C INTEGER NOT NULL,
CONSTRAINT PK_B2C PRIMARY KEY (B,C)
);

RECREATE TABLE C
(
ID INTEGER NOT NULL,
CONSTRAINT PK_C PRIMARY KEY (ID)
);

ALTER TABLE A2B ADD CONSTRAINT FK_A2B_A
FOREIGN KEY (A) REFERENCES A (ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE A2B ADD CONSTRAINT FK_A2B_B
FOREIGN KEY (B) REFERENCES B (ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE B2C ADD CONSTRAINT FK_B2C_B
FOREIGN KEY (B) REFERENCES B (ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE B2C ADD CONSTRAINT FK_B2C_C
FOREIGN KEY (C) REFERENCES C (ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION;