Does the tables order matter in an INNER JOIN query?
search cancel

Does the tables order matter in an INNER JOIN query?

book

Article ID: 41273

calendar_today

Updated On:

Products

Datacom DATACOM - AD

Issue/Introduction

The following query runs quite slowly:

SELECT * FROM TAB1
INNER JOIN TAB2
ON TAB2.FIELDA = TAB1.FIELDC
WHERE TAB2.FIELDB = value

but if the order of the tables in the query is reversed, it performs better:

SELECT * FROM TAB2
INNER JOIN TAB1
ON TAB2.FIELDA = TAB1.FIELDC
WHERE TAB2.FIELDB = value

Is this a problem or the expected behavior for INNER JOIN queries?

Environment

Datacom SQL Option - all releases

Resolution

INNER JOIN forces the join order and the only restriction in the above queries is on the TAB2 table, hence it runs better when TAB2 is the first table.