top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to compare two tables in MYSQL?

0 votes
556 views
How to compare two tables in MYSQL?
posted Jun 13, 2017 by anonymous

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

3 Answers

+1 vote

This query it seems has two portions -
1. If the structure of both the tables is same
2. If structure is same then is the contents of both the table same.

  1. If the structure of both the tables is same: following query will show the actual diff between table1 and table2 -

    SELECT column_name,ordinal_position,data_type,column_type FROM
    (
    SELECT
    column_name,ordinal_position,
    data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=''
    AND table_name IN ('

    ','') GROUP BY column_name,ordinal_position, data_type,column_type HAVING COUNT(1)=1 ) A;
  2. If structure is same then is the contents of both the table same: Assuming in the step 1 you find both the table structure is same then just find out the content of both are same or not (picking what Ajay has suggested) -

    SELECT First_table.*
    FROM First_table
    LEFT OUTER JOIN Second_table
    ON First_table.id = Second_table.id
    WHERE Second_table.id IS NULL

answer Jul 3, 2017 by Salil Agrawal
0 votes

SELECT First_table.*
FROM First_table
LEFT OUTER JOIN Second_table
ON First_table.id = Second_table.id
WHERE Second_table.id IS NULL

answer Jul 2, 2017 by Ajay Kumar
How will this solve the given problem
0 votes

Hi, You can try like this:

SELETC table1.*
FROM table1
LEFT OUTER JOIN table2
ON table1.id = table2.id
WHERE table2.id IS NULL

LEFT OUTER JOIN link two table starting by table1 , if table 2 has no link row all fields of table 2 will be null.if you put in your WHERE condition table2.id is null, you get only rows in table1 not existing in table2.

answer Aug 30, 2019 by Siddhi Patel
Similar Questions
0 votes

How can I insert data into one table from two other tables where i have three tables namely users, role and userrole.
Now I want to insert the data into userrole table from users table and role table with a single statement.

+1 vote

Joiner using how to get three tables value fetching from single query.

...