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