In it's simplest form, a subquery can be described as a query that is nested inside another query, or inside another subquery Subqueries are used to retrieve data from one table based on data in another table.
A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. They generally are used when tables have some kind of relationship. In fact, many SQL statements that include subqueries can be alternatively formulated as joins.
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.
///////////////////////////You can also use the Date Format: WHERE OrderDate BETWEEN '2008-06-01' AND '2008-08-12');
//////////////// Ready to use ///////////Did you know... Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Below is an example showing both a subquery SELECT and a join SELECT that return the same result set.
A subquery is subject to a number of restrictions:
The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively).
If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.
The ntext, text and image data types are not allowed in the select list of subqueries.
Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.
The DISTINCT keyword cannot be used with subqueries that include GROUP BY.
The COMPUTE and INTO clauses cannot be specified.
ORDER BY can only be specified if TOP is also specified.
A view created with a subquery cannot be updated.
The select list of a subquery introduced with EXISTS by convention consists of an asterisk (*) instead of a single column name. The rules for a subquery introduced with EXISTS are identical to those for a standard select list because a subquery introduced with EXISTS constitutes an existence test and returns TRUE or FALSE rather than data.
In the following example, the pub_id column in the WHERE clause of the outer query is implicitly qualified by the table name in the outer query s FROM clause, publishers. The reference to pub_id in the select list of the subquery is qualified by the subquery s FROM clause, that is, by the titles table.
USE pubs SELECT pub_name FROM publishers WHERE pub_id NOT IN (SELECT pub_id FROM titles WHERE type = 'business' ///////////// SELECT Name FROM SalesLT.Product WHERE ProductID NOT IN (SELECT ProductID FROM SalesLT.ProductCategory WHERE Name = 'Bikes') //////////The general rule is that column names in a statement are implicitly qualified by the table referenced in the FROM clause at the same level.
Here s what the query looks like with these implicit assumptions specified:
USE pubs SELECT pub_name FROM publishers WHERE publishers.pub_id NOT IN (SELECT titles.pub_id FROM titles WHERE type = 'business')It is never wrong to state the table name explicitly, and it is always possible to override implicit assumptions about table names with explicit qualifications.
/////////////Delete /////////////////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.
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.
Note
