SELECT A.ValQuote,B.Description,C.NetID,D.ProcDesc
   FROM dbo.TableA A
      JOIN dbo.TableB B
         JOIN dbo.TableC C
            ON C.NetID = B.NetID
               AND C.Description = 'RULE3'
         JOIN dbo.TableD D
            ON D.SrlNo = C.SrlNo
               AND D.ProcType = 'TYPE3'
       ON B.BusnID = A.BusnID
         AND B.SlotLoad = 'Full'
         

SELECT A.ValQuote,B.Description,C.NetID,D.ProcDesc
   FROM dbo.TableA A
      JOIN dbo.TableB B
         JOIN dbo.TableC C
            ON C.NetID = B.NetID               
         JOIN dbo.TableD D
            ON D.SrlNo = C.SrlNo
       ON B.BusnID = A.BusnID

WHERE C.Description = 'RULE3'
   AND D.ProcType = 'TYPE3'
   AND B.SlotLoad = 'Full'
        
         

SELECT		a.ValQuote,
		b.[Description],
		c.NetID,
		d.ProcDesc
FROM		(
			SELECT	DISTINCT
				BusnID,
				ValQuote
			FROM	dbo.TableA
		) AS a
INNER JOIN	(
			SELECT	DISTINCT
				BusnID,
				[Description],
				NetID
			FROM	dbo.TableB
			WHERE	SlotLoad = 'Full'
		) AS b ON b.BusnID = a.BusnID
INNER JOIN	(
			SELECT	DISTINCT
				NetID,
				SrlNo
			FROM	dbo.TableC
			WHERE	[Description] = 'RULE3'
		) AS c ON c.NetID = b.NetID               
INNER JOIN	(
			SELECT	DISTINCT
				SrlNo,
				ProcDesc
			FROM	dbo.TableD
			WHERE	ProcType = 'TYPE3'
		) AS d ON d.SrlNo = c.SrlNo

quote:

---

Originally posted by Peso

In most cases, SQL Server is smart enough to know how and when to filter, ie the two approaches are the same.
But this is not always true. I tend to put the filtering at the ON part just for sure.

---

USE [Master]
GO

DROP TABLE	#Left,
		#Right

CREATE TABLE	#Left
		(
			i INT NOT NULL,
			j INT NOT NULL
		)

INSERT		#Left
SELECT		ABS(CHECKSUM(NEWID())) % 20000,
		ABS(CHECKSUM(NEWID())) % 200
FROM		sysobjects AS so1
CROSS JOIN	sysobjects AS so2
-- 3 966 001 records affected

CREATE NONCLUSTERED INDEX IX_Left ON #Left (i, j)

CREATE TABLE	#Right
		(
			i INT NOT NULL,
			j INT NOT NULL
		)

INSERT		#Right
SELECT		ABS(CHECKSUM(NEWID())) % 20000,
		ABS(CHECKSUM(NEWID())) % 200
FROM		sysobjects AS so1
CROSS JOIN	sysobjects AS so2
-- 3 966 001 records affected

CREATE NONCLUSTERED INDEX IX_Right ON #Right (i, j)

-- Peso 1
SELECT		l.i,
		r.i
FROM		#Left AS l
INNER JOIN	#Right AS r ON r.i = l.i
WHERE		l.j = 12
		AND r.j = 21
-- 20 169 records affected

-- Peso 2
SELECT		l.i,
		r.i
FROM		#Left AS l
INNER JOIN	#Right AS r ON r.i = l.i
			AND r.j = 21
WHERE		l.j = 12
-- 20 169 records affected

-- Peso 3
SELECT		l.i,
		r.i
FROM		(
			SELECT	i
			FROM	#Left
			WHERE	j = 12
		) AS l
INNER JOIN	#Right AS r ON r.i = l.i
			AND r.j = 21
-- 20 169 records affected

-- Peso 4
SELECT		l.i,
		r.i
FROM		(
			SELECT	i
			FROM	#Left
			WHERE	j = 12
		) AS l
INNER JOIN	(
			SELECT	i
			FROM	#Right
			WHERE	j = 21
		) AS r ON r.i = l.i
-- 20 169 records affected