Tutorial How To Manage Database By Using Subqueries

Swati Mishra

Content Writer
Subqueries

A subquery is defined as SELECT query. Subquery returns the single data or multiple data. Subqueries may be nested within the SELECT, INSERT, UPDATE and DELETE commands. We use the subqueries for retrieving data from the single table or multiple tables. Subqueries are used inside the Clauses which is WHERE or HAVING clause. These clauses such as WHERE and HAVING are comes in the SELECT, INSERT, UPDATE and DELETE statement. A SELECT query that contain subqueries, it may be single subquery or it may be multiple subqueries, this is called nested query.


Syntax

SELECT col_name [, col_name…]
FROM tab_name
WHERE col_name=(SELECT col_name from tab_name where col_name=<value>)

In above syntax of the SELECT statement is to retrieve data.
It is necessary to use subquery enclosed within the parentheses. It cannot be use the clauses such as ORDER BY or COMPUTE BY clause.

The SQL Server firstly calculates the inner query and gives the output to the outer query for the result. It is confirm that the outer query depends on the output of subquery.
Now we will discuss about the categories which comes in the subqueries.
Operate on list: By using IN clause, this type of query gives the output a single column with multiple data.

The syntax is here:

WHERE expression [NOT] IN (subquery)

Subquery with comparison operator:

These are the various comparison operator used in it such as (=, <>, >, <, !>, !<, <=, >=). This type of subquery gives the output with single column single data results. For outer query evaluation it is use the above given comparison operator. The error will occur when more than one value is return by the inner query.

The syntax is here:

WHERE expression comparison operator (subquery)

Subquery with modify comparison operator:

These are the various modified comparison operator such as ANY and ALL. This type of query can return the multiple data for outer query evaluation by using above given modified comparison operator.

The syntax is here:

WHERE expression comparison_operator[ANY / ALL] (subquery)

Subqueries with EXISTS keyword:

This type of query gives the output only TRUE or FALSE, It will decide the existence of data by using EXISTS keyword. It will check the existence of records in a table which is used in the inner query.
The syntax is here:
WHERE[NOT] EXISTS (subquery).

Subqueries with IN clause

The subquery by using IN clause it will give the output may be Zero or more values. We will take the example to describe the subquery.

The example is here

SELECT emp_id
FROM employee
WHERE sale_id IN(SELECT sale_id from sales)

From above example emp_id is the column name which is select from the table name employee and the second table name sales which select the sale_id which column name from the sales table. It will return the sale_id from the sales table then after emp_id from employee table.

Now we will take another example with NOT IN clause. NOT IN clause is the use as the IN clause.

The example is here:

SELECT book_id, book_name
FROM BOOK
WHERE book_id NOT IN (SELECT book_id
FROM AUTHOR
WHERE Type= ‘hindi’)

Subqueries with the EXISTS clause:

By using EXISTS clause, this type of query give the output in TRUE OR FALSE. According to the condition it specifies in the inner query and it checks the existence of data in the rows of table and passes the status of existence to the outer query to generate the result set. If the result of subquery contains any row then it will return the TRUE otherwise it will return the FALSE.

The EXISTS keyword contain an asterisk (*) in the SELECT list of the inner query. We will take the example to describe the Subquery with the EXISTS clause.

The example is here:
SELECT book_name
FROM BOOKS
WHERE EXISTS (SELECT *
FROM BOOKS
WHERE color= ‘blue’)
From above example it will return the book name from BOOKS table name where the color of book is blue.

Subqueries with the Aggregate functions:

It is possible to use the Aggregate function in the Subqueries. We will take the example of Subquery with the use of Aggregate function.
The example is here:
SELECT title
FROM Sales
WHERE Advance > (SELECT AVG (Advance)
FROM Sales
WHERE type= ‘business’)
From above example is to display the result title from the sales book where advance amount is greater than the Average advance amount where type of book is business.

NESTED Subquery:


A subquery that contains the single or more than subqueries that is called the NESTED subquery.
We can use the subqueries with the statement these are SELECT, INSERT, UPDATE, DELETE statements. There is no limitation of the subqueries. We will take the example and describe the nested subquery.

The example is here:

SELECT ‘Author name’= SUBSTRING (F_name, 1,1) + ‘.’+ L_name
FROM BOOKS
WHERE au_id IN (SELECT au_id
FROM titleauthor
WHERE Title_id= (SELECT Title_id
FROM titles
WHERE title= ‘ web design’)
From above example Author name from the table BOOKS and the select the author id from the titleauthor table name where the title_id is from the table titles where the title of book is the web design.
Correlated Subqueries:

A correlated subquery is also the query which is depends on the outer query for its calculation. In a Correlated subquery, The WHERE clause references a table in the FROM clause of the outer query. We will use the alias for the tables if the correlated subquery is implemented using the same table. We will take the example for this subqueries which is describe below.

The example is here

SELECT title, type, advance
FROM sales s1
WHERE s1.advance> (SELECT AVG(s2.advance)
FROM sales2 s2 WHERE s1.type = s2.type)

From the above example title from the sales table name where the s1 and s2 is the alias for the table sales and sales 2 where advance value is greater than the average advance value for the book of type.

Subquery with the modified comparison operator

ANY and ALL keywords are used for the modified comparison operator.
The ALL keyword will display TRUE, if all the value satisfies the comparison operator. It will display the FALSE when it’s no values satisfy the comparison operator.
The ANY keyword will display TRUE, if any value that satisfy the comparison operator. It will display the FALSE if no any value satisfies the comparison operator. Now we will take the example for this comparison operator.

The example is here

SELECT title_id, title
FROM sales
WHERE price> ALL(SELECT price from sales WHERE pub_id=’1001’)

From above example It will display the title and title id from the sales table where price is greater than the maximum price of book with the public id is 1001
 
Top