子查询

from ocp-047 Certified Book

Define subqueries
* A subquery is a SELECT statement contained within a SQL statement.
* The outer SQL statement is called the parent. The outermost level is the top
level.
* A top-level SQL statement containing a subquery may be a SELECT, INSERT,
UPDATE, or DELETE, or else a CREATE TABLE or CREATE VIEW.
* Subqueries may be nested within other subqueries.
* Many subqueries could function as standalone queries. Some are correlated,
meaning that they contain references that tie them into their parent queries.

Describe the types of Problems that subqueries Can solve
* A subquery can provide lookup data to assist a parent query in completing a
WHERE clause or something comparable.
* Subqueries can help combine multiple steps into a single query, reducing
what otherwise might be several consecutive SQL statements into a single
statement.
* Subqueries in a CREATE TABLE or INSERT or UPDATE statement can
draw from data from the database to populate database objects quickly.
* Subqueries can name queries for subsequent reference.

List the types of subqueries
* A single-row subquery returns one row of data to the parent query.
* A multiple-row subquery may return more than one row of data to the
  parent query.
* Multiple-column subqueries return two or more columns worth of data at
once to the parent query, which must test for all of the columns at once.
* Correlated subqueries use data from a parent query to determine their
own result.
* Scalar subqueries always return one value, represented in one column of one
row, every time.
* The multiple-column subquery may be of the single-row or multiple-row type
of subquery.
* A correlated subquery might be a single-row, multiple-row, or multiple-
  column subquery.

Write single-row and Multiple-row subqueries
* The results of a single-row subquery can be compared from within the parent
using a scalar comparison operator, such as the equal sign, or the greater-than
or less-than sign.
* The column names are not required to match in such a comparison, but the
datatypes must match, so that the parent query may compare columns of any
name to subquery columns of any name, provided the datatypes match.
* Multiple-row subqueries are compared differently to the parent query than
single-row, using the multiple-row comparison conditions, such as IN, ANY,
or ALL, in combination with single-row comparison operators such as >, to
avoid getting an execution error message.

Write a Multiple-Column subquery
* Multiple-column subqueries return several columns’ worth of data to the
parent query all at once.
* The parent query must compare all of the columns together; the datatypes 
of each expression comparison much match between the parent and the
subquery.
* Multiple-column subqueries may return single-row or multiple-row answers.

use scalar subqueries in sQL
* Scalar subqueries return data in the form of one value, in one column’s worth
of one row.
* Scalar subqueries may be used almost anywhere that any expression could
be used.

solve Problems with Correlated subqueries
* Correlated subqueries use data from the parent in subquery predicates to
determine what data to return to the parent query.
* Correlated subqueries may present some performance degradation; however,
they can perform tasks that could not otherwise be accomplished in a
single query.

update and Delete rows using Correlated subqueries
* The UPDATE and DELETE statements can use correlated subqueries.
* The UPDATE can use correlated subqueries in the SET or the WHERE
clause.
* The DELETE statement can use correlated subqueries in the WHERE clause.
use the eXists and not eXists operators
* The EXISTS operator can be used by a parent query to test a subquery and
determine if it returns any rows at all.
* NOT EXISTS reverses the findings of EXISTS.

use the WitH Clause
* The WITH clause can dynamically name a subquery so that the SELECT
statement following the WITH clause can reference that subquery by name,
treating it as a dynamic table in real time.
* Any subquery names assigned within the WITH clause are only good for that
statement; they are not stored in the database