A little understanding of the SQL keyword for conditional filtering

Source: Internet
Author: User
Tags dname

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.