Left Outer Join Not Working?

问题:

I have a query pulling data from three tables using LEFT OUTER JOIN for both joins. I need the query to return the left most (Salesrep table) info even if the there is no corresponding data in the two right tables (prescriber and prescriptions, respectively). When I run this query without the date parameters in the WHERE clause, I get the expected return, but as soon as I include the date parameters I get nothing returned where there is no matching data for a salesrep. I need to at least see the salesrep table columns requested in the query.

Here is the query... any help is VERY much appreciated.

SELECT  salesrep.salesrepid as SalesRepID,
        salesrep.fname as SalesrepFName,
        salesrep.lname as SalesRepLName,
        salesrep.fname+' '+salesrep.lname as SalesRepFullName,
        prescriber.dea_no as PDeaNo,
        prescriber.lname+', '+prescriber.fname as DocName,
        CONVERT(VARCHAR(8), prescriptions.filldate, 1) as FillDate,
        prescriptions.drugname as DrugName,
        prescriptions.daysupply as Supply,
        prescriptions.qtydisp as QtyDisp,
        prescriptions.rx_no as Refill,
        prescriptions.copay as Sample,
        ROUND(prescriptions.AgreedToPay-(prescriptions.AgreedToPay*.07),2) as AgreedToPay,
        prescriptions.carrierid as CarrierID
FROM    salesrep
  LEFT OUTER JOIN prescriber on salesrep.salesrepid = prescriber.salesrepid
  LEFT OUTER JOIN prescriptions on prescriber.dea_no = prescriptions.dea_no
  WHERE salesrep.salesrepid = 143 AND
        prescriptions.filldate >= '09-01-12' AND
        prescriptions.filldate <= '09-17-12'
ORDER BY prescriptions.filldate

回答1:

You should move the constraints on prescriptions.filldate into the ON condition of the join, and remove it from the where clause:

LEFT OUTER JOIN prescriptions ON prescriber.dea_no = prescriptions.dea_no
                             AND prescriptions.filldate >= '09-01-12'
                             AND prescriptions.filldate <= '09-17-12'

Otherwise, entries for which there are no prescriptions end up with nulls in prescriptions.filldate, and the WHERE clause throws them away.

回答2:

Here you can find a brief description about query processing phases (it's common for most DBMSes). You will find out there, that for OUTER JOIN:

1. first CARTESIAN JOIN is produced,
2. than the ON condition is performed on result set producing subset of rows,
3. after than outer rows are appended with NULLs on inner table's joined columns,
4. on that result the WHERE clause is applied performing filtering.

When you place the condition within WHERE clause which touches outer tables rows they're all discarded. You should simply place that condition within the ON clause, as that one is evaluated before outer rows are appended.

So, those conditions:

prescriptions.filldate >= '09-01-12' AND
prescriptions.filldate <= '09-17-12'

should be moved into ON clause.

回答3:

This is because your prescriptions.filldate inequalities are filtering out your salesrep rows that don't have a value in the prescriptions.filldate column.

So if there are null values (no matching data from the right tables), then the entire row, including the salesrep data is filtered out by the date filters - because the null doesn't fall between the two dates.

回答4:

This fiddle can be useful for illustrating that:

a restriction placed in the ON clause is processed before the join, while a restriction placed in the WHERE clause is processed after the join.

Note that does not matter with inner joins, but it matters a lot with outer joins. More details in docs

---

Table t1

| num | name |
| --- | ---- |
| 1   | a    |
| 2   | b    |
| 3   | c    |

---

Table t2

| num | value |
| --- | ----- |
| 1   | xxx   |
| 3   | yyy   |
| 5   | zzz   |

---

Join condition in the ON clause

SELECT * FROM t1 
LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';

| num | name | num | value |
| --- | ---- | --- | ----- |
| 1   | a    | 1   | xxx   |
| 2   | b    |     |       |
| 3   | c    |     |       |

---

Join condition in the WHERE clause

SELECT * FROM t1 
LEFT JOIN t2 ON t1.num = t2.num 
WHERE t2.value = 'xxx';

| num | name | num | value |
| --- | ---- | --- | ----- |
| 1   | a    | 1   | xxx   |

---

View on DB Fiddle