MySQL Database learning



Home / Tricky Select Queries / How to compare data of two tables of two different databases in MySQL?
Share this job:


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.
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;




Tricky Select Queries related other questions

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

This can be possible. Example your table is test, and the column is test_col. Use the following, and if the number is always at the end and always has a space in front of it this will work:SELECT test_col, ...

How to take data back up of single table?

You may try this to get data backup of a table:CREATE TABLE backup_MyTable SELECT * FROM MyTable; This builds a backup table without the indexes so you can easily copy the data back using an ...

How to find duplicate records from a table in MySQL?

Something like the following query should help you find all of the duplicate records:SELECT emp_email_address FROM table GROUP BY emp_email_address HAVING COUNT(*)>1; ...

How to retreive multiple records without a reference table?

To retrieve multiple records without a reference table, you can use UNION operator provided by MySQL. It may serve your goal.  Take an example:SELECT \'val_1\' AS var1, \'val_2\' AS var2 UNION ...

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

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 jus ...

How to get last top records in asceding order?

To get last top records using LIMIT in MySQL also in ascending order is possible. You may amend your above query as below. The trick will serve your purpose:(SELECT emp_id, emp_name FROM employ ORDER BY emp ...

How to move table from one to another MySQL database?

In MySQL you can do this by different methods. By first of the following examples, you can also move multi tables from one to another database in one go. Take an example:RENAME TABLE current_db.ta ...

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

You may use cascading joins as follow, which can serve your purpose:SELECT de_1.dept_name AS \'Department Name\', de_2.total_employee AS \'Total Employees\', eb.total_bonuses AS \'Tota ...

How to skip repeating values from the query results?

Repeating values can be skipped using GROUP BY in the query as follow:SELECT col_1, col_2, col_3, .... FROM  table_name  GROUP BY col_1; ...

How to retrieve one child row against each parent id?

For this MySQL provides GROUP BY which also works even when the SELECT does not specify aggregate function. So the GROUP BY can also be used as follow:SELECT d.dept_id department_id, e.emp_id AS employee_i ...

An important trick to speed up select query.

Actually, using this query MySQL seems reading every single buyer record. The query part \"buyer_discount/2\" causing the delay to respond. Every buyer_discount (every ...

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

This is can be done through creating drive table of author_books table and then joining the both. First part of the query will find the authors with multiple books. Then join the drived table to that on ...

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

You are doing right but one thing is missing.  You can correct your query only to escape the apostrophe.  I will rewrite your query like below:SELECT COUNT(*) FROM my_table WHERE field_name ...