Using INNERJOIN in MySQL to implement Intersect Union operations _ MySQL

MySQL uses INNERJOIN to implement Intersect Union operations MySQL uses inner join to implement Intersect Union operations
I. Business Background

We have a table design as follows:

CREATE TABLE `user_defined_value` (  `RESOURCE_ID` varchar(20) DEFAULT NULL,  `COLUMN_NAME` varchar(20) DEFAULT NULL,  `VALUE` varchar(255) DEFAULT NULL,  KEY `ID_IDX` (`RESOURCE_ID`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

RESOURCE_ID is the unique identifier of a resource.

COLUMN_NAME is an attribute name of a resource.

VALUE is the attribute VALUE of a resource.

II. requirements

Now you need to find the resource ID of attribute A = '1' and attribute B = '2' from the table.

III. Analysis

Here, we need to check the resource IDs that satisfy both attributes. if the relationship is OR, the SQL statement will be very simple.

SELECT DISTINCT RESOURCE_ID FROM USER_DEFINED_VALUE WHERE (COLUMN_NAME = 'A' AND VALUE in ('1'))  OR (COLUMN_NAME = 'B' AND VALUE in ('2'))

When the relationship is the same, here we cannot simply replace the OR keyword with AND. we need to first check the resources that meet each attribute AND then obtain the union set.

On Baidu, I found that SQL Server and Oracle both support Intersect. we can directly take the query results of two identical structures to the intersection, and MySQL does not have the Intersect keyword. how can we achieve this?

4. use inner join xxx USING xxx

SELECT DISTINCT RESOURCE_ID FROM USER_DEFINED_VALUE INNER JOIN (SELECT DISTINCT RESOURCE_ID FROM USER_DEFINED_VALUE WHERE  (COLUMN_NAME = 'A' AND VALUE in ('1')) ) t0 USING (RESOURCE_ID) INNER JOIN (SELECT DISTINCT RESOURCE_ID FROM USER_DEFINED_VALUE WHERE  (COLUMN_NAME = 'B' AND VALUE in ('2')) ) t1 USING (RESOURCE_ID)

In the first statement, all resource IDs in the table are identified.

In the second sentence, locate the resource ID of attribute A = '1' and obtain the intersection with the query result of the first sentence.

In the third sentence, locate the resource ID of attribute B = '2' and obtain the intersection with the preceding query result.

In this way, you can add other attributes.

Copy the translation results from Google or