What’s the difference between “where” clause and “on” clause when table left join?

by

The where clause applies to the whole resultset; the on clause only applies to the join in question.

In the example supplied, all of the additional conditions related to fields on the inner side of the join – so in this example, the two queries are effectively identical.

However, if you had included a condition on a value in the table in the outer side of the join, it would have made a significant difference.

You can get more from this link: http://ask.sqlservercentral.com/questions/80067/sql-data-filter-condition-in-join-vs-where-clause

For example:

select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 and t2.f4=1

select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 where t2.f4=1

– do different things – the former will left join to t2 records where f4 is 1, while the latter has effectively been turned back into an inner join to t2.

More Related Contents:

  1. SQL JOIN – WHERE clause vs. ON clause
  2. SQL – HAVING vs. WHERE
  3. Conditional WHERE clause in SQL Server
  4. Left Join With Where Clause
  5. What is the difference between HAVING and WHERE in SQL?
  6. Filter Table Before Applying Left Join
  7. Does the order of where clauses matter in SQL?
  8. SQL server ignore case in a where expression
  9. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  10. Is a JOIN faster than a WHERE?
  11. How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?
  12. sql query if parameter is null select all
  13. Oracle date “Between” Query
  14. How to implement a many-to-many relationship in PostgreSQL?
  15. Combining “LIKE” and “IN” for SQL Server [duplicate]
  16. What is the difference between JOIN and UNION?
  17. Oracle: What does `(+)` do in a WHERE clause?
  18. Sorting null values after all others, except special
  19. Can I do a max(count(*)) in SQL?
  20. Which SQL query is better, MATCH AGAINST or LIKE?
  21. SQL Server: drop table cascade equivalent?
  22. How to fill date gaps in MySQL?
  23. Ad hoc queries vs stored procedures vs Dynamic SQL [closed]
  24. Function for week of the month in mysql
  25. How to extract group from regular expression in Oracle?
  26. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  27. Add a row number to result set of a SQL query
  28. How to replace specific values in a oracle database column?
  29. is there a PRODUCT function like there is a SUM function in Oracle SQL?
  30. Connect different Windows User in SQL Server Management Studio (2005 or later)

Leave a Comment