Advertisement: Jobs in UAE, Saudi Arabia, Qatar, Pakistan, Bahrain & other regions. Click here to find one for you.

MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
Powered by MySQL
 
Home / Tricky Select Queries / Question No: 55

How to compare data of two tables of two different databases in MySQL?

I want to compare data of my two tables of two different databases in MySQL at the same server. Any idea to write query which helps to get compared two tables data? I need to know unique records of both the tables.

Answer No: 55

The UNION operator in MySQL can help you to compare data of two tables of two different databases. The following query unions the queries for matching column names and their values from two tables and keeps just those rows which occur once in the each table. Those are the rows unique to one table or the other. In your SELECT you would customize your columns something like { customer_id, customer_fname, customer_lname, customer_email, customer_phone, ...} or as desired:
SELECT 
MIN(customer) AS customer, customer_id, customer_fname, customer_lname,
customer_email, customer_phone
FROM (

SELECT 'customer_table_first_db' AS customer, first_table.customer_id,
first_table.customer_fname, first_table.customer_lname,
first_table.customer_email,first_table.customer_phone
FROM first_db.customer AS first_table

UNION ALL

SELECT 'customer_table_second_db' AS customer,second_table.customer_id,
second_table.customer_fname, second_table.customer_lname,
second_table.customer_email, second_table.customer_phone
FROM second_db.customer AS second_table

) AS temp_table
GROUP BY customer_id, customer_fname, customer_lname,
customer_email, customer_phone
HAVING COUNT(*) = 1
ORDER BY customer,customer_id;
To get similar records from both the tables, you can change the above query as below:
SELECT 
MIN(customer) AS customer, customer_id, customer_fname,
customer_lname, customer_email, customer_phone
FROM (
SELECT 'customer_table_first_db' AS customer,
first_table.customer_id,first_table.customer_fname,
first_table.customer_lname,first_table.customer_email,
first_table.customer_phone
FROM first_db.customer AS first_table

UNION ALL
SELECT 'customer_table_second_db' AS customer, second_table.customer_id, second_table.customer_fname, second_table.customer_lname, second_table.customer_email, second_table.customer_phone
FROM second_db.customer AS second_table
) AS temp_table
GROUP BY customer_id, customer_fname, customer_lname,
customer_email, customer_phone
HAVING COUNT(*) > 1
ORDER BY customer,customer_id;

Related MySQL FAQs to the Above FAQ

How-to-do-numeric-sorting-within-a-string-field-in-MySQL How to do numeric sorting within a string field in MySQL?

How-to-take-data-back-up-of-single-table How to take data back up of single table?

How-to-find-duplicate-records-from-a-table-in-MySQL How to find duplicate records from a table in MySQL?

How-to-get-last-top-records-in-asceding-order How to get last top records in asceding order?

How-to-move-table-from-one-to-another-MySQL-database How to move table from one to another MySQL database?

How-to-get-parent-table-child-table-and-grandchild-table-aggregates-in-MySQL How to get parent table child table and grandchild table aggregates in MySQL?

How-to-skip-repeating-values-from-the-query-results How to skip repeating values from the query results?

How-to-retrieve--one-child-row-against-each-parent-id How to retrieve one child row against each parent id?

An-important-trick-to-speed-up-select-query An important trick to speed up select query.

How-to-find-the-books-which-have-authors-with-more-than-one-book-in-the-table How to find the books which have authors with more than one book in the table?

How-to-count-the-number-of-rows-containing-APOSTROPHE-in-MySQL How to count the number of rows containing APOSTROPHE in MySQL?

How-to-retreive-multiple-records-without-a-reference-table How to retreive multiple records without a reference table?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Sulata iSoft - Limitless Solutions | Limitless Job Opportunities - Careermidway.com

© 2007 - 2014  www.mysqlfaqs.net
All rights reserved. The site is owned and operated by Mr. Javed Bhatti - Pakistan.