Overview There are three types of criteria that can be used for conditional filtering:
- On: Junction (join) filter
- WHERE: General Condition filtering
- Having: conditional filtering after grouping
On
The order in which the Join–on statements are executed:
Example:
SELECT * from A left JOIN B on a.id = b.id and a<>0 WHERE a.name = ' x '
Note the AND clause and the WHERE clause after the on connection. What's the difference between them?
Logically explained: (regardless of how the steps are executed in the execution plan and how the nested connections are made, this is just about thinking about logical steps)
The order of execution is: from–> join–> on–>and–> left–> where–>select
- Step A. First, two tables are connected according to the on condition (logically, equivalent to filtering the data according to the ID equality condition after two table Cartesian sets, the actual situation is analyzed later)
- b step. Filter data based on the and condition before the where
- C step. Keep to the left. In any case, ensure the data integrity of table A. Therefore, in the result of the previous step, a table is filled with data that is filtered by the and condition because it cannot match the B table.
- D step. Then filter the result set based on where.
Having
When it comes to having, you must first talk about group by.
GROUP BY clause:
Create groups
SELECT column, SUM(column)FROM tableGROUP BY column
Description: The GROUP BY clause groups rows according to the data of column joins, that is, rows with the same value are grouped together. It is generally used in conjunction with aggregate functions. Of course, the sum () function here can also be a different aggregate function. All the combined columns (the columns listed in the GROUP BY clause) must be from the tables listed in the FROM clause, and the rows cannot be grouped according to actual values, aggregate function results, or values computed by other expressions.
GROUP BY clause based on multiple columns combined rows
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUMFROM TEACHERGROUP BY DNAME,TSEX
Having clause
GROUP BY clauses are grouped by simply grouping the data of the selected column into a group of rows that have the same value. In practical applications, it is often necessary to delete rows that do not meet the criteria, and in order to achieve this, SQL provides a HAVING clause. The syntax is as follows.
SELECT column, SUM(column)FROM tableGROUP BY columnHAVING SUM(column) condition value
Description: The having is usually used in conjunction with the GROUP BY clause. Of course, the sum () function in the syntax can also be any other aggregate function. The DBMS applies the search condition in the HAVING clause to the row group produced by the GROUP BY clause and removes it from the result table if the row group does not meet the search criteria.
Where
The HAVING clause and the WHERE clause are similar in that it also defines the search criteria. Unlike the WHERE clause, however, a HAVING clause is related to a group, not to a single row.
A little understanding of the SQL keyword for conditional filtering