Check if values exists in other table?

Databases
1

I have:
Table A with 50k rows
Table B with 60k rows

How can I check if Table B rows match those in Table A?

Output result with rows that don’t match?

Apache mysql.

I am using this one on xampp but it takes ages to posses never seems to end.

SELECT  *   
FROM A.`city` A  
WHERE NOT EXISTS (SELECT *      
                  FROM b.`city` B
                  WHERE A.`name` = B.`name`);
2

Is this helpful?

image
image960×741 111 KB

3 Likes
3

You’re doing two complete table scans for that, and the SELECT * on both is not helping your cause, epecially if the tables are large. You could use a left outer join instead (the WHERE clause shows the NON matches).

SELECT A.Field1
     , A.Field2
  FROM A.city  A
  LEFT OUTER JOIN B.City B ON A.name = B.name
WHERE B.name is NULL
1 Like
4

Thanks guys that makes sense. appreciate it

5

Sorry last question. A.name values sometimes come separated by dashes how can I replace them with spaces?

LEFT OUTER JOIN B.City B ON replace(A.name,'-', ' ') = B.name
6

what happened when you tested that? ™

1 Like
7

Keeps on loading?

However when I try this works fast: LEFT OUTER JOIN B.City B ON A.name = replace(B.name,' ', '-')

8

so it’s not using an index

if this is a one-off query (i.e. you won’t be doing it on a regular basis), then clone the B table, update the name column, put an index on it, and use that in your query

9

Your post begs the question, why do you have duplicate data?

10

and your post invites the question, how do you know it’s duplicate data?

two tables, both with a city column, and they’re duplicate?

pretty sketchy inference, my dude

2 Likes
11

Apparently you didnt read the OP’s post. See how the OP said rows and not columns? I would think at your skill level you could tell the difference and know what the OP is talking about. The SQL the OP posted is more than clear he is comparing DATA. So the question still stands, OP why do you have duplicate data?

And I am not your dude.

12

so in the original post, why is he joining all those rows on only one column?

if he wanted actual duplicate rows, he’d have to compare all columns

13

I didn’t say the o p is looking for duplicate rows and neither did he. He is asking to show data that is in one table and not in another which means there can be duplicates. Also, the fact that there are two tables with a name column which would likely be a city name since it is in the city table says either his database is not normalized or he has some special case that he hasn’t mentioned yet. It would be best for the Op to tell us what the actual problem is he is trying to solve rather than asking us about his attempted solution to the problem.

14

It could be seen as “matching rows” as it was written or assumed to be a miscommunicated “rows with fields containing matching values”

Since there is some confusion regarding what was meant

15

Maybe we should simply ask OP why he needs this query instead of guessing what het ment. I’m arround since about 2011 but I increasingly get the feeling that so-called profesionals want to prove to each other how good they are, rather than what it really is all about at Sitepoint. Helping people who need help

2 Likes
16

I agree and in order to properly help the OP’s we need to know what the actual problem is they are trying to solve. Most often they ask for help on their attempt at solving the problem without telling us what the actual problem is.

1 Like