SQL

Sub-Queries

SUB QUERIES

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.

Example:

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’))

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.

SELECT INTO Statement

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: