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

Excel export always fails in docker environment

Excel export always fails in the docker environme...

The process of deploying a project to another host using Jenkins

environment Hostname ip address Serve Jenkins 192...

How to convert extra text into ellipsis in HTML

If you want to display extra text as ellipsis in ...

Nginx implements dynamic and static separation example explanation

In order to speed up the parsing of the website, ...

JavaScript canvas realizes the effect of nine-square grid cutting

This article shares the specific code of canvas t...

Mysql master/slave database synchronization configuration and common errors

As the number of visits increases, for some time-...

mysql delete multi-table connection deletion function

Deleting a single table: DELETE FROM tableName WH...

Detailed explanation of key uniqueness of v-for in Vue

Table of contents 1. DOM Diff 2. Add key attribut...

How to expand the disk size of a virtual machine

After Vmvare sets the disk size of the virtual ma...

Debian virtual machine created by VirtualBox shares files with Windows host

the term: 1. VM: Virtual Machine step: 1. Downloa...

An article to solve the echarts map carousel highlight

Table of contents Preface toDoList just do it Pre...

A brief discussion on MySql views, triggers and stored procedures

view What is a view? What is the role of a view? ...