Advanced queries and Considerations for SQL programming

1. What is a subquery?

When a query is a condition of another query, it is called a subquery. Subqueries can construct powerful compound commands using a few simple commands. Subqueries are most commonly used in the WHERE clause of the Select-sql command. A subquery is a SELECT statement that is nested within 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 outside query becomes the parent query, and parentheses embedded in the query become called subqueries. When SQL Server executes, the subquery section is executed first, the subquery part is evaluated, and the entire parent query is executed, and the final result is returned.

View multiple table data can also use table joins, table joins (join on ...), table joins can be replaced with subqueries, but some subqueries can not be replaced with table joins, subqueries are flexible, convenient, diverse, suitable for the filter criteria as a query.

For example:

The results are:

2.In and not in subquery

1> If your subquery will return multiple rows of data, you can construct the search criteria for the main query with the in and not operator characters. The purpose of the in and not in operators is to test whether a given comparison value is present in a particular set. As long as the data row in the main query matches any one of the rows returned by the subquery, the in operator's comparison results will be true. If the row of data in the main query does not match all rows returned by the subquery, the comparison 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 return multiple data columns. In other words, you can use them in a datasheet subquery. At this point, you need to use a data row builder to give the comparison values that will be compared to each data column.

For example: Select Last_Name, first_name, city, state from President WHERE (city, State) in (select City, state from President Whe RE 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 detect whether the data exists. If the result of a subquery is not empty, the EXISTS (subquery) returns True (true), otherwise the false (false) is returned.

If Exists (subquery) statement

Exists, as in, also allows the addition of the NOT keyword to implement the reverse operation, and not Exists indicates that it does not exist.

4. Sub-query considerations

<1> the principle of using subqueries

1. A subquery must be placed in parentheses.

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

The subquery does not contain an ORDER BY clause. You can only use an ORDER BY clause for a SELECT statement,

And 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. There are two comparison criteria that can be used in subqueries: Single-line operators and multi-line operators.

<2> type of subquery

Single-line subqueries: Only one row of queries returned from the SELECT statement

Multiline subquery: Returning multiple rows of queries from a SELECT statement

<3> Single-line subquery

A single-line subquery is a query that returns one row from an inner query. Use a single line operator in the subquery type. A single line operator is listed on the slide.

5. Methods of querying:<1> Union: Merging rows <2> subqueries in multiple datasets: Nesting one query in another: <3> joins-merging columns from multiple data tables

6. What are related subqueries?

dependent subqueries depend on external queries. An external query is associated with a SQL Server subquery, especially in the where statement of the subquery. Related subqueries work by executing an external query when a reference to an external query is found in a subquery, and the result is returned to the subquery. The subquery operation is then performed on the result set returned by the external query.

Execution performance of correlated subqueries: because subqueries in the correlated subquery are executed on the result set returned by the external query, their efficiency is definitely reduced. The performance of a subquery depends entirely on the query and related data. However, if the statements of related subqueries are written efficiently, they perform better than those that use several connections and temporary tables.