When developing applications that use a database, you will inevitably have to use a union query. Common union queries in SQL include inner join, outer join, and cross join. Many people, including myself, may not be very clear about the differences between the three. Let's take a look at them. If you use join to connect tables, the defective case is inner join. In addition, left join and right join used in development belong to outer join, and outer join also includes full join. Let me use pictures to let you know the difference between them. There are two tables, Table A is the table on the left. Table B is the table on the right. Each of them has four records, two of which have the same name: 1. The result of INNER JOIN is the intersection of AB SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name 2. LEFT [OUTER] JOIN produces a complete set of table A, and the matching values in table B are replaced by null values if there is no match. SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name 3.RIGHT [OUTER] JOIN produces the complete set of table B, and the matching values in table A are replaced by null values if there is no match. SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.name = TableB.name The diagram is similar to left join. 4. FULL [OUTER] JOIN produces the union of A and B. For records that do not match, null is used as the value. SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name You can find out that there is no matching value by using is NULL: SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null 5. CROSS JOIN performs an N*M combination of the data in Table A and Table B, i.e., the Cartesian product. For example, this example will generate 4*4=16 records. During the development process, we must filter the data, so this method is rarely used. SELECT * FROM TableA CROSS JOIN TableB Through the above comparison, I believe everyone has a clear understanding of the difference between inner join, outer join and cross join. Replenish Why is join the best choice? Because if "," is used to connect the tables, the A and B tables will directly generate the Cartesian product, and then use where to filter the results. However, when joining, tables A and B are connected, the results will be filtered according to the conditions after on, and then filtered according to the where conditions. According to the SQL standard CROSS JOIN is a Cartesian product. But for MySQL, CROSS JOIN is equivalent to INNER JOIN. Summarize The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links You may also be interested in:
|
<<: How to install MySQL 5.7.28 binary mode under CentOS 7.4
>>: Practical method of upgrading PHP to 5.6 in Linux
Foreign Keys Query which tables the primary key o...
01. Command Overview The locate command is actual...
Preface In daily code development, there are many...
When making a web page, we usually need to consid...
1. Download the software 1. Go to the MySQL offic...
MySQL5.6 How to create SSL files Official documen...
Although you think it may be a browser problem, i...
Download and install MySQL 8.0.22 for your refere...
Today I made a Spring Festival gold coin red enve...
MySQL Query Cache is on by default. To some exten...
Table of contents 1. Click on the menu to jump 1....
As the number of visits to the company's webs...
Problem description: Recently, there is a demand ...
MySQL SQL statement performance tuning simple exa...
1. Arrow Function 1. Take advantage of the fact t...