When I add a LEFT OUTER JOIN, the query returns only a few rows
left-join, sql, sql-server, t-sql
Solution
When you perform a `LEFT OUTER JOIN` between tables `A` and `E`, you are maintaining your original set of data from `A`. That is to say, there is no data, or lack of data, in table `E` that can reduce the number of rows in your query.
However, when you then perform an INNER JOIN between `E` and `P` at the bottom, you are indeed opening yourself up to the possibility of reducing the number of rows returned. This will treat your subsequent `LEFT OUTER JOIN`s like `INNER JOIN`s.
Now, without your exact schema and a set of data to test against, this may or may not be the exact issue you are experiencing. Still, as a general rule, always put your `INNER JOIN`s before your `OUTER JOIN`s. It can make writing queries like this much, much easier. Your most restrictive joins come first, and then you won't have to worry about breaking any of your outer joins later on.
As a quick fix, try changing your last join to `P` to a `LEFT OUTER JOIN`, just to see if the `Z` join works.
Problem
The original query returns 160k rows. When I add the LEFT OUTER JOIN: ``` LEFT OUTER JOIN Table_Z Z WITH (NOLOCK) ON A.Id = Z.Id ``` the query returns only 150 rows. I'm not sure what I'm doing wrong. All I need to do is add a column to the query, which will bring back a code from a different table. The code could be a number or a NULL. I still have to display NULL, hence the reason for the LEFT join. They should join on the "id" columns. ``` SELECT <lots of stuff> + the new column that I need (called "code"). FROM dbo.Table_A A WITH (NOLOCK) INNER JOIN dbo.Table_B B WITH (NOLOCK) ON A.Id = B.Id AND A.version = B.version --this is where I added the LEFT OUTER JOIN. with it, the query returns 150 rows, without it, 160k rows. LEFT OUTER JOIN Table_Z Z WITH (NOLOCK) ON A.Id = Z.Id LEFT OUTER JOIN Table_E E WITH (NOLOCK) ON A.agent = E.agent LEFT OUTER JOIN Table_D D WITH (NOLOCK) ON E.location = D.location AND E.type = 'Organization' AND D.af_type = 'agent_location' INNER JOIN (SELECT X , MAX(Version) AS MaxVersion FROM LocalTable WITH (NOLOCK) GROUP BY agemt) P ON E.agent = P.location AND E.Version = P.MaxVersion ``` Does anyone have any idea what could be causing the issue?