Database Principles and Applications (SQL Server) Notes Chapter 4 Nested Query and Other Query Clauses

table of Contents

Preface

One, nested query

(1) Subquery

(B) ANY or ALL predicate subquery and comparison operation subquery

1. ANY subquery

2. ALL subquery

(3) EXISTS subquery

2. Other query clauses (collective query)

(1) UNION clause (and operation)

(Two) INTERSECT clause (interaction operation)

(Three) EXCEPT clause (difference operation)

(4) INTO clause

(5) CTE clause

(6) TOP predicate

to sum up


Preface

In this content, we mainly master the nested query and other query clauses of the database. We will introduce a few query clauses and use a few examples to master the query techniques of nested queries and other query clauses.

One, nested query

(1) Subquery

When a SELECT statement cannot complete the query task, the query results of another or more SELECT statements are required as part of the query conditions. This query is called a nested query, also called a subquery, and the processing process of the nested query is From the inside out.

The format of the subquery:

SELECT <列名1>... (列名n)FROM <表1>...(表n)WHERE <列名> <操作符>(    SELECT子查询)

To put it simply, the query results of the lower nested SELECT subquery are queried as the query conditions of the upper SELECT subquery.

The IN subquery is used to judge whether a given value is in the result of the subquery, that is, the operator in the above WHERE <column name> <operator> is IN. If NOT is added in front of the operator IN, the returned The value is opposite.

Example 1. According to the table given below, use T-SQL statements to query all product information that is not in the same product category as Sprite.
sql statement:
USE SalesSELECT *FROM ProductWHERE CategoryID NOT IN(SELECT CategoryIDFROM ProductWHERE ProductName='雪碧')
Test Results:

(B) ANY or ALL predicate subquery and comparison operation subquery

The sub-query of the comparison operation refers to the association between the parent query and the sub-query using comparison operators, that is, "<> = <= >= != !< !>" and other comparison operators.

1. ANY subquery

If any value in the subquery result meets the comparison condition, it will be returned, otherwise the returned value is the opposite. The operator here is ANY.

Example 2. According to the table given below, use T-SQL statements to query the information of all products with a smaller inventory than any one of Coca-Cola.
sql statement:
USE SalesSELECT *FROM ProductWHERE Stocks<ALL(SELECT StocksFROM ProductWHERE ProductName='可口可乐')
Test Results:

2. ALL subquery

Any value in the subquery result will be returned if it meets the comparison condition, otherwise the returned value will be the opposite. The operator here is ALL.

Example 3. According to the table given below, use T-SQL statements to query product information that is more expensive than Coca-Cola.
sql statement:
USE SalesSELECT *FROM ProductWHERE Price>ALL(SELECT PriceFROM ProductWHERE ProductName='可口可乐')
Test Results:

(3) EXISTS subquery

The EXISTS subquery judges whether there is a record in the result of the subquery (whether it is an empty table), if there is, the result is true, otherwise it is false, and the return value is the opposite.

Example 4. According to the table given below, use T-SQL statements to query the commodity information of all commodities that belong to the commodity category.
sql statement:
USE SalesSELECT *FROM ProductWHERE EXISTS(SELECT *FROM CategoryWHERE Category.CategoryID=Product.CategoryID AND CategoryName='日用品')
Test Results:
Example 5. According to the above table, use the T-SQL statement to query the product ID and name of all the spices and beverages in the product.
sql statement:
USE SalesSELECT ProductID,ProductNameFROM ProductWHERE NOT EXISTS(SELECT *FROM CategoryWHERE Category.CategoryID=Product.CategoryID AND CategoryName='日用品' )
Test Results:

2. Other query clauses (collective query)

(1) UNION clause (and operation)

The UNION clause, also known as the union operation. That is, two or more query results can be combined into one result set. Note that the number of columns and the order of the columns in each statement must be the same, and the data types must be compatible.

The format is as follows:

SELECT 语句UNIONSELECT 语句UNION...
Example 6. According to the table given below, use the T-SQL statement to query the product names of all products with product category 1 and product category 2.
sql statement:
USE SalesSELECT ProductNameFROM ProductWHERE CategoryID=1UNIONSELECT ProductNameFROM ProductWHERE CategoryID=2
Test Results:

(Two) INTERSECT clause (interaction operation)

The INTERSECT clause, also known as the intersection operation, is used to compare two query results, that is, to return all non-duplicate values ​​in the two query results on both sides of the operand INTERSECT. Pay attention to the number of columns and the order of the columns in each statement Must be the same, and the data types must be compatible.

The format is as follows:

SELECT 语句INTERSECTSELECT 语句
Example 7. According to the table given below, use T-SQL statements to query all employees who have paid wages on April 10, 2020 and wages on May 10, 2020.
sql statement:
USE YGGZSELECT EmpNOFROM SalayWHERE PayDate='2020-4-10'INTERSECTSELECT EmpNOFROM SalayWHERE PayDate='2020-5-10'
Test Results:

(Three) EXCEPT clause (difference operation)

The EXCEPT clause, also known as the intersection operation, is used to compare the results of two queries, that is, return from the left query all the unique values ​​not found in the right query. Note that the number of columns and the order of the columns in each statement must be the same , And the data types must be compatible.

The format is as follows:

SELECT 语句EXCEPTSELECT 语句
Example 8. According to the table given in the previous question, use T-SQL statements to query all employees who have paid wages on April 10, 2020 but have not paid wages on May 10, 2020.
sql statement:
USE YGGZSELECT EmpNOFROM SalayWHERE PayDate='2020-4-10'EXCEPTSELECT EmpNOFROM SalayWHERE PayDate='2020-5-10'
Test Results:

(4) INTO clause

The INTO clause is used to insert the results of the query into the new table created.

(5) CTE clause

The CTE clause is used to specify a temporary result set.

(6) TOP predicate

If you only need to get the first few items of the query result, you must use the TOP predicate to limit the query result set, that is, use TOP n [PERCENT], that is, list the top n items of the query result or list the top of the query result %item.

The format is as follows:

TOP n [ 数字 ] [ 百分数% ]

to sum up

The above is the whole content of the principle and application of this database. Thank you for your reading and support. If there is any improper expression or code, please point it out! Your points and suggestions can bring great motivation to the author! ! !