Why after RIGHT JOIN, rows in table 2 that do not match table 1 are excluded?

Question

I need to join two tables: `our_sample` and `tls207_pers_appln`, which come from PATSTAT.

`our_sample` has 4 columns: appln_id, appln_auth, appln_nr, appln_kind.

`tls207_pers_appln` has 4 columns: appln_id, person_id, applt_seq_nr, invt_seq_nr.

`our_sample` has 2191 rows, some of which (60 rows) have no corresponding data in `tls207_pers_appln`.

Because I wanted to keep all appln_ids in `our_sample` (even if they have no matching information in `tls207_pers_appln`), I used a RIGHT JOIN to join the two tables.

However, the generated view `t2_tot_in_patent` has only 2096 appln_ids.

This is partly due to the restrictions I set (35 patents were discarded because I only selected those with `HAVING MAX(invt_seq_nr) > 0`, which is acceptable). But this should result in 2191-35 = 2156 patents.

Instead, I got 2096 patents, namely: 2191 (in our_sample) - 60 (missing appln_id in our_sample) - 35 (appln_id with invt_seq_nr = 0).

But the whole purpose of using RIGHT JOIN is that these 60 patents should not be lost. Why is this so?

--Compile the total number of inventors per patent: t2_tot_in_patent

DROP VIEW IF EXISTS t2_tot_in_patent;
CREATE VIEW t2_tot_in_patent AS
SELECT m.appln_id, MAX(invt_seq_nr) AS tot_in_patent
FROM patstat2022a.tls207_pers_appln AS t7
RIGHT OUTER JOIN cecilia.our_sample AS m
ON t7.appln_id = m.appln_id
GROUP BY appln_id
HAVING MAX(invt_seq_nr) > 0

Answer 1

suggestion:

GROUP BY m.appln_id

Maybe the SQL is grouping according to the appln_id of another table.

Try this:

HAVING MAX(invt_seq_nr) > 0 OR MAX(invt_seq_nr) IS NULL

Currently your code only fetches those rows with a maximum value greater than 0, but rows that do not exist in the other table are ignored because their values ​​are NULL.