Mysql语句，on，where，having..区别

Question

提出这个问题很多人会觉得这太基础了..
on为join服务，where为物理表服务，having可以为别名服务.可惜我要的不是这样的答案
我在想一个问题，为什么mysql官方要出三个筛选条件关键字？
即一个关键字不能解决吗？（单独用where放在select执行前进行一次总的筛选）
why not ?

Answer 1

SQL is a standard, not defined by mysql.

Of course, mysql has its own defined parts, and there are also standards that are not implemented. But the ones you asked about are indeed standard parts.

As for why, I’m sorry I couldn’t answer your question. You can search for the English content.

Answer 2

Sometimes, having can replace where, but where cannot replace having. where is for row-level filtering, used to select specific rows in the database, and having is used for group filtering, used to select specific groups (one or more) in the result set ). . .

Answer 3

Let’s put it this way, there is a table that is a student subject score table. We want to find out the students’ total score and the students whose total score is greater than 500 points. At this time, we need to use having, select sum(score) as score from subjects group by user_id having score >500

Answer 4

SQL syntax has standards. MySQL must comply with basic standards, and these three keywords have different semantics:
ON, defines the conditions for table connection
WHERE, defines the filtering of table row records Condition
HAVING, defines the filtering conditions for grouping

Answer 5

Of course I know.
Then we can only use SQL statement standards to explain this problem

Answer 6

Since there is this problem, there must be a sql foundation.

SQL defines a standard keyword sequence. For example, the first keyword in a query must be select; there is also an execution sequence.

1. (left) join table on association condition; on determines the conditions for the association between the table and the previous table, which is standard syntax

2. The execution order of where is after join on, that is to say, where condition filtering is after join is completed, such as left join a, you add a table condition after where, the effect of this left join is similar to join.

3.having This is a conditional filter to reduce the existence of subqueries and is often used in reports; for example, filter again after statistics

Answer 7

Good question, sir. I think so too from a semantic point of view.
Add join condition after join. The condition can be where.
When you need to filter after grouping, where also conforms to the semantics, why use having.
Maybe it’s to differentiate.