"IS NOT NULL" Is still showing NULL SQL-CodePudding

I'm using Microsoft's SQL server to write a query to display information about the customer that placed the order with Id equal to 10643 using LEFT JOIN. This is a query I wrote:

SELECT *
FROM Customers LEFT JOIN Orders 
ON OrderID = 10835
AND Orders.OrderID IS NOT NULL
AND Customers.CustomerID = Orders.CustomerID

The result I'm getting includes Orders.OrderID as NULL

While The result I want is this:

I would like to know why even when I select NOT NULL for the OrderID of Orders for the matching condition, The result still shows NULL. Thanks y'all in advance

CodePudding user response：

You need to LEFT JOIN on just the CustomerID then filter in a WHERE clause, eg

SELECT *
FROM Customers 
LEFT JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderID = 10835

There's no need for IS NOT NULL, since you're filtering all those out already. Note that since you're filtering on the outer table, the query is now equivalent to an INNER JOIN

SELECT *
FROM Customers 
JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderID = 10835