ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Does the tables order matter in an INNER JOIN query?

book

Article ID: 41273

calendar_today

Updated On:

Products

Datacom DATACOM - AD

Issue/Introduction

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

Question: 

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

Environment:  

Datacom SQL Option – all releases

Answer: 

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.

Additional Information:

See also Order of Joins section of Datacom documentation.

Environment

Release: DATABB00200-14-Datacom/AD
Component: