SQL - unexpected change in number of results after adding left join

The following query:

SELECT Assets.Id, Assets.Description
FROM Assets
WHERE CaseNumber = 1265

produces 29 results.

After adding the following left join onto another table:

SELECT Assets.Id, Assets.Description
FROM Assets
LEFT JOIN StockShares ON StockShares.AssetId = Assets.AssetId
WHERE CaseNumber = 1265

I'm down开发者_如何学Go to 3. This has totally thrown me. I thought I knew about SQL?

Can anyone either confirm this result is not quite right, or explain why the number of results has dropped?

---

If all you've added to a query is a LEFT JOIN (with ON clause), then the number of results should stay the same or increase - if no rows match the join criteria, the result count should remain static. If there are multiple matching rows in the right table, for a single row in the left table, then the result count will increase.

If you add a LEFT JOIN, but also add new conditions into your WHERE or HAVING clauses, you may effectively transform your LEFT JOIN into an INNER JOIN. In such a case, the number of results may obviously drop. What has to be borne in mind is that the entire WHERE clause (also, HAVING) must be true for a result to appear in the final result set. If you're referencing columns from the right side of a LEFT JOIN, then you must consider what will happen when such columns are NULL.

---

Replace WHERE with AND.

WHERE applies after the join; AND applies during the join.

---

I don't believe this is possible given the code you're showing. Something else is going on here.

---

If AssetId is NULL the join will fail to yield as many results. You can play with this sample fiddle here (code below): http://sqlfiddle.com/#!18/97124/2

CREATE TABLE Assets(
    Id INT PRIMARY KEY IDENTITY,
    Description VARCHAR(200),
    AssetId INT
);

CREATE TABLE Stockshares(
  AssetId INT
);

insert into assets values('desription1', 1);
insert into assets values('desription2', null);
insert into assets values('desription3', null);

insert into Stockshares values(1);
insert into Stockshares values(2);
insert into Stockshares values(3);


Select * from assets
join stockshares on stockshares.assetid = assets.assetid
where stockshares.AssetId = 1 -- change to 2,  get less results because AssetId is null for asset 2

Here's some documentation for joining on nulls: https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-2017#nulls_joins

---

Try to put the where clause before joining youll get the same results. The fact that where is applied after the left join has happened has filtered out most records as the left join on unmatched records would be null and hence reduced the record count