I have table A

Table B

Note: A and B has date values but different column names ( 'dt' for Table A and 'date' for table B)

i want id1 column values from Table A which are not in Table B and Vice Versa for the date "2022-04-10".

Desired output:

steps taken so far: select id1 from A where dt="2022-04-10" EXCEPT select id1 from B result ( its running for a long time and timing out, not sure its because of Volume of Data or the query is wrong) will check with smaller tables.

I can't use Left Inner Join because Date columns are different ( date vs dt).

Any help would be much appreciated.

CodePudding user response：

You can do it by applying a UNION statement between a left joined and a right joined table. From these joins your targets are those rows whose second table values is NULL because of no match:

SELECT tableA.id1,
       tableA.dt
FROM      tableA 
LEFT JOIN tableB
       ON tableA.id1 = tableB.id1 
      AND tableA.dt = tableB.dt
WHERE tableB.id1 IS NULL
  AND tableA.dt = '2022-04-10' 

UNION 

SELECT tableB.id1,
       tableB.dt
FROM      tableB 
LEFT JOIN tableA
       ON tableB.id1 = tableA.id1 
      AND tableB.dt = tableA.dt
WHERE tableA.id1 IS NULL
  AND tableB.dt = '2022-04-10'

Find the SQL Fiddle here.

CodePudding user response：

I'm not completely sure of your output. But I think you want something like this:

bind_rows(
  anti_join(rename(a,date=dt) %>% filter(date=="2022-04-10"),b %>% filter(date=="2022-04-10")),
  anti_join(b %>% filter(date=="2022-04-10"),rename(a,date=dt) %>% filter(date=="2022-04-10"))
)

Output:

   id1       date
1:  x1 2022-04-10
2:  a2 2022-04-10
3:  x2 2022-04-10
4:  y2 2022-04-10
5:  a1 2022-04-10
6:  c3 2022-04-10
7:  b1 2022-04-10