SQL Select syntax

select| syntax
The most commonly used command in SQL is the SELECT statement, which is used to retrieve data. The syntax is:

SELECT [All | DISTINCT [On (expression [, ...]]]] * | expression [as Output_name] [, ...] [INTO [temporary | TEMP] [TABLE] new_table] [from From_item [, ...]] [WHERE condition] [GROUP by expression [, ...]] [Having condition [, ...]] [{UNION | INTERSECT | EXCEPT [All]} Select] [ORDER BY expression [ASC | DESC | USING operator] [, ...]] [For UPDATE [of Class_name [, ...]]] [LIMIT {Count | All} [{OFFSET |,} start]]



Now we will demonstrate the complex syntax of the SELECT statement through a variety of examples. The tables used for these examples are defined in the vendor and part databases.
1.4.1.1. Simple Select
Here are some simple examples of using SELECT statements:

Example 1-4. Simple query with a condition

To find out all records with the field price greater than 10 from the table part, we write the following query:

SELECT * FROM part WHERE price > 10;
And then get the table:
PNO | PName | Price-----+---------+--------3 | Bolt | 15 4 | Cam | 25



Using "*" in a SELECT statement retrieves all the properties in the table. If we only want to retrieve properties PName and price from the table part, we use the following statement:

SELECT PName, price from part WHERE price > 10;
This time our results are:
PName | Price--------+--------Bolt | Cam | 25
Note that the SQL SELECT statement corresponds to the "projection" (map) in the relational calculus, rather than the "selection" (see Relational calculus for details).


The conditions in the WHERE clause can also be or,and with the keyword, and not logically connected:

SELECT PName, price from part WHERE pname = ' Bolt ' and (Price = 0 OR Price <= 15);
This produces the following results:
PName | Price--------+--------Bolt | 15



You can use arithmetic operations in the target list and WHERE clause. For example, if we wanted to know how much it would cost if we bought two parts, we could use the following query:

SELECT pname, Price * 2 as DOUBLE from part WHERE Price * 2 < 50;
So we get:
PName | DOUBLE--------+---------Screw | Nut | Bolt | 30
Note that DOUBLE is the new name for the second column after the keyword as. This technique can be used for each element in the target list, giving them a new caption to display in the result column. This new title is often called an alias. This alias cannot be used elsewhere in the query.



1.4.1.2. Joins (connection)
The following example shows how the connection is implemented in SQL.

To connect three tables Supplier,part and sells on a common attribute, we typically use the following statement:

SELECT S.sname, p.pname from SUPPLIER S, part P, sells se WHERE s.sno = se. SNO and p.pno = SE. PNO;
And the result we have is:
sname | PName-------+-------Smith | Screw Smith | Nut Jones | Cam Adams | Screw Adams | Bolt Blake | Nut Blake | Bolt Blake | Cam



In the FROM clause, we use an alias for each relationship, because there are common naming properties (SNO and PNO) between these relationships. Now we can distinguish the common naming properties of different tables by simply prefixing each relationship's alias with a dot prefix. The Union is computed using the same method as shown in an inner join. First, the Cartesian product supplierxpartxsells is calculated. Then select the records that satisfy the conditions given in the WHERE clause (that is, the values of the public named properties must be equal). Finally, we map out all the attributes except S.sname and P.pname.

Another way to connect is to use the following SQL JOIN syntax:

Select Sname, pname from Supplierjoin sells using (SNO) JOIN part using (pno);
Giving again:
sname | PName-------+-------Smith | Screw Adams | Screw Smith | Nut Blake | Nut Adams | Bolt Blake | Bolt Jones | Cam Blake | Cam (8 rows)



A join table created with the JOIN syntax is a table reference list item that appears in the FROM clause before any where,group by or HAVING clause. Other table references, including table names or other JOIN clauses, can be included in the FROM clause if separated by commas. The tables generated by the connection are logically the same as any other tables listed in the FROM clause.

There are two main types of SQL join, CROSS join (unconditional connection) and conditional connection. A conditional connection can also be further subdivided according to the declared join condition (on,using, or NATURAL) and the way it is applied (INNER or OUTER connections).



Connection type
CROSS JOIN
{T1} CROSS JOIN {T2}

A cross join (cross join) receives two tables T1 and T2, each with N rows and M rows, and returns a join table that contains a cross product NXM bar record. For each row r1,t2 each line of T1, R2 is generated with R1 connection to the table row JR,JR contains all R1 and R2 fields. The CROSS join is actually a INNER join on TRUE.
Conditional JOIN
{T1} [NATURAL] [INNER | {left | Right | Full} [OUTER]] JOIN {T2} {on search condition | USING (Join column list)}

A conditional JOIN must declare its join condition by providing a (and only one) Natural,on, or using a keyword such as a. The ON clause accepts a search condition, which is the same as a WHERE clause. The using clause accepts a comma-delimited list of field names that must be in the join table, and joins those tables with those fields, and the resulting join table contains every other field for each common field and two tables. NATURAL is an abbreviation of a USING clause that lists all the common field names in two tables. Side effects of using using and NATURAL are that only one copy of each connected field appears in the result table (compared to the JOIN of the previously defined relational calculus).



[INNER] JOIN

For each row of T1, the R1 table has a row in T2 that satisfies the join condition with the R1.

For all joins, INNER and OUTER are optional. INNER is the default. Left,right, and full only for OUTER JOIN.

Left [OUTER] JOIN

First, perform a INNER JOIN. Then, if there is one row in T1 that does not meet the join condition for any T2 row, then a connection row is returned, and the T2 of the row is null.

Tip: The linked table unconditionally contains all the rows in the T1.

Right [OUTER] JOIN

First, perform a INNER JOIN. Then, if there is one row in T2 that does not meet the join condition for any T1 row, then a connection row is returned, and the T1 of the row is null.

Tip: The linked table unconditionally contains all the rows in the T2.

Full [OUTER] JOIN

First, perform a INNER JOIN. Then, if there is one row in T1 that does not meet the join condition for any T2 row, then a connection row is returned, and the T1 of the row is null. Similarly, if there is one row in T2 that does not meet the join condition for any T1 row, then a connection row is returned, and the T2 of the row is null.

Tip: The linked table unconditionally owns each row from the T1 and every line from the T2.

All types of joins can be linked together or nested together, and both T1 and T2 can be tables generated by the connection. We can use parentheses to control the order of joins, and if we don't actively control them, then the join order is left to right.
1.4.1.3. Aggregation operator
SQL is provided with some aggregation operators (e.g., Avg,count,sum,min,max), and these aggregate operators take an expression as an argument. This expression is evaluated as long as the row that satisfies the WHERE clause, and then the aggregation operator computes the set of the input value. Typically, the result of a cluster calculation of the entire SELECT statement is to produce a result. However, if a group is declared in a query, the database will perform a separate calculation for each group, and the aggregation results appear according to each group (see the next section).

Example 1-5. Gathered

We would like to know the average price of all parts inside the table, we can use the following query:

SELECT AVG (Price) as Avg_price from part;



The result:

Avg_price-----------14.5



If we want to know how many parts are stored in the table part, we can use the statement:

SELECT COUNT (PNO) from the part;
Get:
COUNT-------4




1.4.1.4. Group Aggregation
SQL allows us to divide the records within a table into groups. The aggregate operators described above can then be applied to these groups (that is, the value of the clustered operator is no longer an operation on the values of all declared columns, but the operation of all the values of a group.) This aggregate function is calculated independently for each group. ）

The grouping of records is implemented by keyword GROUP BY, followed by a list of properties that define the group's composition. If we use the statement group by A1,⃛, AK we divide the relationship into groups so that if and only if the two records agree on all attributes A1,⃛, AK, they are the same group.

Example 1-6. Gathered

If we want to know how many parts each vendor sells, we can write a query like this:

SELECT S.sno, S.sname, COUNT (SE. PNO) from SUPPLIER S, sells se WHERE s.sno = se. SNO GROUP by S.sno, S.sname;
Get:
SNO | sname | COUNT-----+-------+-------1 | Smith | 2 2 | Jones | 1 3 | Adams | 2 4 | Blake | 3



Then we have a look at what's going on. First make a connection to the table SUPPLIER and sells:

S.sno | S.sname | SE. PNO-------+---------+--------1 | Smith | 1 1 | Smith | 2 2 | Jones | 4 3 | Adams | 1 3 | Adams | 3 4 | Blake | 2 4 | Blake | 3 4 | Blake | 4



Then we put the same records of those attributes S.sno and S.sname in the group:

S.sno | S.sname | SE. PNO-------+---------+--------1 | Smith | 1 | 2--------------------------2 | Jones | 4--------------------------3 | Adams | 1 | 3--------------------------4 | Blake | 2 | 3 | 4



In our example, we have four groups and now we can apply the aggregate operator COUNT to each group to produce the final result of the query given above.



Note that if you want to make sense of the results of a query that uses the group by and clustered operators, the attributes that are used for grouping must also be present in the target list. All properties that do not appear in the GROUP by clause can only be selected by using the aggregate function. Otherwise, there will be no unique values associated with the other fields.

It is also important to note that aggregation is meaningless in aggregation, such as AVG (SNO), because SELECT only makes a round of grouping and aggregation. You can get the result by using a temporary table or by using a sub SELECT in the FROM clause to do the first level of aggregation.
1.4.1.5. Having
The HAVING clause movement is very much like a WHERE clause and is used only to compute the groups that satisfy the conditions given in the HAVING clause. In fact, the Where to filter out the input lines that we don't need before grouping and aggregating, and having groups that are not needed after group. Therefore, the result of a clustered function cannot be used by the WHERE. On the other hand, we have no reason to write a having that does not involve a clustered function. If your condition does not contain aggregation, then you can also write it in the where, so that you can avoid the aggregation of the rows you are about to discard.

Example 1-7. Having

If we want to know those vendors who sell more than one component, use the following query:

SELECT S.sno, S.sname, COUNT (SE. PNO) from SUPPLIER S, sells se WHERE s.sno = se. SNO GROUP by S.sno, S.sname has COUNT (SE. PNO) > 1;
and get:
SNO | sname | COUNT-----+-------+-------1 | Smith | 2 3 | Adams | 2 4 | Blake | 3




1.4.1.6. Subqueries
In the WHERE and having clauses, the subquery (sub selection) is allowed to be used wherever the numeric value is to be generated. In this case, the value must first come from the calculation of the subquery. The use of subqueries extends the ability to express SQL.

Example 1-8. Child query

If we want to know all the parts that are more expensive than the one named ' Screw ', we can use the following query:

SELECT * from part where price > (select the From part where pname= ' screw ');



The result:

PNO | PName | Price-----+---------+--------3 | Bolt | 15 4 | Cam | 25



When we examine the query above, we find that there are two SELECT keywords. The first one at the beginning of the query-we'll call it the outer select-and the other in the WHERE clause becomes an embedded query-we'll call it the inner select. Each record in an external select must first evaluate the inner select. After all the calculations have been done, we know the price of the records named ' Screw ', and then we can check for more expensive records. (In fact, in this case, the inner query only needs to be executed once, because it does not depend on the high-level state of the outer query.) )

If we want to know the suppliers who do not sell any parts (for example, we want to remove these vendors from the database), we use:

SELECT * from SUPPLIER S where not EXISTS (select * from sells se where SE. SNO = S.sno);



In our case, the result column will be empty because each vendor sells at least one part. Notice that we use the S.sno from the outer select in the inner select of the WHERE clause. As mentioned earlier, subqueries are computed once for each outer query, that is, the value of the S.sno is always obtained from the actual record of the outer SELECT.


1.4.1.7. Subqueries in from inside
The use of some special subqueries is to put them in the FROM clause. This feature is useful because such subqueries can output multiple columns and multiple rows, and subqueries used in expressions must produce a result. The subquery in from also allows us to have more than one round of grouping/aggregation characteristics without resorting to temporary tables.

Example 1-9. Subqueries from inside

If we want to know the highest average part price in all of our suppliers, we can't use MAX, but we can write this:

Select MAX (Subtable.avgprice) from (select AVG (p.price) as Avgprice to SUPPLIER S, part P, sells se WHERE s.sno = se. SNO and p.pno = SE. PNO GROUP by S.sno) subtable;
This subquery returns one row for each vendor (because of its GROUP by) and then we aggregate all the rows in the outer query.



1.4.1.8. Union, Intersect, Except (union, intersection, dissimilarity)
These operators calculate the association of tuples generated by two subqueries, and the differences in the intersection and set theory.

Example 1-10. Union, Intersect, Except

The following example is an example of UNION:

Select S.sno, S.sname, s.city from SUPPLIER s WHERE s.sname = ' Jones ' UNION SELECT s.sno, S.sname, s.city from SUPPLIER s W Here S.sname = ' Adams ';
Produce results:
SNO | sname | City-----+-------+--------2 | Jones | Paris 3 | Adams | Vienna



The following are examples of intersecting (INTERSECT):

Select S.sno, S.sname, s.city from SUPPLIER s WHERE s.sno > 1INTERSECT SELECT s.sno, S.sname, s.city from SUPPLIER s WH ERE S.sno < 3;
Produce results:
SNO | sname | City-----+-------+--------2 | Jones | Paris
The tuple returned by two queries is the one that sno=2.


Finally, an example of a EXCEPT:

Select S.sno, S.sname, s.city from SUPPLIER s where S.sno > 1EXCEPT SELECT s.sno, S.sname, s.city from SUPPLIER s where S.sno > 3;
The result:
SNO | sname | City-----+-------+--------2 | Jones | Paris 3 | Adams | Vienna