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

What are the advanced queries and matters needing attention in SQL programming? I believe many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problems. Through this article, I hope you can solve this problem.

1. What is a subquery?

When a query is a condition of another query, it is called a subquery. Sub-queries can use several simple commands to construct powerful compound commands. Subqueries are most commonly used in the WHERE clause of the SELECT-SQL command. A subquery is an SELECT statement that is nested within one SELECT, SELECT...INTO statement, INSERT...INTO statement, DELETE statement, or UPDATE statement, or within another subquery.

Syntax: select.... from Table 1 where column 1 > (subquery)

The outer query becomes the parent query, and the query embedded in parentheses is called the child query. When SQL Server executes, it first executes the sub-query part, finds the value of the sub-query part, and then executes the entire parent query to return the * result.

Table join can also be used to view multi-table data, table join (join on...), table join can be replaced by subquery, but some subqueries can not be replaced by table join, subquery is more flexible, convenient and diverse, so it is suitable to be used as the filter condition of query.

For example:

The result is:

2.In and Not in subqueries

1 > if your subquery will return multiple rows of data, you can use the IN and NOT IN operators to construct the search conditions of the main query. The purpose of the IN and NOT IN operators is to test whether a given comparison value appears in a particular collection. As long as the data rows in the main query match any data rows returned by the subquery, the comparison result of the IN operator will be true. If the data rows in the main query do not match all the data rows returned by the subquery, the comparison result of the NOT IN operator will be true.

For example: SELECT * FROM student WHERE student_id IN (SELECT student_id FROM absence); name | sex | student_id |

+-+

| | Kyle | M | 3 | |

| | Abby | F | 5 | |

The 2 > IN and NOT IN operators can also be used in subqueries that will return multiple data columns. In other words, you can use them in datasheet subqueries. At this point, you need to use a data row constructor to give the comparison values that will be compared with each data column.

For example, SELECT last_name, first_name, city, state FROM president WHERE (city, state) IN (SELECT city, state FROM president WHERE last_name = 'Roosevelt'); +-+

| | last_name | first_name | city | state | |

+-+

| | Roosevelt | Theodore | New York | NY | |

| | Roosevelt | Franklin D. | Hyde Park | NY | |

+-+

3.Exists and Not Exists subquery

Exists can detect the existence of data. If the result of the subquery is not empty, Exists (subquery) returns true (true), otherwise returns false (false).

If Exists (subquery) statement

Exists, like In, also allows you to add the Not keyword to implement the reverse operation, and Not Exists means that it does not exist.

4. Considerations for subquery

Principles for using subqueries

1. A subquery must be placed in parentheses.

two。 Place the subquery to the right of the comparison condition to increase readability.

The subquery does not contain an ORDER BY clause. Only one ORDER BY clause can be used for a SELECT statement

And if specified, it must be placed in the * of the main SELECT statement.

The ORDER BY clause can be used and is necessary for Top-N parsing.

3. Two comparison conditions can be used in subqueries: single-row operators and multi-row operators.

Type of subquery

Single-row subquery: a query that returns only one row from the inner SELECT statement

Multi-row subquery: a query that returns multiple rows from an inner SELECT statement

Single-line subquery

A single-row subquery is a query that returns a row from an internal query. Use a single-line operator in this subquery type. One-line operators are listed in the slide.

5. Query method: join: merge rows in multiple datasets subqueries: nest one query in another query join: merge columns in multiple data tables

6. What is a related subquery?

Related subqueries depend on external queries. External queries are related to SQL Server subqueries, especially in the WHERE statements of subqueries. The related subquery works by executing the external query when a reference to the external query is found in the subquery and returning the results to the subquery. The subquery operation is then performed on the result set returned by the external query.

Execution performance of related subqueries: because the subqueries in the related subqueries are executed on the result set returned by the external query, the efficiency is definitely reduced. The performance of subqueries depends entirely on queries and related data. However, if the statements of the relevant subqueries are written efficiently, their execution performance can outperform those programs that use several joins and temporary tables.

After reading the above, have you mastered the methods of advanced queries and matters needing attention in SQL programming? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.