'compare two tables having same column name but different date column names

I have table A

id1 dt
x1 2022-04-10
a2 2022-04-10
a1 2022-04-10
x1 2022-05-10
x2 2022-04-10
y2 2022-04-10
y1 2022-05-10
x1 2022-06 -10

Table B

id1 dt
a1 2022-04-10
c3 2022-04-10
a1 2022-05-10
l1 2022-05-10
b1 2022-04-10
y2 2022-04-10
x1 2022-06-10
z1 2022-05-10

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

Desired output:

id1 DATE
x1 2022-04-10
a2 2022-04-10
x2 2022-04-10

Update 1: note: 1)y2,a1 are not in Desired output because they exists in both tables 2)c3 is not in desired output because it exists in Table B

id1 exists in both tables, but i want "id1" values only from Table A which are not in Table B for the date 2022-04-10.

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.

mysqlsqlrjoindatabricks


Solution 1:[1]

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.

EDIT

Want results only from table A:

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'

Only from table B?

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'

Solution 2:[2]

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

Solution 3:[3]

This worked as well

with A as (
select distinct id1
from Table_A
where dt = '2022-04-10'
)

, B as (
select distinct id1
from  Table_B
where date = '2022-04-10')

select id1 from A
where id1 not in (select id1 from B)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Somanath Patil

Related Questions