Briefly talk about mysql left join inner join

Preface

I have been busy developing a cold chain traceability system for Province X recently. I work until 11pm every day and get home. I also work overtime on weekends. I don't have much time to write a blog. I will write it when I have some free time.

Business: SQL statements to count data in and out of the warehouse.

Question: Only the X-end incoming orders are counted.

Reason: It was not discovered that in the X-side database, only the incoming order had field a data, but the outgoing order did not, and field a was used for inner join, resulting in only being able to find the incoming order for statistical operations. Note: The warehouse in and out orders are in the same table. When statistical data is collected, some data needs to use the a field to associate with other tables.

Solution: Use left join.

I have always wondered when I could use left join and inner join, and this time I learned a lesson.

For the difference between them, you can read the following article:

Difference between inner join and left join

Here is a small copy:

I thought I understood the difference between inner join and left join, but today when I fetched parameters from the front end, I found that the result was not what I expected, and then I realized that the problem was with the inner join.

The requirement is to query data from the database and display it in the form of a bar chart on the front end. The data found is grouped by industry, showing the number of households and the proportion of households in each industry. The fields involved are the number of users and total number of users in table A and the industry name in table B. Originally, the industry name should be displayed on the X-axis regardless of whether the data can be found or not. However, no data is displayed on either the X-axis or the Y-axis. The problem was that I was using the wrong connection method.

1. The difference between left join, right join and inner join in SQL

Left join returns all records in the left table and records in the right table where the join fields are equal.

Right join returns all records in the right table and records whose join fields are equal in the left table.

Inner join (equivalue join) returns only the rows where the joined fields in the two tables are equal

Here are some examples:

Table A records the following:

aID aNum
1 a20050111
2 a20050112
3a20050113
4 a20050114
5a20050115

Table B records the following:

bID bName
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408

1.left join

The sql statement is as follows:

select * from A
left join B
on A.aID = B.bID

Here are the results:

aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL

(The number of rows affected is 5)

Results:

Left join is based on the records of table A. A can be regarded as the left table, and B can be regarded as the right table. Left join is based on the left table.

In other words, all records in the left table (A) will be displayed, while the right table (B) will only display records that meet the search criteria (in this example: A.aID = B.bID).

The places where table B does not have enough records are all NULL.

Summarize

This is the end of this article about MySQL left join and inner join. For more information about MySQL left join and inner join, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!