Summary of Mysql exists usage

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

Recommend

Sample code using scss in uni-app

Pitfalls encountered I spent the whole afternoon ...

IDEA configuration process of Docker

IDEA is the most commonly used development tool f...

How to create a web wireframe using Photoshop

This post introduces a set of free Photoshop wire...

HTML tutorial, HTML default style

html , address , blockquote , body , dd , div , d...

Determine whether MySQL update will lock the table through examples

Two cases: 1. With index 2. Without index Prerequ...

CSS perfectly solves the problem of front-end image deformation

I saw an article in Toutiao IT School that CSS pe...

MySQL v5.7.18 decompression version installation detailed tutorial

Download MySQL https://dev.mysql.com/downloads/my...

Summary of basic operations for MySQL beginners

Library Operations Query 1.SHOW DATABASE; ----Que...

Specific implementation methods of MySQL table sharding and partitioning

Vertical table Vertical table splitting means spl...

Javascript implements simple navigation bar

This article shares the specific code of Javascri...

Detailed graphic tutorial on installing Ubuntu 20.04 dual system on Windows 10

win10 + Ubuntu 20.04 LTS dual system installation...

Detailed tutorial on using cmake to compile and install mysql under linux

1. Install cmake 1. Unzip the cmake compressed pa...

Web Design Tutorial (7): Improving Web Design Efficiency

<br />Previous article: Web Design Tutorial ...