Detailed explanation of the differences between SQL joint query inner join, outer join and cross join

Detailed explanation of the differences between SQL joint query inner join, outer join and cross join

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:
  • A brief introduction to the usage of decimal type in MySQL
  • Mysql method to copy a column of data in one table to a column in another table
  • How to improve MySQL Limit query performance
  • Mysql master/slave database synchronization configuration and common errors
  • Use and analysis of Mysql Explain command
  • SQL statements in Mysql do not use indexes
  • How to correctly create MySQL indexes
  • Explanation of the usage of replace and replace into in MySQL
  • How to optimize MySQL performance through MySQL slow query
  • MySQL database case sensitivity issue

<<:  How to install MySQL 5.7.28 binary mode under CentOS 7.4

>>:  Practical method of upgrading PHP to 5.6 in Linux

Recommend

Example statements for indexes and constraints in MySQL

Foreign Keys Query which tables the primary key o...

How to use Linux locate command

01. Command Overview The locate command is actual...

Basic usage and pitfalls of JavaScript array sort() method

Preface In daily code development, there are many...

A quick solution to the problem of PC and mobile adaptation

When making a web page, we usually need to consid...

MySQL 5.7.17 installation and configuration method graphic tutorial (windows)

1. Download the software 1. Go to the MySQL offic...

MySQL configuration SSL master-slave replication

MySQL5.6 How to create SSL files Official documen...

Detailed explanation of the application of the four states of hyperconnection

Although you think it may be a browser problem, i...

MySQL 8.0.22 download, installation and configuration method graphic tutorial

Download and install MySQL 8.0.22 for your refere...

JavaScript implements H5 gold coin function (example code)

Today I made a Spring Festival gold coin red enve...

Detailed explanation of the use of MySQL select cache mechanism

MySQL Query Cache is on by default. To some exten...

Use of Vue3 pages, menus, and routes

Table of contents 1. Click on the menu to jump 1....

MySQL 4G memory server configuration optimization

As the number of visits to the company's webs...

MySQL SQL statement performance tuning simple example

MySQL SQL statement performance tuning simple exa...

The implementation of event binding this in React points to three methods

1. Arrow Function 1. Take advantage of the fact t...