Shulou(Shulou.com)06/02 Report--

It is believed that many inexperienced people are at a loss about the example analysis of the wrong data caused by the wrong use of LEFTJOIN. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Yesterday, someone in the company who is familiar with the business and SQL came to me in a hurry and said that he had to solve an online SQL problem. He found that the data did not match and could not be criticized, but the data error was of great importance. Duty-bound to quickly analyze this SQL with him.

In fact, it is two tables, one table has the associated key of the other table, you can think of it as the primary foreign key relationship (here is only the relationship, I did not say the primary foreign key, or absolute one-to-one correspondence). His problem is

Why is the data queried by the two form tables correct? as long as one left join has NULL empty data, this is a SQL statement that has been online for a long time, and today it is suddenly found that the result of the reconciliation is not correct?

OK, production data must not be used for demonstration. Here we simply build two tables to illustrate this problem.

Here we use SIZE_1 and SIZE_2 to do the connection. The original SQL produced is written like this (in fact, it is much more complicated than this, 5 tables LEFT JOIN plus 6-7 conditions)

Select tab1.*,tab2.*

From tab1 as tab1

Left join tab2 as tab2 on tab1.size_1 = tab2.size_2 and tab2.name = 'aaa'

The question for this staff member is why there is a NULL, and what he wants is

I explained to him that the above NULL result is very normal, because the programmer does not understand the difference between LEFT JOIN and the following direct condition and underwriting, and thinks that the two are equal.

Here the programmer may write the conditions above because he is influenced by the knowledge of database optimization and knows the order in which the SQL optimizer executes (I won't talk about it here).

I explained to this staff member that the wrong result was caused after this condition was put into LEFT JOIN.

I explained to him the general reason, the following diagram can well explain the data after the query of the three methods, as well as the cause of the data error. You can see very clearly that only the first way of writing causes the query results to be inconsistent with expectations.

1. The on condition is the condition used when generating the temporary table, which returns the record in the left table regardless of whether the condition in on is true or not.

2. The where condition is the condition that the temporary table is filtered after the temporary table is generated. At this point, there is no meaning of left join (the record of the table on the left must be returned). If the condition is not true, it will be filtered out.

Because of the particularity of left join,right join,full join, records in left or right table will be returned regardless of whether the condition on on is true or not, while full has the union of left and right. While inner jion does not have this particularity, the condition is put in on and where, and the returned result set is the same.

After reading the above, have you mastered the method of sample analysis of incorrect data caused by incorrect use of LEFTJOIN? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.