Looking for duplicate values between 2 tables sql

Question

I have 2 tables, and the 2 tables has the concatenate value of 2 columns. For example,

column1| column2| concatenate value
   A         B             AB

Now, when I count the the 2 table, the value on the 2nd table(newly created table) appear to have more value than original table. What can I do to find out which value is the extra?

I had tried, but the code does not work:

SELECT concat (fullVisitorId, cast(visitId as string)) as fullvisitor_visitId_tableA 
FROM  `ga_sessions_*` 
where _table_suffix between '20220101' and '20220130'
UNION all
SELECT concat (fullVisitorId, cast(visitId as string)) as fullvisitor_visitId_tableB 
FROM  `test_ps_ga360_total_3`
EXCEPT 
SELECT concat (fullVisitorId, cast(visitId as string)) as fullvisitor_visitId_tableA 
FROM  `ga_sessions_*` 
where _table_suffix between '20220101' and '20220130'
INTERSECT
SELECT concat (fullVisitorId, cast(visitId as string)) as fullvisitor_visitId_tableB 
FROM `test_ps_ga360_total_3`;

Any insights would be greatly appreciated.

Answer 1

So I had to solve a problem similar to this once. I would add another column to Table 1 (columns 1 & 2) that is your concatenated column (Column 3) and then do a left join on that to the other table that you are looking for the missing values from.

Step 1:

ALTER TABLE `ga_sessions_*` 
ADD column_3 varchar(250);

Step 2:

UPDATE `ga_sessions_*` SET column_3 = CONCAT(column_1,column_2);

Step 3:

Then do a LEFT JOIN on column_3 & concatenate value (AB) WHERE column_3 is null

Should give you the output of all the extra values in AB