Which SQL query is faster? Filter on Join criteria or Where clause?

by

Performance-wise, they are the same (and produce the same plans)

Logically, you should make the operation that still has sense if you replace INNER JOIN with a LEFT JOIN.

In your very case this will look like this:

SELECT  *
FROM    TableA a
LEFT JOIN
        TableXRef x
ON      x.TableAID = a.ID
        AND a.ID = 1
LEFT JOIN
        TableB b
ON      x.TableBID = b.ID

or this:

SELECT  *
FROM    TableA a
LEFT JOIN
        TableXRef x
ON      x.TableAID = a.ID
LEFT JOIN
        TableB b
ON      b.id = x.TableBID
WHERE   a.id = 1

The former query will not return any actual matches for a.id other than 1, so the latter syntax (with WHERE) is logically more consistent.

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. CROSS JOIN vs INNER JOIN in SQL
  3. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  4. How to generate a range of numbers between two numbers?
  5. SET versus SELECT when assigning variables?
  6. Multiple INSERT statements vs. single INSERT with multiple VALUES
  7. Split function equivalent in T-SQL?
  8. Generate Dates between date ranges
  9. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  10. How to print VARCHAR(MAX) using Print Statement?
  11. Where value in column containing comma delimited values
  12. How to update Identity Column in SQL Server?
  13. SQL query to split column data into rows
  14. Common Table Expression, why semicolon?
  15. T-SQL split string based on delimiter
  16. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  17. Concatenate values based on ID
  18. Splitting delimited values in a SQL column into multiple rows
  19. SQL “between” not inclusive
  20. Can I set ignore_dup_key on for a primary key?
  21. Using PIVOT in SQL Server 2008
  22. Stored procedure that exports data into csv files only exports to one file
  23. How do I prevent a database trigger from recursing?
  24. TSQL – How to use GO inside of a BEGIN .. END block?
  25. Using IF ELSE statement based on Count to execute different Insert statements
  26. How to combine results of two queries into a single dataset
  27. Executing SQL Server Agent Job from a stored procedure and returning job result
  28. SQL take just the numeric values from a varchar
  29. COALESCE Function in TSQL
  30. Using CASE Statement inside IN Clause

Leave a Comment