I want to discuss a problem I am sure many of us encountered at least once in our life. I myself made this common mistake at least two times before I learned it by heart and now can easily recognize and point others to it. Let's consider this typical life scenario - bring all customers and their order records. If the customer doesn't have any order, let's bring this customer anyway.

USE AdventureWorksLT 
GO
SELECT 
	C.CompanyName, 
	OH.* 
FROM SalesLT.Customer C 
LEFT JOIN SalesLT.SalesOrderHeader OH 
	ON C.CustomerID = OH.CustomerID 

SELECT @@RowCount 

/*
---------
440
*/

We get 440 records in return. Now, suppose we only want to see expensive orders, e.g. orders that are greater than $5000. We re-write this query as

 
USE AdventureWorksLT
GO

SELECT 
	C.CompanyName, 
	OH.* 
FROM SalesLT.Customer C 
LEFT JOIN SalesLT.SalesOrderHeader OH 
	ON C.CustomerID = OH.CustomerID 
WHERE OH.TotalDue > 5000 

select @@RowCount 

/*
---------
15
*/

And we only get 15 records! What happened? Where are the customers that don't have orders or have less expensive orders? By adding a WHERE condition on the OrderHeader table we transformed this query into an INNER JOIN. In order to keep this query as a LEFT JOIN, we need to move this condition from WHERE clause into JOIN clause like this

USE AdventureWorksLT
GO

SELECT 
	C.CompanyName, 
	OH.* 
FROM SalesLT.Customer C 
LEFT JOIN SalesLT.SalesOrderHeader OH 
	ON C.CustomerID = OH.CustomerID 
	AND OH.TotalDue > 5000 

select @@RowCount 

/*
---------
440
*/

Now we get back 440 records. The reason of such behavior is simple. JOIN evaluates first and then WHERE clause of the SQL statement. Please see this excellent blog for the logical order of different clauses in the SELECT statement Anatomy of a Query and another article by Plamen Ratchev on this exact issue Outer Joins

 So, the records joined correctly get filtered in the WHERE clause and we end up with the INNER JOIN equivalent. Now you know the reason and hopefully you'll be able to avoid this problem in your code.