SUBQUERY
Subquery Fundamentals
- A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this example a subquery is used as a column expression named MaxUnitPrice in a SELECT statement.
A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.
Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases, a join approach would yield better results. The following is an example showing both a subquery SELECT and a join SELECT that return the same result set:
A subquery nested in the outer SELECT statement has the following components:
- A regular SELECT query including the regular select list components.
- A regular FROM clause including one or more table or view names.
- An optional WHERE clause.
- An optional GROUP BY clause.
-
An optional HAVING clause.The SELECT query of a subquery is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified.
A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query. Individual queries may not support nesting up to 32 levels. A subquery can appear anywhere an expression can be used, if it returns a single value.
If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output (the select list of the outer query).
Statements that include a subquery usually take one of these formats: - WHERE expression [NOT] IN (subquery)
- WHERE expression comparison_operator [ANY | ALL] (subquery)
- WHERE [NOT] EXISTS (subquery)
There are three basic types of subqueries. Those that:
- Operate on lists introduced with IN, or those that a comparison operator modified by ANY or ALL.
- Are introduced with an unmodified comparison operator and must return a single value.
- Are existence tests introduced with EXISTS.
You can use subqueries in DML statements to:
- Copy data from one table to another
- Retrieve data from an inline view
- Update data in one table based on the values of another table
- Delete rows from one table based on rows in a another table
- Write your INSERT statement with a subquery.
- Do not use the VALUES clause.
- Match the number of columns in the INSERT clause with that in the subquery.
Inserting Using a Subquery as a Target
Verify the results.
Retrieving Data with a Subquery as Source
Updating Two Columns with a Subquery
Update the job and salary of employee 114 to match
the job of employee 205 and the salary of employee
168.
Updating Rows Based
on Another Table
Use subqueries in UPDATE statements to update rows
in a table based on values from another table.
Deleting Rows Based
on Another Table
Use subqueries in DELETE statements to remove rows
from a table based on values from another table.
Using the WITH CHECK OPTION Keyword
on DML Statements
- A subquery is used to identify the table and columns of the DML statement.
- The WITH CHECK OPTION keyword prohibits you from changing rows that are not in the subquery.
STIKOM SURABAYA
Riko Dwi Christian
D3 - Manajemen Informatika
0 komentar:
Posting Komentar