Summary of Mysql exists usage

Introduction

EXISTS is used to check whether a subquery returns at least one row of data. The subquery does not actually return any data, but returns a value of True or False.

EXISTS specifies a subquery that tests for the existence of rows. Syntax: EXISTS subquery. The subquery parameter is a restricted SELECT statement (COMPUTE clause and INTO keyword are not allowed). The result type is Boolean and returns TRUE if the subquery contains rows.

Example

An activity configuration main table activity_main uses act_code to uniquely identify an activity. The activity venue adaptation table activity_area is associated with the main table through act_code. The activity prize table activity_sku is associated with the main table through act_code.

Activity main table

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'

Adaptation table of websites where the event is held

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'

Event Prize List

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'

Comparing Queries Using EXISTS and IN This example compares two semantically similar queries. The first query uses IN and the second query uses EXISTS. Note that both queries return the same information.

# 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')
)

The above is the detailed content of the summary of Mysql exists usage. For more information about the usage of Mysql exists, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Introduction to the use and difference between in and exists in MySQL
Comparative Analysis of IN and Exists in MySQL Statements
Detailed explanation of mysql exists and not exists examples
MySQL exists and in detailed explanation and difference
Summary of the differences between in query and exists query in mySQL
A bug in MySQL about exists
How to solve the error "A Windows service with the name MySQL already exists." when installing MySQL
Tutorial on using FROM and EXISTS clauses in MySQL subqueries
MYSQL IN and EXISTS optimization examples
mysql not in, left join, IS NULL, NOT EXISTS efficiency problem record
UCenter info: MySQL Query Error SQL:SELECT value FROM [Table]vars WHERE noteexists

<<: JavaScript Factory Pattern Explained

>>: Nginx server adds Systemd custom service process analysis