Why is my t-sql left join not working?

by

The reason is because you are including the right-hand table in the WHERE clause. You should move that to the ON condition of the LEFT JOIN:

Select    P.appId, S.stepId, S.section, P.start
From      #appSteps    S   With (NoLock)
Left Join #appProgress P   On  S.stepId = P.stepId 
                           And P.appId = 101
Where     S.section Is Not Null

The reason it does this is because the WHERE clause is evaluated after the LEFT JOIN, which then filters out your NULL results from the LEFT JOIN.

Including the right-hand table of a LEFT JOIN (or the left-hand table of a RIGHT JOIN) in the WHERE clause effectively transforms the OUTER JOIN into an INNER JOIN.

More Related Contents:

  1. Get Parent with multiple children in desc order
  2. How can I schedule a job to run a SQL query daily?
  3. Convert Month Number to Month Name Function in SQL
  4. SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry [duplicate]
  5. Reset identity seed after deleting records in SQL Server
  6. How to avoid the “divide by zero” error in SQL?
  7. Multiple INSERT statements vs. single INSERT with multiple VALUES
  8. Limit on the WHERE col IN (…) condition
  9. Removing leading zeroes from a field in a SQL statement
  10. How to simulate UNPIVOT in Access?
  11. Get day of week in SQL Server 2005/2008
  12. Getting result of dynamic SQL into a variable for sql-server
  13. Better techniques for trimming leading zeros in SQL Server?
  14. Dynamic Pivot Table in SQL Server
  15. Optimized SQL for tree structures
  16. How to insert a blob into a database using sql server management studio
  17. Favourite performance tuning tricks [closed]
  18. Query a database based on result of query from another database
  19. How to delete the top 1000 rows from a table using Sql Server 2008?
  20. CHECK CONSTRAINT on multiple columns
  21. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  22. SQL count consecutive days
  23. How to create a Date in SQL Server given the Day, Month and Year as Integers
  24. Count records for every month in a year
  25. Is there a severe performance hit for using Foreign Keys in SQL Server?
  26. How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
  27. Backup SQL Schema Only?
  28. Date serial in SQL?
  29. SQL Server: +(unary) operator on non-numeric Strings
  30. Parameterise table name in .NET/SQL?

Leave a Comment