Condition within JOIN or WHERE

by

The relational algebra allows interchangeability of the predicates in the WHERE clause and the INNER JOIN, so even INNER JOIN queries with WHERE clauses can have the predicates rearrranged by the optimizer so that they may already be excluded during the JOIN process.

I recommend you write the queries in the most readable way possible.

Sometimes this includes making the INNER JOIN relatively “incomplete” and putting some of the criteria in the WHERE simply to make the lists of filtering criteria more easily maintainable.

For example, instead of:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
    AND c.State="NY"
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
    AND a.Status = 1

Write:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
WHERE c.State="NY"
    AND a.Status = 1

But it depends, of course.

More Related Contents:

  1. How do I obtain a Query Execution Plan in SQL Server?
  2. Count(*) vs Count(1) – SQL Server
  3. SQL JOIN: is there a difference between USING, ON or WHERE?
  4. When should I use CROSS APPLY over INNER JOIN?
  5. Subqueries vs joins
  6. Difference between Oracle’s plus (+) notation and ansi JOIN notation?
  7. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  8. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  9. Importance of varchar length in MySQL table
  10. SQL SELECT speed int vs varchar
  11. SQL ‘like’ vs ‘=’ performance
  12. How many rows in a database are TOO MANY?
  13. Is a JOIN faster than a WHERE?
  14. What’s the best to check if item exist or not: Select Count(ID)OR Exist(…)?
  15. How does table alias names affect performance?
  16. Performance issue in using SELECT *? [duplicate]
  17. Fastest way to determine if record exists
  18. SQL count(*) performance
  19. Why is a UDF so much slower than a subquery?
  20. Subquery v/s inner join in sql server
  21. SQL Server SELECT LAST N Rows
  22. Improving performance of cluster index GUID primary key
  23. Two single-column indexes vs one two-column index in MySQL?
  24. How do you interpret a query’s explain plan?
  25. Which SQL statement is faster? (HAVING vs. WHERE…)
  26. “SELECT *” vs “SELECT ” for performance
  27. Challenge,how to implement an algorithm for six degree of separation?
  28. Entity Framework Skip/Take is very slow when number to skip is big
  29. Oracle , != , ^= operators
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment