Data in table 1 which is not in table 2

July 18, 2022

I want to find the ResumeDID which are in ResumeActivityLog1 but not in ResumeActivityLog2
for example e,f.

FIDDLE – https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1ae5bc7640d4fafdffee3f97edaab6fc

why is this query giving empty results

select *
from `ResumeActivityLog1` p
where not exists (
  select * from `ResumeActivityLog2` q
  where q.CreatedOn = p.CreatedOn
);

use these statements for creating this scenario

-- create table
CREATE TABLE ResumeActivityLog1 ( ResumeDID VARCHAR(255), CreatedOn  VARCHAR(255));
CREATE TABLE ResumeActivityLog2 ( ResumeDID VARCHAR(255), CreatedOn  VARCHAR(255));

-- create procedure
CREATE  PROCEDURE `SaveMultipleResumeActivityLog1`(
    _resumeDIDs           Text
)
    INSERT INTO `ResumeActivityLog1` ( `ResumeDID`, `CreatedOn`)
    SELECT ResumeDID,"2021"
    FROM JSON_TABLE(CONCAT('["', REPLACE(_resumeDIDs, ',', '","'), '"]'),
                    '$[*]' COLUMNS (ResumeDID VARCHAR(255) PATH '$')) jsontable;

-- create procedure
CREATE  PROCEDURE `SaveMultipleResumeActivityLog2`(
    _resumeDIDs           Text
)
    INSERT INTO `ResumeActivityLog2` ( `ResumeDID`, `CreatedOn`)
    SELECT ResumeDID,"2021"
    FROM JSON_TABLE(CONCAT('["', REPLACE(_resumeDIDs, ',', '","'), '"]'),
                    '$[*]' COLUMNS (ResumeDID VARCHAR(255) PATH '$')) jsontable;
-- call procedure with CSV as a parameter
CALL SaveMultipleResumeActivityLog1('a,b,c,d,e,f');
CALL SaveMultipleResumeActivityLog2('a,b,c,x,y');

>Solution :

You have not correlated the rows in the subquery – as written all rows exist purely for CreatedOn and your query is essentially saying where not (true)

select *
from ResumeActivityLog1 p
where not exists (
  select * from ResumeActivityLog2 q
  where q.CreatedOn = p.CreatedOn and q.ResumeDID = p.ResumeDID
);