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

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

CodePudding user response：

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';

CodePudding user response：

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 is also valid, it basically depends on what you want to do.