Full outer join not showing unmatched rows - SQL

Question

I am trying to do a full outer join of two tables, matching them with the "PO Product Code" of table 1 and "Product Code" of table 2. Each time I attempt to do this, there are a few product codes that are left out:

CABSCABS0000, DOORINTD0015, FLORCARP0001, EXCL0001, and FLORTILE0000

Each of these product codes are in table 2, but not in table 1.

Here are the screenshots of the tables I am working with.

https://i.stack.imgur.com/1XDMa.png

https://i.stack.imgur.com/CWSDw.png

And here is the code that I have tried:

SELECT cost.[Actual Close]
    ,cost.[Project Name]
    ,cost.[Lot]
    ,cost.[Model]
    ,cost.[Elev]
    ,cost.[PO Product Code]
    ,cost.[Invoiced + Open] AS 'Invoiced + Open'
    ,rev.[Gross Sale] AS 'Gross Sale'

FROM Table1 cost FULL OUTER JOIN
     Table2 rev
    ON rev.[Product Code] = cost.[PO Product Code]  
WHERE rev.[Project Name] = cost.[Project Name] AND 
      rev.[Lot] = cost.[Lot];

I must also mention that the Product Code is specific to each Project Name and Lot, which is why that is added to the 'WHERE' clause.

Here is the output I got, which is missing the 5 craft codes listed above.

https://i.stack.imgur.com/MQjD1.png

Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Jun 11 '19 at 00:53
Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. — philipxy, Jun 11 '19 at 00:57
Learn what FULL 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/both table column to be not NULL after an OUTER JOIN ON removes any rows from the table(s) extended by NULLs, ie leaves only LEFT/RIGHT/INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Jun 11 '19 at 04:40

score 2 · Answer 1 · answered Jun 11 '19 at 10:32

2

The FULL JOIN returns NULL values in columns for rows that do not match in either of the tables. Your WHERE clause filters these out -- because NULL comparisons almost never evaluate to "true" (the exception is IS NULL and NULL-safe comparisons).

What you want to do is move these conditions to the ON clause. That is where they belong anyway, because they are really JOIN conditions:

FROM Table1 cost FULL OUTER JOIN
     Table2 rev
     ON rev.[Product Code] = cost.[PO Product Code] AND 
        rev.[Project Name] = cost.[Project Name] AND 
        rev.[Lot] = cost.[Lot];

answered Jun 11 '19 at 10:32

Gordon Linoff

1,242,037
58
646
786

I have just tried this but it still seems to leave out the 5 Product Codes. Maybe there is some essential information that I left out during simplification of the question. In both tables 1 and 2, there are multiple lines with the same Product Code, but with different 'Project Names' and 'Lot' number. These lines are just hidden because I filtered the results to a specific 'Project Name' and 'Lot' number. Can you possibly think of any reason why I might still be having this issue? Anything will help. Thanks. – Zach G Jun 11 '19 at 16:26

Mr.J · Answer 2 · 2019-06-11T00:45:45.680

1

Its because of your where clause

rev.[Project Name] = cost.[Project Name]
AND rev.[Lot] = cost.[Lot];

Since those items that you mentioned are not in table A but are in table B, you are basically removing them via WHERE clause.

If you want those 5 Items to show, try removing the where clause, then run the query again.

edited Jun 11 '19 at 00:45

answered Jun 11 '19 at 00:13

Mr.J

430
2
10
30

. . And the solution is? – Gordon Linoff Jun 11 '19 at 00:30
@GordonLinoff The question does not give a coherent specification, or even a coherent question, so how could code doing it that's not a guess be given? The title mentions "not showing unmatched rows" & this answer addresses that. Although poorly. – philipxy Jun 11 '19 at 04:47
You don't explain "because of" or "since"--how "those items that you mentioned are not in table A but are in table B" implies that "you are basically removing them via WHERE clause". Also the latter doesn't mean anything. A "basically" or "essentially" or "in other words" that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly". (Compare to my (terse) comment on the question.) – philipxy Jun 11 '19 at 04:50
@philipxy, so its still not clear? I used "because of" then put the code so that I can point out the where clause, Then "those items that you mentioned are not in table A but are in table B" is basically the items that are in `table B` but are not in `table A`... Thank you for your inputs, if you don't mind me asking, how can I improve my answer? – Mr.J Jun 11 '19 at 05:10
Re "how can I improve my answer?" Explain in small steps why X implies Y. Replace the sentence using "basically" with a sentence that says something. Read your answer and see what you actually wrote. Reread what my last comment says about your "Since ..." sentence. PS I don't understand why you write "so its still not clear?" when I just said it is not clear & said why or why you repeat in your comment the part of your answer that I just said was not clear. PS You clearly don't think you are clear enough because you stuck "basically" into a sentence instead of making it clear. – philipxy Jun 11 '19 at 05:18
@philipxy English is not my mother tongue so I always get corrected when I thought my thoughts and ideas and sentence construction is perfectly understandable. Thank you for your inputs, I will reread my answer, and understand my mistakes. – Mr.J Jun 11 '19 at 07:40
Your use of English is not the problem. That's clear from my comments. The problem is what you have not written. "Read your answer and see what you actually wrote." Good luck. – philipxy Jun 11 '19 at 07:45

Full outer join not showing unmatched rows - SQL

2 Answers2