The difference between on and where in join tables and conditions

The join table operation is not detailed here. When we join a table, it is unclear whether you have paid attention to the conditions following on and where, what is the difference? Some friends may think that the conditions behind them are the same. You can follow on, or you can follow where if you like. What is the difference between on and where?
In the join operation, there are several situations. Left join, right join, inner join, etc.

To clearly express the problems described in the topic, I will briefly describe the left, right, and inner connection methods.

The following describes the log table (post) and Category Table (Category) of a common Blog system.

Here we stipulate that some logs may not be classified, and some of them may not belong to its articles.

1. Left join:

(Make sure to find all rows in the left join table)

Find all articles and display their categories:

Select P. Title, C. category_name from post P left join category C on p. cid = C. cid2. right join:

(Make sure to find all rows in the right join table)

Query all categories and display the number of articles contained in the category.

Select count (P. ID), C. category_name from post P rightjoin category C on p. PID = C. cid3. inner join

(Identify the rows associated with the two tables) query logs with the same category. (That is, log articles that are not classified will not be within the scope of our query ).

Select P. Title, C. category_name from post P inner join category C on p. cid = C. CID.

This is equivalent to the hard join of two tables. Now let's look back at the problem above.

In the first case, what will happen if the on condition is written after the WHERE clause?

That is: select P. Title, C. category_name from post P left join category C where P. cid = C. CID

In the second case, we also follow the above writing method.

Select count (P. ID), C. category_name from post P rightjoin category C where P. PID = C. CID

If you run the preceding SQL statements, you will find that they have filtered out some records that do not meet the conditions. Here, you may have doubts, didn't left and right be used? They can ensure that all rows on the left or right are queried. Why is it useless now? For this problem, haha! Is it incredible. This problem occurs because the where and on keywords are followed by conditions. Well, now I am not talking about it. I 'd like to remind you of the answer. For join operations on tables involved in join operations, if the rows that do not meet the join conditions are also within our query range, we must place the join conditions behind on, but not after the WHERE clause. If we place the join condition behind the WHERE clause, all left, right, and other operations will not be able to work. In this case, the effect is equivalent to the inner connection. The conditions that do not affect the selection of rows can be placed after the on or where clause. Remember: All the connection conditions must be placed behind the on clause. Otherwise, all the left clause and right clause in the front will act as a decoration and will not work.