Advanced queries for SQL Programming (subqueries) and considerations

1. What are sub-queries?

When a query is a condition of another query, it is called a subquery. Subqueries can use a few simple commands to construct powerful composite commands. Subqueries are most commonly used in the WHERE clause of the Select-sql command. A subquery is a SELECT statement that is 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)

Outside the query becomes the parent query, the parentheses embedded query becomes called a subquery. When SQL Server executes, the subquery part is executed, the value of the subquery part is calculated, the entire parent query is executed, and the final result is returned.

Table joins can also be used to view data from multiple tables (join on ... ), table connections can be replaced by subqueries, but some subqueries can not be replaced with table joins, sub-query is more flexible, convenient, various forms, suitable for the query as the filter criteria.

For example:

The result is:

2.In and not in subqueries

1> If your subquery returns multiple rows of data, you can construct the search criteria for the main query with the in and not in operators. The purpose of the in and not in operators is to test whether a given comparison value does not appear in a particular collection. As long as the data rows in the main query match any one of the data rows returned by the subquery, the in operator's comparison will be true. If the data rows in the main query do not match all the rows returned by the subquery, the not-in operator will result in a comparison of 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 columns of data. In other words, you can use them in a data table subquery. 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 Pres Ident 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 detect whether the data exists. If the result of the subquery is not NULL, then exists (subquery) returns True (True), otherwise false (false) is returned.

If Exists (subquery) statement

Exists and in are also allowed to add the NOT keyword to implement the inverse operation, not Exists to indicate that it does not exist.

4. Considerations for Sub-queries

<1> guidelines for 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 use only one 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 a subquery: the single-line operator and the multiple-line operators.
Types of <2> subqueries
Single-row subquery: Query that returns only one row from the inner SELECT statement
Multi-row subquery: Query that returns multiple rows from the inner SELECT statement
<3> single-line subquery
A single-line subquery is a query that returns a row from within the query. A single-line operator is used in the subquery type. A single-line operator is listed in the slide.

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

6. What is a correlated subquery?

correlated subqueries depend on external queries. External queries are associated with SQL Server subqueries, especially in the where statement of a 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: the efficiency of a subquery in a related subquery is definitely reduced because it executes on the result set returned by the external query. The performance of a subquery relies entirely on queries and related data. However, if the statement of the correlated subquery is written efficiently, its execution performance is better than that of the programs that use several connections and temporary tables.

Advanced queries for SQL Programming (subqueries) and considerations