Co0mparing two tables in mysql

I would like to compare two tables.  I need to compare multiple columns and count them.  I will need to compare upto 6 columns.  I found this query but have been unable to modify it for multiple columns.

SELECT * FROM tblA
 WHERE tblA.ID
 NOT IN (
 SELECT tblB.ID
 FROM tblB
 WHERE tblA.ID=tblB.ID
 )
ralphs1961Asked:
Who is Participating?
 
johanntagleConnect With a Mentor Commented:
If what you need are the same, the query is different:

select count(a.primary_key_column) from table1 a join table2b
on (a.col1=b.col1 and a.col2=b.col2 and .... a.col6=b.col6);

a.primary_key_column can actually be any column, but I usually recommend the primary key, especially if it's innodb.
0
 
nishant joshiConnect With a Mentor Technology Development ConsultantCommented:
means you want to need that record from A table which not in B table.
Then it is advisable to use outer join.

SELECT * FROM tblA a
Outer Join tblB b
ON a.ID <> b.ID --AND more colums

Open in new window


Regards,
nishant
0
 
johanntagleCommented:
Agree with Nishant that an outer join is what is needed, except I usually do it this way:

select a.* from table1 a left outer join table2b
on (a.col1=b.col1 and a.col2=b.col2 and .... a.col6=b.col6)
where b.pimary_key_column is null;
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
ralphs1961Author Commented:
Thanks for the responses and I am going to try both.

It seems I made a mistake in my original question, what I am after is a count of rows that are the same.
0
 
ralphs1961Author Commented:
Thanks very much you both, I ran the query and it worked, It took 1/2 hour to complete, is there anyway to make it faster?  3,000 rows being compared to 14 million rows...  

I do have indexes setup but if you have any ideas please let me know.  If you think this last question should be a new question please let me know.
0
 
johanntagleCommented:
See if a concatenated index with all 6 columns will help.  Make sure you have them on both tables.  If it still doesn't help, please post output of EXPLAIN <query>
0
 
nishant joshiTechnology Development ConsultantCommented:
;with t1(cnt,id) as 
(
SELECT 1,a.ID FROM tblA a
Outer Join tblB b
ON a.ID <> b.ID
group by a.ID
)
;with t2 as
(
SELECT cnt+1,id from t1
)
select cnt from t2

Open in new window

use recurcive cte..
Regards,
0
 
johanntagleCommented:
Nishant, does mysql already support that?  Last time I checked it still lagged behind the SQL standard and won't accept that yet.
0
 
ralphs1961Author Commented:
I would like to thank both of you for your help.  I am going to award johanntagle 400 points as he made the suggestion of concatenating the columns into a index.  Turned a half hour query into 5 seconds.  Thanks.  Both of the original queries worked and worked well with out the memory out error I was getting with the first query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.