Inner join on will filter out the conditions of null values on both sides

Time:2020-1-22
During the work of the previous two days, I met a problem. As for the join on query, I have always been confused about the results. Here is a record.
1. First, look at the following SQL query statement:

Inner join on will filter out the conditions of null values on both sides

The result is 25053

2. Add a condition of o.lat = n.lat:

Inner join on will filter out the conditions of null values on both sides

The result is 15586

3. Now we change the condition to o.lat! =N.lat, should the result show a difference of 25053-15586?

Inner join on will filter out the conditions of null values on both sides

We found that the results were not as expected, but 125. Strange, where are the remaining 25053-15586-125 = 9342 pieces of data? How can they not be found?

4. Look at the following SQL statement again. We filter out the situation that o.lat and n.lat are empty:

Inner join on will filter out the conditions of null values on both sides

9342!!
Aha, I see. Join on will filter out the conditions with null values on both sides.
If you return null data in the left table, you can use left join. On the contrary, if you return null data in the right table, you can use right join.
Inner join (which can be abbreviated to join) will not return null data in the left and right tables.

Recommended Today

[reading notes] calculation advertising (Part 3)

By logm This article was originally published at https://segmentfault.com/u/logm/articles and is not allowed to be reproduced~ If the mathematical formula in the article cannot be displayed correctly, please refer to: Tips for displaying the mathematical formula correctly This article isComputing advertising (Second Edition)Reading notes. This part introduces the key technology of online advertising, which is […]