How to compare two tables in MYSQL?

Question

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.

Answer 1

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 2

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

Comments

How will this solve the given problem

Answer 3

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.