Full Outer Join failing to return all records from both tables-CodePudding

I have a pair of tables I need to join, I want to return any record that's in tableA, tableB or both. I think I need a FULL OUTER JOIN

This query return 1164 records

SELECT name FROM tableA 
WHERE reportDay = '2022-Apr-05'

And this one return 3339 records

SELECT name FROM tableB 
WHERE reportDay = '2022-Apr-05'

And this one returns 3369 records (so there must be 30 records in tableA that aren't in tableB)

select distinct name FROM tableA where reportDay = '2022-Apr-05'
union distinct
select distinct name FROM tableB where reportDay = '2022-Apr-05'

I want to obtain a list of all matching records in either table. The query above returns 3369 records, so a FULL OUTER JOIN should also return 3369 rows (I think). My best effort so far is shown below. It returns 1164 rows and returns what looks to me to be a left join between tableA and tableB.

SELECT tableA.name.*, tableB.name.* 
FROM tableA
FULL OUTER JOIN tableB 
ON (tableA.name = tableB.name and tableB.reportDay = '2022-Apr-05')
WHERE tableA.reportDay = '2022-Apr-05'

Help appreciated. (if this looks question looks familiar, it's a follow-on question to this one )

UPDATE - Sorry (@forpas) to keep moving the goalposts - I'm trying to match test data to real-data scenario's.

DROP TABLE tableA;
DROP TABLE tableB;

CREATE TABLE tableA (name         VARCHAR(10),
  reportDay    DATE, 
  val1         INTEGER,
  val2         INTEGER);

CREATE TABLE tableB (name         VARCHAR(10),
  reportDay    DATE, 
  test1        INTEGER,
  test2        INTEGER);

INSERT INTO tableA values  ('A','2022-Apr-05',1,2),  
('B','2022-Apr-05',3,4), ('C','2022-Apr-05',5,6), 
('A','2022-Apr-06',1,2), ('B','2022-Apr-06',3,4), 
('C','2022-Apr-06',5,6), ('Z','2022-Apr-04',5,6), 
('Z','2022-Apr-06',5,6) ;

INSERT INTO tableB values  ('A','2022-Apr-03',5,6), 
('B','2022-Apr-04',11,22), ('B','2022-Apr-05',11,22),  
('C','2022-Apr-05',33,44), ('D','2022-Apr-05',55,66), 
('B','2022-Apr-06',11,22), ('C','2022-Apr-06',33,44), 
('D','2022-Apr-06',55,66), ('Q','2022-Apr-06',5,6);

SELECT tableA.*, tableB.* 
FROM tableA
FULL OUTER JOIN tableB 
ON (tableA.name = tableB.name and tableB.reportDay = '2022-Apr-05'
AND  tableA.reportDay = '2022-Apr-05' )

For this data, I'd hope to see 4 rows of data 'A' from tableA only, 'B' and 'C' from both tables, and 'D' from table B only. I'm after the 5th April records only! The query (shown above) suggested by @forpas works except that the 'A' record in tableA doesn't get returned.

UPDATE - FINAL EDIT AND ANSWER!

Ok, the solution seem to be to concetenate the two fields together before joining....

SELECT a.*, b.* 
FROM tableA a FULL OUTER JOIN tableB b 
ON (b.name || b.reportDay) = (a.name || a.reportDay)
WHERE (a.reportDay = '2022-Apr-05' OR a.reportDay IS NULL)
  AND (b.reportDay = '2022-Apr-05' OR b.reportDay IS NULL);

CodePudding user response：

The condition for the date should be placed in a WHERE clause:

SELECT a.*, b.* 
FROM tableA a FULL OUTER JOIN tableB b 
ON b.name = a.name AND a.reportDay = b.reportDay 
WHERE '2022-Apr-05' IN (a.reportDay, b.reportDay);

or:

SELECT a.*, b.* 
FROM tableA a FULL OUTER JOIN tableB b 
ON b.name = a.name
WHERE (a.reportDay = '2022-Apr-05' OR a.reportDay IS NULL)
  AND (b.reportDay = '2022-Apr-05' OR b.reportDay IS NULL);

See the demo.