SQL programming-advanced queries and precautions

SQL programming-advanced queries and precautions

1. What is a subquery?

When one query is a condition of another query, it is called a subquery. For subquery, you can use several simple commands to Construct Powerful composite commands. Subqueries are most commonly used in the WHERE clause of SELECT-SQL commands. A subquery is a SELECT statement nested in a SELECT, SELECT... INTO statement, INSERT... INTO statement, DELETE statement, or UPDATE statement, or nested in another subquery.

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

The external query is a parent query, and the query embedded in parentheses is called a subquery. When SQL Server executes the subquery, it first executes the subquery part, obtains the value of the subquery part, then executes the entire parent query, and returns the final result.

You can also use table join (join on...) to view data in multiple tables ...), table connections can be replaced by subqueries, but some subqueries cannot be replaced by table connections. subqueries are flexible, convenient, and diverse, and are suitable for filtering queries.

For example:

Result:

2. In and Not In subqueries

1> if your subquery returns multiple data rows, you can use the IN and not in operators to construct the search conditions for the primary query. The IN and not in operators are used to test whether a given comparison value exists IN a specific set. As long as the data rows IN the primary query match any data rows returned by the subquery, the comparison result of the IN operator is true. If the data rows IN the primary query do NOT match all data rows returned by the subquery, the comparison result of the not in operator is true.

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

+ ------- + ----- + ------------ +

| Kyle | M | 3 |

| Abby | F | 5 |

2> the IN and not in operators can also be used IN subqueries that return multiple data columns. In other words, you can use them in data table subqueries. In this case, you need to use a data row constructor to compare the value with each data column.

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 subqueries

Exists can check whether data Exists. If the subquery result is not null, Exists (subquery) returns true (true); otherwise, false ).

If Exists (subquery) Statement

Like In, Exists allows the addition of the Not keyword to perform the inverse operation. Not Exists indicates that it does Not exist.

4. subquery considerations

<1> subquery principles

1. A subquery must be placed in parentheses.

2. Place the subquery on the right of the comparison condition to improve readability.

The subquery does not contain the order by clause. Only one order by clause can be used for a SELECT statement,

If it is specified, it must be placed at the end of the main SELECT statement.

The order by clause can be used and is required for Top-N analysis.

3. Two Comparison conditions can be used in a subquery: single-line and multi-line operators.

<2> subquery type

Single Row subquery: Only one row of query is returned from the SELECT statement.

Multi-row subquery: returns a multi-row query from an in-SELECT statement.

<3> Single Row subquery

A single row subquery is a query that returns a row from an inner query. Use a single row operator in this subquery type. The single line operators are listed on the slides.

5. query method:<1> join: Merge rows in multiple datasets <2> subquery: nest one query in another query <3> join: Merge columns in multiple data tables.

6. What are related subqueries?

Related subqueries depend on external queries. External queries are related to SQL Server subqueries, especially in the WHERE Statement of subqueries. The subquery method is to execute an external Query when the reference of an external query is found in the subquery, and return the result to the subquery. Then, perform the subquery operation on the result set returned by the external query.

Performance of related subqueries: As the subqueries in related subqueries are executed in the result set returned by external queries, their efficiency will definitely decrease. The performance of subqueries depends entirely on queries and related data. However, if the related subquery statements are written efficiently, the execution performance of the statements is better than that of the programs that use several connections and temporary tables.

Blog Source: http://www.cnblogs.com/yimeng-com/archive/2015/05/03/4474746.html