Right Join Not Including Null Rows

Question

Hoping someone can help me with this. I am rewriting an Alteryx Workflow into SQL and in the workflow there is a left, inner and right join. When I write my sql to replicate the left and inner join I get the same number of records as the Alteryx workflow does but I'm having an issue with the right join. I should be getting 25 records but my workflow keeps giving me 17(the same as the results of the left and inner join are giving). Table 1 has only quote number 10 and 11(which fill 17 rows of data) in it but table 2 has all 10 quotes so with a right join I expected to see my results showing the 2 quotes from table 1 which would fill 17 rows similar to the left and inner join but also populate the rest of the results with the other 8 rows of missing quotes to give me 25 rows in total. Why isn't this happening? Why is the right join not giving me the quotes that table1 doesn't have? Below is the query:

SELECT
table2."Quote No",
table1."Quote No",
table1.* 
FROM table1
right JOIN
    (
    SELECT distinct
    "Quote No"
    FROM Table2
    GROUP BY 1
        )table2 ON table2."Quote No"=table1."Quote No"
WHERE 1=1
AND table1."Quote No" IN  ('10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20')

GROUP BY when no aggregate functions?!? And then SELECT DISTINCT? Skip the GROUP BY, simply do SELECT DISTINCT. — jarlh, Feb 03 '22 at 07:18
What's in the two tables? The same columns? If `select * from table1 where whatever` gives you the 17 rows you want from table1, and `select * from table2 where whatever` gives you the 8 rows you want from table2, and if all you want is all of those 17 + 8 rows, then why not just do a `UNION ALL` to join the 17 + 8 rows together? — johnjps111, Feb 19 '22 at 13:58
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, Aug 01 '22 at 06:55

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

When you use an outer join (like RIGHT JOIN), and when the 'Other' table (the table on the other side of the join: Table1 for you) has no matching rows, Table2 rows are still to be returned, but the Table1 columns will appear as NULLs. If you add a WHERE condition, such as the one you added on those columns ( AND table1."Quote No" IN ('10...) table1."Quote No" is treated as NULL, and it does not match anything inside the list and eliminated. Try moving your WHERE condition to the JOIN clause:

SELECT table2."Quote No", table1."Quote No", table1.* 
FROM table1 
     right JOIN ( SELECT distinct "Quote No" FROM Table2 GROUP BY 1 )table2 
     ON table2."Quote No"=table1."Quote No" 
     AND table1."Quote No" IN ('10','11','12','13','14','15','16','17','18','19','20')

score 0 · Answer 2 · answered Feb 03 '22 at 19:55

0

Fixed it by switching my tables around so made table 2 my base table and left joined it to table 1 plus the feedback around including where clause in the join helped me stumble upon the 25 rows I was looking for, thanks all!!

answered Feb 03 '22 at 19:55

GiantPencil

1

Right Join Not Including Null Rows

2 Answers2