Shulou(Shulou.com)06/01 Report--

This article mainly introduces mysql how to check the intersection of the two tables, has a certain reference value, friends in need can refer to. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Mysql query the intersection of two tables: 1, the data columns of two data are combined and intersected with the UNION ALL keyword; 2, the query with the IN keyword is used; and 3, the subquery with the EXISTS keyword is used.

Mysql queries the intersection of two tables:

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

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

2. Internal connection or equivalent connection. Jion (inner jion). To compare the two data columns, the condition must involve the columns to be compared. There is no sex column to compare 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 or SELECT * FROM student AS an inner JOIN teacher AS b ON a.name = b.name AND a.ID=b.ID

3. Query with IN keyword. You must have as many IN as you need to compare how many columns of data are the same. At this time, you must control the columns. Too many columns are not recommended.

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 sentence is also derived from judging whether two equal conditions are met in teacher.

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

Thank you for reading this article carefully. I hope it will be helpful for everyone to share mysql how to check the intersection of the two tables. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.