You're doing a
LEFT JOIN
, so the order of evaluation matters.
For records in
t1
with no matching record in
t2
, the column
t2.INVOICE_QTY
will be
Null
. Therefore, your condition in the
WHERE
clause will not be met, and those records will be removed from the result.
You can either update the condition to allow for this:
...
WHERE (t1.quantity > t2.INVOICE_QTY Or t2.INVOICE_QTY Is Null) And t1.no = @no
Or, you can move the condition to the
JOIN
, so that it gets applied earlier:
...
FROM t1
LEFT OUTER JOIN t2
ON t1.no = t2.no
And t1.quantity < t2.INVOICE_QTY
WHERE t1.no = @no