Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to implement sub-query in SQL Server. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

1. Introduction to the concept of subquery

Subqueries can be nested in SELECT, INSERT, UPDATE, DELETE statements or other subquery statements. Subqueries generally play the role of the result set in the middle of the query. Subqueries are also called internal queries or internal selections. Statements containing subqueries are called external queries or external selections. Depending on the complexity of available system memory and other expressions in the query, nesting restrictions vary, and 32 layers of nesting can be generally supported. It is personally recommended to avoid multi-layer nesting as much as possible so that the readability and maintainability of SQL statements will be poor. Subqueries can generally be used where expressions are used.

2. Subquery classification

Single-valued subquery: the return value of the subquery belongs to a single. Commonly used with query columns and single-valued query conditions.

Multi-valued subquery: multiple returned values of a subquery. Common filter statements such as in and exists are used.

3. Usage scenarios of subqueries 3.1 use subqueries in the selection list

That appears in the selection list using a subquery as a column of values for the query results. In this case, calling a subquery can only return a single value. Can only be a single-valued subquery.

-- query student name, age, total score select a. Username, (select sum (score) from Score where userId=a.userId) totalScorefrom UserInfo a3.2 as query condition-- query department name is employee information single value query select username,age,address from Emp where deptno= of technical department (select top 1 deptno from DeptInfo where depName=' technical department') -- query the list of products with a product inventory of more than 10 tons and query select name,code from ProductInfo where Code in (select s.code from StockInfo s where qty > 20) The same uses of insert, update and delete are similar, for example:-- delete the product delete from ProductInfo where Code in (select s.code from StockInfo s where qty=0) with zero inventory;-- update the relationship between the remark field of the product with zero inventory and the update ProductInfo set remark=' tag 'where Code in (select s.code from StockInfo s where qty=0) 4, subquery and join query.

Many SQL statements containing subqueries can be expressed in the form of join queries, and there is little difference in performance between statements containing subqueries and semantically equivalent statements without subqueries. However, in the case of checking the existence, the performance of the join query is better, and the larger the amount of data, the more efficient the join query will be.

For example:

-- query the student's name, age, total score select min (a.username), min (a.age), sum (score) totalScore from UserInfo a left join Score b on a.userId=b.userId5, including the difference between in and its sub-query

In subquery: the result is a list of 0 or more values, indicating that the value is equal to the relationship.

Subquery: it only returns true or false. Indicates the act of being.

Normally, in and exist are interchangeable.

When the null value does not exist in the subquery result, the query result obtained by not in and not exists is the same, and the query plan generated is the same.

The query record is 0 when the value in in is null; when the value in exists is null, it means that the query condition does not work.

Example:

Select name,age UserInfo where userId in (1, 2, 3, 4); select name,age UserInfo where exists (1, 2, 3, 4)

Note: for the efficiency of EXISTS and IN, exists is usually more efficient than in, but it depends on the actual situation: IN is suitable for the situation where the appearance is large and the inner table is small; EXISTS is suitable for the case of small appearance and large inner table.

6. Subquery containing comparison operator

A subquery can consist of a comparison operator (=, >, > =,!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.