How to find intersection in mysql

In mysql, you can use the "SELECT" statement and the "INNER JOIN" keyword to query the intersection and find the intersection data. The syntax is "SELECT field name FROM data table 1 INNER JOIN data table 2 USING (field name) ;".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Create two tables

CREATE TABLE `object_a` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `oname` varchar(50) DEFAULT NULL,
  `odesc` varchar(50) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Add data

##

CREATE TABLE `object_b` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `oname` varchar(50) DEFAULT NULL,
  `odesc` varchar(50) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Add data

Query intersection

SELECT a.oname,a.odesc FROM object_a a INNER JOIN object_b b ON a.oname=b.oname AND a.odesc=b.odesc

is equivalent to

SELECT a.oname,a.odesc FROM object_a a INNER JOIN object_b b USING(oname,odesc)

The result is as follows

PS: You can try this writing method for other databases

SELECT oname,odesc FROM object_a 
INTERSECT
SELECT oname,odesc FROM object_b

[Related recommendations:

mysql video tutorial]

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