How to check the intersection of two tables in mysql

Mysql method to query the intersection of two tables: 1. Use the UNION ALL keyword to merge the data columns of the two data to query the intersection; 2. Use the query with the IN keyword; 3. Use the EXISTS keyword subquery.

mysql query intersection method of two tables:

1. Two tables (columns) that require intersection The structure should be consistent, the corresponding number of fields, and field types should be the same; use the UNION ALL keyword to merge the data columns of the two data; GROUP BY all the above columns that need to be compared; finally HAVING COUNT (any column, no more Column)>1, then it is the intersection.

SELECT a.* FROM(    SELECT * from teacher    UNION ALL    SELECT * from student)a GROUP BY a.id,a.name,a.sex HAVING COUNT(a.sex)>1

#2. Inner join or equivalent join. jion(inner jion). When comparing two data columns, the condition must involve the column that needs to be compared. There is no comparison of the sex column here, so there is one more row of data than step 2.

SELECT * FROM student AS a JOIN teacher AS b ON a.name =b.name AND a.ID=b.ID或者SELECT * FROM student AS a inner  JOIN teacher AS b ON a.name =b.name AND a.ID=b.ID

#3. Query with IN keyword. There must be as many INs as there must be to compare how many columns of data are the same. At this time, you must control the columns. It is not recommended to use too many columns.

SELECT * FROM student AS a where a.name in (select name from teacher )AND a.ID in (select ID from teacher);

4. Subquery with EXISTS keyword. To put it bluntly, this statement is derived by judging whether two equal conditions are met in the teacher.

SELECT * FROM student AS a where EXISTS (select *from teacher b where a.name =b.name AND a.ID=b.ID)

More related free learning recommendations: mysql tutorial(video)

The above is the detailed content of How to check the intersection of two tables in mysql. For more information, please follow other related articles on the PHP Chinese website!