Missing some rows when using FULL OUTER JOIN

Question

I'm trying to join two tables with a FULL OUTER JOIN but I'm not getting what I expected because there is a a row missing.

I'm trying to join both table in 3 different columns.

Table A:

CallId      ASId    DateTime    CallStatus     DurationSeconds
21280070    NULL    17/07/2019  in_queue        1
21280070    2099726 17/07/2019  agent_dialing   3
21280070    2099726 17/07/2019  agent_ringing   3
21280070    2099726 17/07/2019  speaking_agent  95
21280070    NULL    17/07/2019  in_queue        1
21280070    2098692 17/07/2019  agent_dialing   1
21280070    2098692 17/07/2019  agent_ringing   6
21280070    2098692 17/07/2019  speaking_agent  10

Table B:

B.ASId  B.CallId    B.CallDetails                   B.DateTime  B.Duration
2099726 21280070    dialing                         17:21:41    3
2099726 21280070    ringing                         17:21:44    3
2099726 21280070    incoming_call_in_conversation   17:23:19    95
2098692 21280070    dialing                         17:23:21    1
2098692 21280070    ringing                         17:23:27    6
2098692 21280070    incoming_call_in_conversation   17:23:37    10
2098692 21280070    wrapup                          17:23:57    20

I want to get something like this:

A.CallId    A.ASId  A.DateTime  A.CallStatus    A.DurationSeconds       B.ASId  B.CallId    B.CallDetails   B.DateTime  B.Duration
21280070    NULL    17:21:38    in_queue        1                       NULL    NULL        NULL            NULL    NULL
21280070    2099726 17:21:41    agent_dialing   3                       2099726 21280070    dialing         17:21:41    3
21280070    2099726 17:21:44    agent_ringing   3                       2099726 21280070    ringing         17:21:44    3
21280070    2099726 17:23:19    speaking_agent  95                      2099726 21280070    incoming_call   17:23:19    95
21280070    NULL    17:23:20    in_queue        1                       NULL    NULL        NULL            NULL     NULL
21280070    2098692 17:23:21    agent_dialing   1                       2098692 21280070    dialing         17:23:21    1
21280070    2098692 17:23:27    agent_ringing   6                       2098692 21280070    ringing         17:23:27    6
21280070    2098692 17:23:37    speaking_agent  10                      2098692 21280070    incoming_call   17:23:37    10
NULL        NULL    NULL        NULL            NULL                    2098692 21280070    wrapup          17:23:57    20

I've tried following code:

SELECT *
FROM table a
FULL OUTER JOIN table b 
ON a.CallId = b.CallId AND a.ASId = b.ASId AND a.DateTime = b.DateTime
WHERE a.CallId = 21280070 
ORDER BY a.DateTime

And I'm getting one row less that I expected, the last one with wrapup:

A.CallId    A.ASId  A.DateTime  A.CallStatus    A.DurationSeconds       B.ASId  B.CallId    B.CallDetails   B.DateTime  B.Duration
21280070    NULL    17:21:38    in_queue        1                       NULL    NULL        NULL            NULL    NULL
21280070    2099726 17:21:41    agent_dialing   3                       2099726 21280070    dialing         17:21:41    3
21280070    2099726 17:21:44    agent_ringing   3                       2099726 21280070    ringing         17:21:44    3
21280070    2099726 17:23:19    speaking_agent  95                      2099726 21280070    incoming_call   17:23:19    95
21280070    NULL    17:23:20    in_queue        1                       NULL    NULL        NULL            NULL     NULL
21280070    2098692 17:23:21    agent_dialing   1                       2098692 21280070    dialing         17:23:21    1
21280070    2098692 17:23:27    agent_ringing   6                       2098692 21280070    ringing         17:23:27    6
21280070    2098692 17:23:37    speaking_agent  10                      2098692 21280070    incoming_call   17:23:37    10

Does anyone have an idea why this is happening? I'm not able to understand why last row is not showing.

Many thanks in advance.

Kind regards!

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. (This is a faq.) — philipxy, Jul 19 '19 at 01:00

Thom A · Answer 1 · 2019-07-18T16:52:12.383

2

Because of a.CallId = 21280070 . Because when a JOIN isn't successful a.CallId will have a value of NULL and this clause won't be true.

At a guess, you probably want one of these:

WHERE 21280070 IN (a.CallId,b.CallId)

WHERE a.CallId = 21280070
   OR b.CallId = 21280070

edited Jul 18 '19 at 16:52

answered Jul 18 '19 at 16:10

Thom A

88,727
11
45
75

1

Oh Lord! Many thanks man! You are right! What a stupid mistake I did. Really appreciated. – Charlyjauskas Jul 18 '19 at 16:50

Missing some rows when using FULL OUTER JOIN

1 Answers1