Many new mysql users may be confused about the difference between where and having in mysql. Let's take a few simple examples to introduce the difference between where and having.
Let's run two SQL statements first:
The Code is as follows: |
Copy code |
SELECT * FROM 'Welcome 'HAVING id> 1 LIMIT 0, 30
SELECT * FROM 'Welcome 'WHERE id> 1 LIMIT 0, 30
|
Check the results. How can this problem be solved? Whether the same result is found.
Let's look at two more SQL statements:
The Code is as follows: |
Copy code |
SELECT user, MAX (salary) FROM users group by user having max (salary)> 10;
SELECT user, MAX (salary) FROM users group by user where max (salary)> 10;
|
How is it? Can you see the difference? The first SQL statement can run normally [errors may occur in the old mysql version], and the second statement will report an error.
If you don't see it, let's look at it again.
• The WHERE clause is used to filter data from a table or view. For example:
The Code is as follows: |
Copy code |
1. select * from table_a where title1 = 'a '; Select * from table_a where title1 = 'a '; |
• The HAVING clause stores the query results in the memory for Secondary Filtering in the SELECT clause and WHERE clause. For example:
The Code is as follows: |
Copy code |
1. select * from table_a 2. where title1 = 'A' 3. having content2 like 'B %' |
Summary
The two are similar, but they are also different. Let's take a brief look.
The difference between where and having is that the where clause operates on all records of a table and only searches for records that match the specified condition.
The having clause only retrieves the records of the operated table and further filters the result set.
Generally, the having clause is connected to the group by clause, while the where clause is connected to the select, delete, and update statements.