Right join not generating all rows

Question

I have two tables Factory and Site, the factory table has 10 records

Code      Site
Skirt     115
Skirt     116
Skirt     117
Skirt     118
Skirt     119
Skirt     120
Skirt     121
Skirt     122
Skirt     123
Skirt     124

and tbl_site has the following sites Site 115 116 117 118 119 120 121 122 123 124 125 But when I run the following query I only get 10 counts instead of 11

select count(*) from tbl_factory fr
right join tbl_site sit on fr.siteid = sit.siteid
where fr.code='Skirt'

Put the `WHERE` filter into the `ON`. Note that `RIGHT JOIN` is simply the mirror of `LEFT JOIN`, and most people find it confusing especially in the presence of other joins, so generally avoided. — Charlieface, Feb 23 '22 at 02:41
LEFT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Similarly for RIGHT JOIN ON & right table rows. Always know what INNER JOIN ON you want as part of an OUTER JOIN ON. After a LEFT/RIGHT JOIN ON a WHERE, INNER JOIN or HAVING that requires a right/left [sic] table column to be not NULL removes rows with introduced NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. Please research before posting. (This can clearly be expected to be a faq.) For debug a [mre] is required. [ask] [Help] [meta] [meta.se] — philipxy, Feb 23 '22 at 02:47

score 0 · Answer 1 · answered Feb 23 '22 at 03:01

I would switch to a LEFT JOIN starting with the table you want all rows... essentially the same, but helps to start with ALL you want and OPTIONALLY getting the other.

This will show proper context to ensure all sites accounted for and which sites have a factory that makes skirts.

select 
      s.siteid,
      coalesce( count(f.siteid), 0 ) as HasFactory
   from 
      tbl_site s 
         LEFT JOIN tbl_factory f
            on s.siteid = f.siteid
           AND f.code = 'Skirt'
  group by
      s.siteid

Once that is confirmed, you can just do a count which will always give 11 sites since a left-join (not required having a match in the factory table). But you can also get a count of factories that DID make skirts

select 
      count(*) TotalSites,
      count( distinct f.siteid ) CountOfFactoriesMakingSkirts
   from 
      tbl_site s 
         LEFT JOIN tbl_factory f
            on s.siteid = f.siteid
           AND f.code = 'Skirt'

score -1 · Answer 2 · answered Feb 23 '22 at 02:39

try removing the where clause from your query since it filters the join results-the entire 11 rows- and returns only those with the code skirt -the 10 rows from the other table tbl_site or you can use the And as mentioned in this question why is my TSQL left join not working

Right join not generating all rows

2 Answers2