Home > Database > Mysql Tutorial > body text

How to check the intersection of two tables in mysql

coldplay.xixi
Release: 2020-10-19 15:59:17
Original
8320 people have browsed it

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.

How to check the intersection of two tables in mysql

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
Copy after login

How to check the intersection of two tables in mysql

#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
Copy after login

How to check the intersection of two tables in mysql

#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);
Copy after login

How to check the intersection of two tables in mysql

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)
Copy after login

How to check the intersection of two tables in mysql

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template