A brief analysis of SQL statement Select

Ⅰ.select General Syntax

Select selection list from table expression [sort declaration] [intercept]

Ⅱ. Table Expression 1.from clause

From Table_reference [, table_reference [, ...]]

The FROM clause generates a virtual table from a comma-delimited list of table_reference, with Jion.

Table_reference can be a table name or a generated table, such as "subqueries, table joins, or complex combinations of these things."

If more than one table is listed in the FROM clause, then they "jion" (the connection type, see below) Form a derived table. The derived table can be used to convert the where, GROUP by, and having clauses, and finally generate the result of the table expression.

Cross Connect :

T1 Cross JOIN T2

The T1 cross JOIN T2 a combination of each row from T1 and T2 (that is, a Cartesian product), and the joined table will contain such rows: all the fields in the T1 are followed by all the fields in the T2.
If both tables have N and M rows, the joined table will have n*m rows.

The From T1 cross JOIN T2 is equivalent to the from T1,T2. It is also equivalent to the from T1 INNER JOIN T2 on TRUE (see below).

Conditional connection

Internal connection

T1 [INNER] Join T2 on Boolean_expressiont1 [INNER] Join T2 USING (join column list) T1 NATURAL [INNER] Join T2

For each row in T1, if you can find one or more rows in T2 that satisfy the join condition, each row that satisfies the condition generates a row in the join table.

External connection

T1 {left | Right | Full} [OUTER] JOIN T2 on boolean_expressiont1 {left | Right | Full} [OUTER] JOIN T2 USING (join column list) T1 NATURAL {left | Right | Full} [OUTER] JOIN T2

Left OUTER join (OUTER join). First, an internal connection is performed. A row is then generated for each T1 in which a matching row cannot be found in T2, and the column corresponding to T2 in the row is NULL-padded. Therefore, the resulting connection table always contains at least one copy of each row from T1.

Right outer join (OUTER join). First, an internal connection is performed. A row is then generated for each T2 in which a matching row cannot be found in T1, and the column corresponding to T1 in the row is NULL-padded. Therefore, the resulting connection table always contains at least one copy of each row from T2.

Fully connected (full OUTER join). First, an internal connection is performed. A row is then generated for each T1 and T2 in which no matching rows are found, and the unmatched column in the row is NULL-padded. Therefore, the resulting connection table unconditionally contains at least one copy of each row in T1 and T2.

The ON clause is the most common type of join condition: it receives a Boolean expression that is the same as the WHERE clause. If the results of the two rows from T1 and T2 are true on the on expression, then they are considered to be matching rows.

A using is an abbreviated syntax for a join condition: it receives a comma-delimited list of field names that must be common to the join table and whose values must be the same. Finally, the JOIN USING will output each pair of equal input fields as one field followed by all other fields. Thus, the using (A, B, c) is equivalent to ON (t1.a = t2.a and t1.b = t2.b and t1.c = t2.c) except that if on is used, there will be two in the result A, B and C fields, and only one when using using (e.g. If a select * is used, they will take precedence).

Finally, natural is an abbreviated form of using: it automatically forms a using list of fields with the same name in two tables (only once for the same named field). If there are no fields with the same name, natural behaves like a cross JOIN.

Example:

T1:

num | Name-----+------   1 | A   2 | b   3 | c

T2:

num | Value-----+-------   1 | xxx   3 | yyy   5 | zzz

Run:

= = SELECT * from t1 cross JOIN T2; num | name | num | Value-----+------+-----+-------1 |   A | 1 | XXX 1 |   A | 3 | YYY 1 |   A | 5 | ZZZ 2 |   B | 1 | XXX 2 |   B | 3 | YYY 2 |   B | 5 | ZZZ 3 |   C | 1 | XXX 3 |   C | 3 | YYY 3 |   C | 5 | ZZZ (9 rows) = SELECT * from t1 INNER JOIN t2 on t1.num = T2.num; num | name | num | Value-----+------+-----+-------1 |   A | 1 | XXX 3 |   C | 3 | YYY (2 rows) = SELECT * from t1 INNER JOIN T2 USING (num); num | name | Value-----+------+-------1 | A | XXX 3 | C | YYY (2 rows) = SELECT * from t1 NATURAL INNER JOIN T2; num | name | Value-----+------+-------1 | A | XXX 3 | C | YYY (2 rows) = SELECT * from t1 left JOIN t2 on t1.num = T2.num; num | name | num | Value-----+------+-----+-------1 |   A | 1 | XXX 2 |     B |   | 3 |   C | 3 | YYY (3 rows) = SELECT * from t1 left JOIN T2 USING (num); num | name | Value-----+------+-------1 | A |XXX 2 |   B | 3 | C | YYY (3 rows) = SELECT * from t1 right JOIN t2 on t1.num = T2.num; num | name | num | Value-----+------+-----+-------1 |   A | 1 | XXX 3 |   C | 3 |      yyy |   | 5 | ZZZ (3 rows) = SELECT * from t1 full JOIN t2 on t1.num = T2.num; num | name | num | Value-----+------+-----+-------1 |   A | 1 | XXX 2 |     B |   | 3 |   C | 3 |      yyy |   | 5 | ZZZ (4 rows) = SELECT * from t1 left JOIN t2 on t1.num = t2.num and t2.value = ' xxx '; num | name | num | Value-----+------+-----+-------1 |   A | 1 | XXX 2 |     B |   | 3 |     C | | (3 rows)

Note that placing restrictions in the WHERE clause will produce different results:

= = SELECT * from t1 left JOIN t2 on t1.num = t2.num WHERE t2.value = ' xxx '; num | name | num | Value-----+------+-----+-------   1 | a    |   1 | XXX (1 row)

PS: Note the last two SELECT statements:

The join condition declared with on can also contain conditions that are not directly related to the connection. This functionality may be useful for some queries, but we need to think it over carefully.

This is because the restriction is processed before the connection when it is placed in the ON clause, and when the limit is placed in the WHERE clause after the connection is processed.

The join condition of an inner join can be written in a WHERE clause or in a join clause. The end result is the same.

For outer joins, we have no choice: the join condition must be completed in the FROM clause. The ON or using clause of an outer join is not equal to the where condition, because it causes the increment of rows in the final result (those that do not match the input line) and the deletion.

2.where clause

WHERE search_condition

The search_condition here is a return-type boolean value expression.
After the processing of the FROM clause is completed, each generated row is checked against the search criteria. If the result is true, the row remains in the output table, otherwise (that is, the result is false or null) it is discarded.

PS: It is important to note that the subquery in the WHERE statement issues the reference to the field of the table in the FROM statement. For example:

SELECT ... From FDT where C1 in (SELECT c3 from t2 where C2 = fdt.c1 + 10)

3.group BY clause

SELECT select_list from    ...    [WHERE ...]    GROUP by Grouping_column_reference [, Grouping_column_reference] ...

After the where filter is passed, the resulting input table can continue to be grouped with the GROUP BY clause, and then select some grouping rows with the HAVING clause.

The GROUP by clause is used to aggregate all the rows with the same grouping_column_reference values, reducing them to a single row, so that duplicates or compute aggregates in the output can be deleted. The order in which these fields are listed is irrelevant.

GROUP BY restrictions on select reference fields
Any field that appears after a select that does not appear in the aggregate function must also appear after group by. The grouped fields can be referenced in the select list because each group has a single value.

Example:

It calculates the total sales for each product (not the total sales for all products).

SELECT product_id, P.name, (SUM (s.units) * P.price) as sales from products    p left JOIN sales s USING (product_id)    GROUP by product_id, P.name, P.price;

In this example, the fields Product_id,p.name and P.price must be in the GROUP BY clause because they are all referenced in the query selection list (but below). The S.units field does not have to be in the GROUP by list because it is only in a clustered expression (sum (...)). , which represents the total sales of a group of products. For each product, this query returns the total sales for that product.

4.having clause

SELECT select_list from ... [WHERE ...] GROUP by ... Having boolean_expression

If a table has been grouped with group by and you are only interested in some of the groups, then you can filter the groupings with the HAVING clause.

groupby restrictions on the HAVING clause reference field are the same as groupby restrictions on the Select reference field

If a query calls an aggregate function, but does not have a GROUP BY clause, the grouping still occurs: The result is a single group of rows (or if a single row is eliminated by the having, then there may be no rows).
Similarly, it can contain only a HAVING clause, no call to an aggregate function, or a GROUP BY clause.

Ⅲ. Sorting

SELECT select_list from    table_expression    ORDER by Sort_expression1 [ASC | DESC]              [, Sort_expression2 [ASC | DESC] ...]

After the query generates the output table, you can also sort the output table after you have finished processing the picklist. If there is no sort, the rows are returned in an unpredictable order.

Sort_expression is any expression that can be used to select a list, for example:

SELECT A, b from Table1 ORDER by a + B, C;

If more than one sort expression is specified, the following expressions are used for further sorting only if the preceding expressions are sorted equally. Each expression can be followed by an optional ASC (ascending, default) or desc (descending) to set the sort direction. In ascending order, a small value is output, and the "small" is defined in terms of the < operator. Similarly, the descending order is judged by the > operator.

Note that the sort options are relatively independent for each row sequence. For example, order by x, y desc means that order by x ASC, y desc, differs from ORDER by x Desc, y desc.

A sort_expression can also be a field number, such as:

SELECT A, B, c from Table1 ORDER by 1;

Ⅳ.limit Intercept

SELECT select_list from    table_expression    [ORDER by ...]    [LIMIT {number | All}]

The limit clause allows you to remove only a subset of the data rows from the query results. The limit all and the omit limit clauses are the same.

It is a good idea to use the ORDER BY clause to constrain the result rows to a unique order using limit. Otherwise you will get an unexpected subset.

Ⅴ.select statements in general Order of execution:

1.from clause: generates a virtual table T1 by making various joins to the table.

2.where clause: for each row of data in the virtual table T1, the WHERE clause is evaluated and returns true, the table line returns false, and the bank is deleted. The virtual table T2 is formed after filtering.

3.group By clause: the virtual table T2 is grouped by the expression after group by.

4.having clauses: After the grouped data, further filtering, can only refer directly to the field in group by or use other fields as aggregate function parameters. form the selection list T3.

5.order by clause: sorts the T3.

6.limit clause: intercepts the corresponding number of rows.

In these keywords, the column name of the final view can be used only in the order by statement, such as:

SELECT fruitname, Productplace, Price, ID as IDE, Discountfrom t_test_fruitinfowhere (productplace = N "China") ORDER by IDE

You can use the IDE only in an order BY statement, and you cannot use the IDE if you need to reference column names in other conditional statements.

The above is organized according to the PostgreSQL documentation, since some syntax may only be applicable to PostgreSQL, but the principle is similar.

A brief analysis of SQL statement Select