Why FULL OUTER JOIN 2 Tables not returning all rows of those tables

Question

I have 2 tables like below
Table Demographics2

CREATE TABLE Demographics2
(Date date,
 FirstID int,
 SecondID int,
 FirstName varchar(50),
 LastName varchar(50), 
 DCode int
 )
 ;

Insert into Demographics2 VALUES
('20200402', 342, 812, 'John', 'Smith', 823),
('20200414', 263, 812, 'Jane', 'Austen', 676), 
('20200414', 263, 812, 'Jane', 'Austen', 556)
;

Table Item2

CREATE TABLE Item2
(Date date,
 FirstID int,
 SecondID int,
 ItemName varchar(50),
 ItemForm varchar(50),
 ICode int
 )
;

Insert into Item2 VALUES
('20200318', 754, 812, 'Perindo', 'Blue', 169),
('20200318', 754, 812, 'Perindo', 'Blue', 156),
('20200318', 754, 812, 'Lipitor', 'Blue', 295),
('20200318', 754, 812, 'Perindo', 'Blue', 627),
('20200318', 754, 812, 'Perindo', 'Blue', 740),
('20200318', 754, 812, 'Metformin', 'Green', 992),
('20200414', 263, 812, 'Pred', 'Blue', 234),
('20200414', 263, 812, 'Pred', 'Blue', 279),
('20200414', 263, 812, 'Phen', 'Blue', 605),
('20200414', 263, 812, 'Pred', 'Blue', 979),
('20200414', 263, 812, 'Phen', 'Blue', 513),
('20200414', 263, 812, 'Pred', 'Blue', 127)

When I FULL OUTER JOIN 2 tables, with time filter in WHERE, the result I got lacked the rows of Date 20200318 of Table Item2

SELECT *
FROM master.[dbo].[Demographics2] d
     FULL OUTER JOIN master.[dbo].[Item2] i
     ON d.FirstID = i.FirstID
WHERE CONVERT(date,d.Date) BETWEEN '2020-03-01 00:00:00.000' AND '2020-05-01 00:00:00.000'

-- Result

 Date     FirstID SecondID  FirstName   LastName DCode  Date    FirstID SecondID    ItemNameItemFormICode
2020-04-02  342   812      John         Smith   823     NULL       NULL NULL        NULL    NULL    NULL
2020-04-14  263   812      Jane         Austen  676     2020-04-14  263 812         Pred    Blue    234
2020-04-14  263   812      Jane         Austen  676     2020-04-14  263 812         Pred    Blue    279
2020-04-14  263   812      Jane         Austen  676     2020-04-14  263 812         Phen    Blue    605
2020-04-14  263   812      Jane         Austen  676     2020-04-14  263 812         Pred    Blue    979
2020-04-14  263   812      Jane         Austen  676     2020-04-14  263 812         Phen    Blue    513
2020-04-14  263   812      Jane         Austen  676     2020-04-14  263 812         Pred    Blue    127
2020-04-14  263   812      Jane         Austen  556     2020-04-14  263 812         Pred    Blue    234
2020-04-14  263   812      Jane         Austen  556     2020-04-14  263 812         Pred    Blue    279
2020-04-14  263   812      Jane         Austen  556     2020-04-14  263 812         Phen    Blue    605
2020-04-14  263   812      Jane         Austen  556     2020-04-14  263 812         Pred    Blue    979
2020-04-14  263   812      Jane         Austen  556     2020-04-14  263 812         Phen    Blue    513
2020-04-14  263   812      Jane         Austen  556     2020-04-14  263 812         Pred    Blue    127

Is there any way of putting time filter in WHERE clause but still getting all the rows of 2 tables
(My expected result I got from FULL OUTER JOIN 2 tables without time filter in WHERE clause)

-- Expected result
    Date    FirstID SecondID    FirstName   LastName    DCode   Date    FirstID SecondID    ItemName    ItemForm    ICode
2020-04-02  342     812         John        Smith       823     NULL       NULL NULL       NULL         NULL    NULL
2020-04-14  263     812         Jane        Austen      676     2020-04-14  263 812        Pred         Blue    234
2020-04-14  263     812         Jane        Austen      676     2020-04-14  263 812        Pred         Blue    279
2020-04-14  263     812         Jane        Austen      676     2020-04-14  263 812        Phen         Blue    605
2020-04-14  263     812         Jane        Austen      676     2020-04-14  263 812        Pred         Blue    979
2020-04-14  263     812         Jane        Austen      676     2020-04-14  263 812        Phen         Blue    513
2020-04-14  263     812         Jane        Austen      676     2020-04-14  263 812        Pred         Blue    127
2020-04-14  263     812         Jane        Austen      556     2020-04-14  263 812        Pred         Blue    234
2020-04-14  263     812         Jane        Austen      556     2020-04-14  263 812        Pred         Blue    279
2020-04-14  263     812         Jane        Austen      556     2020-04-14  263 812        Phen         Blue    605
2020-04-14  263     812         Jane        Austen      556     2020-04-14  263 812        Pred         Blue    979
2020-04-14  263     812         Jane        Austen      556     2020-04-14  263 812        Phen         Blue    513
2020-04-14  263     812         Jane        Austen      556     2020-04-14  263 812        Pred         Blue    127
NULL    NULL        NULL        NULL        NULL        NULL    2020-03-18  754 812     Perindo         Blue    169
NULL    NULL        NULL        NULL        NULL        NULL    2020-03-18  754 812     Perindo         Blue    156
NULL    NULL        NULL        NULL        NULL        NULL    2020-03-18  754 812     Lipitor         Blue    295
NULL    NULL        NULL        NULL        NULL        NULL    2020-03-18  754 812     Perindo         Blue    627
NULL    NULL        NULL        NULL        NULL        NULL    2020-03-18  754 812     Perindo         Blue    740
NULL    NULL        NULL        NULL        NULL        NULL    2020-03-18  754 812   Metformin        Green    992

Does this answer your question? [Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?](https://stackoverflow.com/questions/55094277/is-it-true-that-using-inner-join-after-any-outer-join-will-essentially-invalidat) — philipxy, Oct 12 '22 at 23:17
Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] PS Minimal example data please. — philipxy, Oct 12 '22 at 23:21
See [Condition within JOIN or WHERE](https://stackoverflow.com/q/1018952/3404097) [Left Join With Where Clause](https://stackoverflow.com/q/4752455/3404097) [Right Join not returning all expected rows](https://stackoverflow.com/q/18784519/3404097) etc etc. — philipxy, Oct 12 '22 at 23:32
@philipxy Thanks for sharing the above. I did my research and did read those posts. I can understand my problem coming from the Where clause but I do not know how to fix it. That's why I post my question here — Shichimi, Oct 12 '22 at 23:54
Please act on my comments: It is not helpful to give wrong code when you are stuck getting to a goal, give clear specifications & relevant parts you can do. See my answer at the link re inner join after outer. Including its link [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097). — philipxy, Oct 13 '22 at 00:05

score 1 · Answer 1 · answered Oct 12 '22 at 23:06

The WHERE clause filters out records present only in the second table, for those records d.Date is NULL.

You can use OR d.Date IS NULL to include those records too:

SELECT *
FROM master.[dbo].[Demographics2] d
FULL OUTER JOIN master.[dbo].[Item2] i
     ON d.FirstID = i.FirstID
WHERE (CONVERT(date,d.Date) BETWEEN '2020-03-01 00:00:00.000' AND '2020-05-01 00:00:00.000')
     OR d.Date IS NULL

@TimBiegeleisen's answer is also valid, it basically depends on what you want to do.

Thanks for your suggested solution. It produces the same results as the one by Tim's code — Shichimi, Oct 13 '22 at 00:03

score 0 · Accepted Answer · answered Oct 12 '22 at 23:04

0

You may try logically filtering on both dates with the help of COALESCE():

SELECT *
FROM master.[dbo].[Demographics2] d
FULL OUTER JOIN master.[dbo].[Item2] i
     ON d.FirstID = i.FirstID
WHERE CONVERT(date, COALESCE(d.Date, i.Date)) BETWEEN '2020-03-01' AND '2020-05-01';

answered Oct 12 '22 at 23:04

Tim Biegeleisen

502,043
27
286
360

Many thanks for your concise code. Completely solved my issue. – Shichimi Oct 13 '22 at 00:01

Why FULL OUTER JOIN 2 Tables not returning all rows of those tables

2 Answers2