A brief analysis of join queries and set operations in MySQL learning

<p>This article will take you through the join query and set operations in MySQL. I hope it will be helpful to you! </p> <p><img src="https://img.php.cn/upload/article/000/000/024/6145d21cb7577613.jpg" alt="A brief analysis of join queries and set operations in MySQL learning" ></p> <h2><strong>Join query</strong></h2> <p>Join query refers to a matching query between two or more tables, generally called horizontal Operation means that the final result will contain all the columns in these tables. There are three join operations in MySQL, cross join, inner join, and outer join. [Related recommendations: <a href="https://www.php.cn/course/list/51.html" target="_blank" textvalue="mysql视频教程">mysql video tutorial</a>]</p> <p> Cross join is called <code>CROSS JOIN</code>. It performs Cartesian product on two tables, and it will return the two tables. The composition of all columns, for example, there are n pieces of data in the left table and m pieces of data in the right table, then the final result is <code>n*m</code>, but you can also connect it to yourself, then the final result is <code>n*n</code> items, such as the following statement. </p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false;">select * from orders as a cross join orders as b; +---------+--------+---------+--------+ | orderId | userId | orderId | userId | +---------+--------+---------+--------+ | 10007 | 2 | 10001 | 1 | | 10006 | 4 | 10001 | 1 | ..... | 10002 | 1 | 10007 | 2 | | 10001 | 1 | 10007 | 2 | +---------+--------+---------+--------+ 49 rows in set (0.01 sec)</pre><div class="contentsignin">Copy after login</div></div><p>Since there are 7 pieces of data in the orders table, 49 pieces of data will be generated in the end. Another way to write it is as follows. </p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false;">mysql> select * from orders as a ,orders as b;</pre><div class="contentsignin">Copy after login</div></div><p>Their results are the same, but the writing method is different. The above writing method was standardized for SQL by the American National Standards Institute in 1989, called the ANSI SQL 89 standard, and the first writing method was 92 year regulations. </p><p>Cross joins are useful for quickly generating repeated data, such as the following statement. </p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false;">insert orders select a.orderId,a.userId from orders as a ,orders as b limit 10;</pre><div class="contentsignin">Copy after login</div></div><p>The following is an inner join, which is divided into two parts. First, a Cartesian product is generated, and then it is filtered according to the subsequent ON filtering conditions. It generates values ​​with the same records in the two tables. </p><p><img src="https://img.php.cn/upload/image/218/908/717/1631965602120211.png" title="1631965602120211.png" alt="1 (2).png"/></p><p>In addition to the equal operator (=) after ON, you can also use other operators, such as greater than ( <code>></code>), less than ( <code><</code>) and not equal to ( <code><></code>) operators to form join conditions. </p><p>The last one is OUTER JOIN, which can match data between tables according to some filtering conditions. Different from INNER JOIN, there are unfound matching data in the reserved table added through OUTER JOIN. MySQL supports LEFT OUTER JOIN, RIGHT OUTER JOIN, you can omit OUTER when writing. </p><p>The following is a diagram of LEFT JOIN. LEFT JOIN returns all records in the left table (table1) and matching records in the right table (table2). </p><p><img src="https://img.php.cn/upload/image/297/131/340/1631965607545060.png" title="1631965607545060.png" alt="A brief analysis of join queries and set operations in MySQL learning"/></p><p>The following is a diagram of RIGHT JOIN. RIGHT JOIN returns all records in the right table (table2) and matching records in the left table (table1). </p><p><img src="https://img.php.cn/upload/image/383/403/226/163196561126076A brief analysis of join queries and set operations in MySQL learning" title="163196561126076A brief analysis of join queries and set operations in MySQL learning" alt="A brief analysis of join queries and set operations in MySQL learning"/></p><h2><strong>Set operation</strong></h2><p>There is a UNION operator in MySQL, which is used to combine 2 or more SELECT result sets and Delete duplicate rows between SELECT statements. When using it, follow the following basic rules: </p><ul><li>The number and order of columns appearing in all <code>SELECT</code> statements must be the same. </li><li>Columns must also be of the same data type. </li></ul><p>If you have the following table now</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false;">CREATE TABLE t1 ( id INT PRIMARY KEY ); CREATE TABLE t2 ( id INT PRIMARY KEY ); INSERT INTO t1 VALUES (1),(2),(3); INSERT INTO t2 VALUES (2),(3),(4);</pre><div class="contentsignin">Copy after login</div></div><p>Execute the following SQL</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false;">SELECT id FROM t1 UNION SELECT id FROM t2;</pre><div class="contentsignin">Copy after login</div></div><p>The final result is like this. </p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false;">+----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | +----+ 4 rows in set (0.00 sec)</pre><div class="contentsignin">Copy after login</div></div><p>The UNION statement will delete duplicate data from the result row set by default, but you can use UNION ALL to obtain duplicate records. </p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false;">SELECT id FROM t1 UNION ALL SELECT id FROM t2;</pre><div class="contentsignin">Copy after login</div></div><p>The results are as follows</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false;">+----+ | id | +----+ | 1 | | 2 | | 3 | | 2 | | 3 | | 4 | +----+ 6 rows in set (0.00 sec)</pre><div class="contentsignin">Copy after login</div></div><p>The basic difference between UNION and JOIN is that UNION combines the result set horizontally, while the JOIN statement combines the result set vertically. </p> <p><img src="https://img.php.cn/upload/image/410/849/290/1631965648883159.png" title="1631965648883159.png" alt="A brief analysis of join queries and set operations in MySQL learning"></p> <blockquote> <p>Original address: https://juejin.cn/post/7001772087534682143</p> <p>Author: i Tingfeng Passing Night</p> </blockquote> <p>For more programming-related knowledge, please visit: <a href="https://www.php.cn/course.html" target="_blank" textvalue="编程视频">Programming Video</a>! ! </p>

The above is the detailed content of A brief analysis of join queries and set operations in MySQL learning. For more information, please follow other related articles on the PHP Chinese website!