Right Outer Join does not work after inserting row

Question

I have 2 table with the following data

ManualVersions - list of all manual versions

+------------------+---------------+--------------+
|Id   | ManualID   | VersionNumber |  VersionName |
+-----+------------+---------------+--------------+
|10   |   12       |   1.0         |   Version 1  |
|17   |   12       |   2.0         |   Version 2  |

CustomersManuals - This lets me know which customer have access to which manual versions

+---+---------------+--------------------+
|Id | CustomerID    |  ManualVersionsID  |
+-- +---------------+--------------------+
|4  | 19            |     10             |
|8  | 24            |     10             |

The goal is to write a query that lets me know which manual versions a customer does and does not have access to.

To do this I run the following sql on the above tables.

Select CustomersManuals.id as CustomerManualsID, ManualVersions.VersionNumber, ManualVersions.VersionName, ManualVersions.id as ManualVersionID
FROM CustomersManuals
RIGHT OUTER JOIN ManualVersions ON CustomersManuals.ManualVersionsID = ManualVersions.id
WHERE (CustomersManuals.CustomerID=24 OR CustomersManuals.CustomerID iS NULL) AND ManualVersions.ManualID = 12

Results from above SQL

+-------------------+--------------+---------------+----------------+
|CustomerManualsID  | VersionNumber| VersionName   | ManualVersionID|
+-------------------+--------------+---------------+----------------+
| 8                 | 1.0          | Version 1     | 10             |
| NULL              | 2.0          | Version 2     | 17             |

The above is correct and does what I want. Null is returned showing customerID 24 does not have manual 2.

If I now INSERT the following row to the CustomersManuals table the above SQL will not work

CustomersManuals (row i inserted)

+----+------------+------------------+
| Id | CustomerID | ManualVersionsID |
+----+------------+------------------+
|30  | 18         | 17               |

The above sql now only returns 1 row

+-------------------+---------------+---------------+---------------------+
| CustomerManualsID | VersionNumber | VersionName   |   ManualVersionID   |
+-------------------+---------------+---------------+---------------------+
|  8                |  1.0          | Version 1     |   10                |

Note in the above table the Row that had null against the CustomerManualsID has gone.

Is there a way to show all the manuals a customer has and all the ones they don’t have?

Incidentally, no one ever uses `RIGHT [OUTER] JOIN`, preferring instead its more intuitive counterpart. — Strawberry, Oct 31 '19 at 18:09
Learn what LEFT/RIGHT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left/right table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right/left [sic] table column to be not NULL after a LEFT/RIGHT JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS For code questions give a [mre]. — philipxy, Oct 31 '19 at 18:11
This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Oct 31 '19 at 18:12
Does this answer your question? [Right Join not returning all expected rows](https://stackoverflow.com/questions/18784519/right-join-not-returning-all-expected-rows) — philipxy, Oct 31 '19 at 19:46

score 0 · Accepted Answer · answered Oct 31 '19 at 18:06

Put the conditions on the child table in the ON clause, not WHERE. Then you don't need to test explicitly for NULL.

Select CustomersManuals.id as CustomerManualsID, ManualVersions.VersionNumber, ManualVersions.VersionName, ManualVersions.id as ManualVersionID
FROM CustomersManuals
RIGHT OUTER JOIN ManualVersions ON CustomersManuals.ManualVersionsID = ManualVersions.id AND CustomersManuals.CustomerID=24
WHERE ManualVersions.ManualID = 12

DEMO

score 0 · Answer 2 · answered Oct 31 '19 at 18:11

Augmenting Barmar's answer, it would be more usual to write this...

Select m.id CustomerManualsID
     , v.VersionNumber
     , v.VersionName
     , v.id ManualVersionID
  FROM ManualVersions v
  LEFT
  JOIN CustomersManuals m 
    ON m.ManualVersionsID = v.id 
   AND m.CustomerID = 24
 WHERE v.ManualID = 12

Right Outer Join does not work after inserting row

2 Answers2