• Here is a quick example of how moving the filter from the join to the WHERE clause can have an effect on the result of your queries (if you're using OUTER JOINS):

    Although there is a filter in the first query on the Forename, SQL still includes the other records. This is because SQL server does the inner join internally and applies the filter then adds all missing rows from the left table.

    Hope this makes sense.

    CREATE TABLE #Employee (ID INT IDENTITY(1, 1), Forename VARCHAR(20))

    INSERT INTO #Employee(Forename)

    SELECT 'Abu Dina' UNION ALL

    SELECT 'SQL4n00bs' UNION ALL

    SELECT 'Test' UNION ALL

    SELECT '1337'

    CREATE TABLE #Location (ID INT IDENTITY(1, 1), Employee_ID INT, Location VARCHAR(50))

    INSERT INTO #Location(Employee_ID, Location)

    SELECT 1, 'UK' UNION ALL

    SELECT 2, 'Manchester'

    SELECT a.* , b.*

    FROM #Employee AS a

    LEFT JOIN #Location AS b

    ON a.id = b.employee_id

    and a.forename = 'Abu Dina'

    SELECT a.* , b.*

    FROM #Employee as a

    LEFT JOIN #Location as b

    ON a.id = b.employee_id

    WHERE a.forename = 'Abu Dina'

    DROP TABLE #Employeea

    DROP TABLE #Location

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn