How to find the difference set in mysql

Mysql method to find the difference set: 1. Use not exists to filter the difference set of the two tables; 2. Filter the difference set of the two tables through the empty field value generated after the LEFT JOIN connection.

The operating environment of this article: Windows 7 system, mysql version 8.0, Dell G3 computer.

How to find the difference set in mysql?

How to query the records with inconsistent data in two tables with different numbers of fields in mysql

Generally, NOT EXISTS can be used ( non-existence clause) or LEFT JOIN left (right) connection to generate empty field values ​​to filter the difference between the two tables

1, NOT EXISTS

not exists When the comparison field has an available index, its operating efficiency is very high. However, if it is run on a large data table without an index, its operating efficiency is extremely poor. In this case, 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 left (right) join to find the difference set. Since the need to implement two table joins will lead to the Cartesian effect, the number of record rows in the output set may increase.

If it is not one-to-one or one-to-many, we should process the many-to-many situation into many-to-one before connecting, otherwise the output record set 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;

There are two ways to find the difference set. When there is an index available, the efficiency of not exists is higher than that of left join. On the contrary, left join is more efficient.

[Related recommendations: mysql video tutorial】

The above is the detailed content of How to find the difference set in mysql. For more information, please follow other related articles on the PHP Chinese website!