A sub query can be defined as a group of nested SELECT statements inside a SELECT, INSERT, UPDATE or DELETE statement. Sub query can also be used inside the WHERE or HAVING clauses of the outer SELECT, INSERT, UPDATE or DELETE statements. SELECT statements containing one or more sub queries are called nested queries.
The command syntax is:
          (SELECT [ALL|DISTINCT] suquery_select_list
          [FROM {table_nmae | view_name}
                   [[,table_name2|view_name2}]                                                             [..,{table_name16|view_name16{]]
          [WHERE clause]
          GROUP BY clause]
          [HAVING clause])




A subquery must be enclosed within parentheses and cannot use ORDER BY, COMPUTE BY or FOR BROWSE clauses. SQL Server does not implement any restriction on level of nesting while using subqueries, SQL Server imposes restrictions on the number of tables or views used in a subquery or a join.
SQL Server evaluates the inner query first and returns the result to the outer query for the final result set.
The outer query always depends on the evaluation result of the subquery.
Subqueries can be divided into three catefories depending upon the values they return;
Ø       Sub queries that operate on lists: this type of query returns single-column-multiple values results and are implemented using the IN clause. The syntax is as follows:
WHERE expression [NOT] IN (subquery)




Ø       Subqueries that are introduced with an unmodified comparison o-erator: this type of query returns single column-single value results for outer query evaluation and is implemented using unmodified comparison operators(operators without the ANY or ALL  keywords) the syntax is as follows:
WHERE expression comparison_operator [ANY|ALL] (subquery)




Ø       Subqueries that check for the existence of data: this type of query checks for the existence of records in a table that are used in the inner query, and returns either  a TRUE or a FALSE VALUE based on the existence of data. This is implemented using the EXISTS keyword. The syntax is as follows:
WHERE [NOT] EXISTS (subquery)




SubQueries With IN

            A subquery introduced with IN returns zero or more values. Consider the following example where all author ID’S, from the TITLEAUTHOR table, are displayed whose books are sold:
SELECT au_id
          FROM titleauthor
          WHERE title_id IN (SELECT  title_id FROM sales)




SQL Server returns a list of all title IDs to the main query then lists all the authors, whose books are sold, in the result set.
Consider the following example where the server returns a list of publisher IDs to the main query, and then determines whether each publisher’s pub_id is in that list:
          SELECT publisher=pub_name
          FROM publishers
          WHERE pub_id IN ( SELECT pub_id FROM titles WHERE type=’business’)




The inner query is evaluated first and then the result set is sent to the outer query.
Consider another subquery with the IN clause:
SELECT type=type, Average=AVG(ytd_sales)
          FROM titles
          WHERE type IN (SELECT type FROM titles
WHERE title=” the busy executive’s  database guide” or title=’Is Anger the Enemy?’) GROUUP BY type




The inner query is evaluated first and then the result set is sent to the outer query.
The NOT IN clause is used in the same way as the IN clause. Consider the following example.
SELECT pub_id, pub_name
          FROM publishers
          WHERE pub_id NOT IN (SELECT pub_id FROM titles
                   WHERE type=’mod_cook’)




Sub Queries with EXISTS

            The subquery, when used with the EXISTS clause, always returns data in terms of TRUE OR FALSE and passes the status to the outer query to produce the results set. The subquery returns a TRUE value if the result set contains any rows.
The query introduced with the EXISTS keyword differs from other queries. The EXISTS keyword is not preceded by any column name, constant or there expression and it contains an asterisk (*) in the SELECT list.
1. SELECT pub_name
          FROM publishers
          WHERE EXISTS (SELECT * FROM titles WHERE type=’business’)
          2. SELECT pub_name
          FROM publishers
          WHERE EXISTS (SELECT * FROM publishers WHERE City=’Paris’)



Aggregate functions can also be used in subqueries. Consider the following example which displays the titles of all those books for which the advance is more than the average advance of business related books.
SELECT Title=title
          FROM titles
          WHERE advance>(SELECT AVG (advance) from titles
                                      WHERE type=’business’)



Subquery Restrictions:
The restrictions imposed are:
Ø       The column list of the select statement of a subquery introduced with the comparison operator can include only one column.
Ø       The column used in the WHERE clause of the outer query should be compatible with the column used in the select list of the inner query.
Ø       The DISTINCT keyword cannot be used with the subqueries that include the GROUP BY clause.
Ø       The ORDER BY clause, GROUP BY clause and INTO keyword cannot be used in a subquery, because a subquery cannot manipulate its result internally.
Ø       Do not specify more than one column name in the subquery introduced with the EXISTS keyword.
Ø       A view created with a subquery cannot be updated.
Nested Sub Queries:
          A sub query can contain one or more subqueries. There is no restriction in the number of subqueries that you can include with SELECT, INSERRT, UPDATE or DELETE statements.
1. SELECT ‘Author Name’=SUBSTRING (au_fname, 1,1)+’.’+au_lastname
FROM authors  WHERE au_id IN(SELECT au_id  FROM titleauthor          WHERE title=’Net Etiquette’))
2. SELECT ‘Author ID’=au_id, Name=SUBSTRING (au_fnmae, 1,1) + ‘.’+au_lname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHERE type=’business’))




SELECT title_id=title_id,  Title=title
FROM title
FROM  titles
WHERE pub_id=’0736’)
Lists all the titles along with their IDs from the titles table where price is greater than the maximum price of books published by the publisher with publisher ID 0736.
SELECT title_ID = title_id, Title = title
FROM titles
WHERE price >ANY (SELECT price
FROM titles
WHERE pub_id = `0736`)
Lists all the titles along with their titles IDs from the titles table where price is greater than the minimum price of books published by the publisher with publisher ID 0736.
SELECT publisher_ID = pub_id, Name = pub_name
FROM publishers
WHERE city = ANY (SELECT city FROM authors)
Lists all the publishers  where city is sane as of any author.
A SELECT statement with an INTO clause is used to store the result set in a new table without a data definition process. The SELECT INTO statement creates a new table, if the table already exists then the operation fails.
The syntax of the SELECT INTO statement is:
SELECT columns_list
          INTO new_table_name
          FROM table_names
          WHERE conditions



The SELECT INTO clause creates a permanent table if the select into/bulk copy database option is set. If the select into/bulkcopy database option is not set, then only local temporary tables (prefixed by #), and global temporary tables (prefixed by ##) can be created.
The select into/bulkcopy database option can be set using the following command:
sp_dboption  ‘pubs’, ‘select into / bulkcopy ‘, true




 SELECT title_id, title
INTO newtitles
From titles
WHERE price >$15
Column names in the new table can be changed while specifying the columns in the SELECT statement. Consider the following example with the new column names:
SELECT Title _ID = title _id, Title_Name =title
INTO new titles 1
FROM titles
WHERE advance >$7000




Post a Comment


This site uses Akismet to reduce spam. Learn how your comment data is processed.