Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to find the difference set of mysql". In the daily operation, I believe that many people have doubts about how to find the difference set of mysql. The editor consulted all kinds of data and sorted out a simple and easy-to-use operation method. I hope it will be helpful to answer the doubt of "how to find the difference set of mysql"! Next, please follow the editor to study!

The method of mysql difference set: 1, use not exists to filter the difference set of two tables; 2, filter the difference set of two tables through the empty field value generated after LEFT JOIN connection.

This article operating environment: windows7 system, mysql8.0 version, Dell G3 computer.

How does mysql find the difference set?

How does mysql query records with inconsistent data in two tables with different number of fields

Generally, you can use the empty field values generated by NOT EXISTS (non-existent clause) or LEFT JOIN left (right) join to filter the difference between the two tables.

1 、 NOT EXISTS

Not exists is very efficient when there is an index available for comparison fields, but it is extremely inefficient when running on a large data table without an index, so you should avoid using it.

SELECT * FROM smd_employee T1 WHERE NOT EXISTS (SELECT 1 FROM asd_user_account T2 WHERE t2.u_phone = t1.employee_phone)

2 、 LEFT JOIN

Use the left (right) join to find the difference set, because the need to implement two table joins will lead to the Cartesian effect, and the number of record rows in its output set may increase.

If it is not one-to-one or one-to-many, we should deal with the many-to-many situation as many-to-one before joining, otherwise the output recordset may be incorrect.

SELECT a.* FROM smd_employee a LEFT JOIN asd_user_account b ON b.u_phone = a.employee_phone WHERE b.u_phone IS NULL

For the two methods of finding difference sets, when an index is available, the efficiency of not exists is higher than that of left join, on the contrary, left join is better.

At this point, the study of "how to find the difference set of mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.