Home / Tricky Select Queries / Question No: 55

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;