Detailed explanation of MySQL union query (the difference between IN and EXISTS)

Cartesian product

The Cartesian product refers to the Cartesian product of two sets X and Y in mathematics. Cartesian product, also known as direct product, is expressed as X × Y. The first object is a member of X and the second object is one member of all possible ordered pairs of Y [3].
Assume that set A={a, b} and set B={0, 1, 2}, then the Cartesian product of the two sets is {(a, 0), (a, 1), (a, 2 ), (b, 0), (b, 1), (b, 2)}.

Related free learning recommendations: mysql video tutorial

Inner connection

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

Outer join

Outer join is divided into left outer join and right outer join. If a joint query is performed, if the table on the left is completely displayed, we say it is a left outer join; if the table on the right is completely displayed, we say it is a right outer join.

--左外连接，表1完全显示select 字段名 from 表名1 left join 表名2 on 连接条件;-- 右外连接，表2完全显示select 字段 from 表名1 right join 表名2 on 连接条件;

Self-connection

When it comes to comparisons between rows, you need to Connected
Example: Display all grade information with higher "Computer Principles" than "Java" grades

-- 先查询“计算机原理”和“Java”课程的idselect id,name from course where name='Java' or name='计算机原理';-- 再查询成绩表中，“计算机原理”成绩比“Java”成绩 好的信息SELECTs1.*FROMscore s1,score s2WHEREs1.student_id = s2.student_idAND s1.score <pre class="brush:php;toolbar:false">-- 也可以使用join on 语句来进行自连接查询SELECTs1.*FROMscore s1JOIN score s2 ON s1.student_id = s2.student_idAND s1.score <p><strong>Subquery</strong></p><p>The subquery refers to the subquery embedded in Select statements in other SQL statements are also called nested queries. <br> Single row subquery: Subquery that returns one row of records <br> Case: Query and "Xiaobai"'s classmates: </p><pre class="brush:php;toolbar:false">select * from student where classes_id=(select classes_id from student wherename='小白');

Multiple row subquery: Subquery that returns multiple rows of records

1. [NOT] IN keyword:
   First execute the SQL of the subquery, put the result into the memory, then perform the outer query, and directly put the given result Just compare the restriction conditions with the results of the subquery and filter them. (Depends on memory, suitable for situations where the result set of a subquery is relatively small)
2. [NOT] EXISTS keyword:
   Execute the outer query first, take the records of each outer query in turn, and bring them into In the inner query
   If the result set of the inner query is not empty, keep the results of the outer query
   If the result set of the inner query is empty, discard the results of the outer query
   Does not rely on memory, suitable for situations where the outer query result set is small and the subquery result set is relatively large

Merge query

For To merge the execution results of multiple selects, you can use the set operators union and union all. When using UNION
and UNION ALL, the fields in the result sets of the previous and subsequent queries need to be consistent.
union
This operator is used to obtain the union of two result sets. When this operator is used, duplicate rows in the result set are automatically removed.
Case: Query the courses with ID less than 3, or the name is "English":

select * from course where id<p><strong>union all</strong><br> This operator is used to obtain the union of two result sets . When this operator is used, duplicate rows in the result set are not removed. <br> Case: Query courses with ID less than 3, or with the name "Java": </p><pre class="brush:php;toolbar:false">-- 可以看到结果集中出现重复数据Javaselect * from course where id<blockquote><p><strong>Related free learning recommendations: </strong><a href="https://www.php.cn/course/list/51.html" target="_blank"><strong>mysql database</strong></a><strong>(video)</strong></p></blockquote>

The above is the detailed content of Detailed explanation of MySQL union query (the difference between IN and EXISTS). For more information, please follow other related articles on the PHP Chinese website!