Summary of Mysql exists usage

CREATE TABLE `activity_main` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`act_code` varchar(255) NOT NULL COMMENT 'Activity code',
`act_name` varchar(255) NOT NULL COMMENT 'Activity name',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_code` (`act_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Activity main table'

CREATE TABLE `activity_area` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `act_code` varchar(255) NOT NULL COMMENT 'Activity code',
 `area` varchar(255) NOT NULL COMMENT 'Websites participating in this activity',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='List of websites compatible with the event'

CREATE TABLE `activity_sku` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `act_code` varchar(255) NOT NULL COMMENT 'Activity code',
 `sku` varchar(255) NOT NULL COMMENT 'Products given away during the event',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Activity gift table'

# Query the weight scale select * from activity_main where act_code in (
select act_code from activity_sku where sku = 'Lingye Jun's body fat scale'
)

# Query the weight scale select * from activity_main a where exists (
select 1 from activity_sku b where a.act_code = b.act_code and b.sku = 'Lingye Jun's body fat scale'
)

# Fuzzy query B-BEKO British baby stroller select * from activity_main where act_code in (
select act_code from activity_sku where sku like '%B-BEKO%'
)

# Fuzzy query B-BEKO British baby stroller select * from activity_main a where exists (
select 1 from activity_sku b where a.act_code = b.act_code and b.sku like '%B-BEKO%'
)

# Query the activities held in Blog Garden select * from activity_main where act_code in (
select act_code from activity_area where area = '博客园'
)

# Query the activities held in Blog Garden select * from activity_main a where exists (
select 1 from activity_area b where a.act_code = b.act_code and b.area = '博客园'
)


# Activity information for holding an event in Blog Garden and the prize is a Huawei phone select * from activity_main where act_code in (
select act_code from activity_area where area = '博客园' and act_code in (
select act_code from activity_sku where sku = 'Huawei P30Pro'
))


# The inner layer exists statement is only effective in the current where statement. Whether it is finally returned depends on the outermost layer exists. If it is true, it is returned to the result set, and if it is false, it is discarded.
select * from activity_main a where exists (
select 1 from activity_area b where a.act_code = b.act_code and b.area = '博客园' and exists
(select 1 from activity_sku c where a.act_code = c.act_code and c.sku = 'Huawei P30Pro')
)