Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]

by

The on clause is used when the join is looking for matching rows. The where clause is used to filter rows after all the joining is done.

An example with Disney toons voting for president:

declare @candidates table (name varchar(50));
insert @candidates values 
    ('Obama'), 
    ('Romney');
declare @votes table (voter varchar(50), voted_for varchar(50));
insert @votes values 
    ('Mickey Mouse', 'Romney'),
    ('Donald Duck', 'Obama');

select  *
from    @candidates c
left join    
        @votes v
on      c.name = v.voted_for
        and v.voter="Donald Duck"

This still returns Romney even though Donald didn’t vote for him. If you move the condition from the on to the where clause:

select  *
from    @candidates c
left join    
        @votes v
on      c.name = v.voted_for
where   v.voter="Donald Duck"

Romney will no longer be in the result set.

More Related Contents:

  1. How to extrapolate dates in SQL Server to calculate the daily counts?
  2. How do I split a string so I can access item x?
  3. Insert results of a stored procedure into a temporary table
  4. Best approach to remove time part of datetime in SQL Server
  5. SQL Server : Columns to Rows
  6. get a comma delimited string from rows [duplicate]
  7. Find all tables containing column with specified name – MS SQL Server
  8. How important is the order of columns in indexes?
  9. Remove Identity from a column in a table
  10. How to split a single column values to multiple column values?
  11. Why no windowed functions in where clauses?
  12. T-SQL Cast versus Convert
  13. What is a stored procedure?
  14. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  15. convert Excel Date Serial Number to Regular Date
  16. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  17. Partition Function COUNT() OVER possible using DISTINCT
  18. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  19. Dynamic SQL to generate column names?
  20. Pivot data in T-SQL
  21. Split one column value into multiple column values
  22. How to find consecutive rows based on the value of a column?
  23. T-SQL – Aliasing using “=” versus “as” [closed]
  24. Which SQL query is faster? Filter on Join criteria or Where clause?
  25. Know relationships between all the tables of database in SQL Server
  26. How to escape square brackets inside square brackets for field name
  27. is it possible to select EXISTS directly as a bit?
  28. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  29. Using CASE Statement inside IN Clause
  30. SQL Convert Week Number to Date (dd/MM)

Leave a Comment