How JOIN Order Can Increase Performance in SQL Queries
Join the DZone community and get the full member experience.
Join For FreeIntroduction
FROM [Table-A] AS a INNER JOIN [Table-B] AS b ON a.IDNO = b.IDNO
FROM [Table-B] AS a INNER JOIN [Table-A] AS b ON a.IDNO = b.IDNO
Which one is best for performance?
To understand it Details Lets take an Example
Step-1 [ Create Base Table and Insert Some Records ]
-- Item Master IF OBJECT_ID(N'dbo.tbl_ITEMDTLS', N'U')IS NOT NULL BEGIN DROP TABLE [dbo].[tbl_ITEMDTLS]; END GO CREATE TABLE [dbo].[tbl_ITEMDTLS] ( ITEMCD INT NOT NULL IDENTITY PRIMARY KEY, ITEMNAME VARCHAR(50) NOT NULL ) GO -- Inserting Records INSERT INTO [dbo].[tbl_ITEMDTLS] (ITEMNAME) VALUES ('ITEM-1'),('ITEM-2'),('ITEM-3'); -- Item UOM Master IF OBJECT_ID(N'dbo.tbl_UOMDTLS', N'U')IS NOT NULL BEGIN DROP TABLE [dbo].[tbl_UOMDTLS]; END GO CREATE TABLE [dbo].[tbl_UOMDTLS] ( UOMCD INT NOT NULL IDENTITY PRIMARY KEY, UOMNAME VARCHAR(50) NOT NULL ) GO -- Inserting Records INSERT INTO [dbo].[tbl_UOMDTLS] (UOMNAME) VALUES ('KG'),('LTR'),('GRM'); GO -- Transaction Table IF OBJECT_ID(N'dbo.tbl_SBILL', N'U')IS NOT NULL BEGIN DROP TABLE [dbo].[tbl_SBILL]; END GO CREATE TABLE [dbo].[tbl_SBILL] ( TRID INT NOT NULL IDENTITY PRIMARY KEY, ITEMCD INT NOT NULL, UOMCD INT NOT NULL, QTY DECIMAL(18,3) NOT NULL, RATE DECIMAL(18,2) NOT NULL, AMOUNT AS QTY * RATE ); GO -- Foreign Key Constraint ALTER TABLE [dbo].[tbl_SBILL] ADD CONSTRAINT FK_ITEM_tbl_SBILL FOREIGN KEY(ITEMCD) REFERENCES [dbo].[tbl_ITEMDTLS](ITEMCD); GO ALTER TABLE [dbo].[tbl_SBILL] ADD CONSTRAINT FK_UOMCD_tbl_SBILL FOREIGN KEY(UOMCD) REFERENCES [dbo].[tbl_UOMDTLS](UOMCD); -- Insert Records INSERT INTO [dbo].[tbl_SBILL] (ITEMCD, UOMCD, QTY, RATE) VALUES (1, 1, 20, 2000),(2, 3, 23, 1400);
Step-2 [ Now Make Some JOIN ]
SELECT b.TRID, b.ITEMCD, a.ITEMNAME, b.UOMCD, c.UOMNAME, b.QTY, b.RATE, b.AMOUNT FROM [dbo].[tbl_ITEMDTLS] AS a INNER JOIN [dbo].[tbl_SBILL] AS b ON a.ITEMCD = b.ITEMCD INNER JOIN [dbo].[tbl_UOMDTLS] AS c ON b.UOMCD = c.UOMCD;
SELECT b.TRID, b.ITEMCD, a.ITEMNAME, b.UOMCD, c.UOMNAME, b.QTY, b.RATE, b.AMOUNT FROM [dbo].[tbl_ITEMDTLS] AS a INNER JOIN [dbo].[tbl_SBILL] AS b ON a.ITEMCD = b.ITEMCD INNER JOIN [dbo].[tbl_UOMDTLS] AS c ON b.UOMCD = c.UOMCD OPTION ( QUERYRULEOFF JoinCommute);
Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments