w3resource logo


>Understanding Sql subqueries

SQL Subqueries

rating SQL subqueries has average rating 7 out of 10. Total 95 users rated.

<<PreviousNext>>

Introduction

A subquery is a SQL query nested inside a larger query.

  • A subquery may occur in :
    • - A SELECT clause
    • - A FROM clause
    • - A WHERE clause
  • The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.
  • A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
  • You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, or ALL.
  • A subquery can be treated as an inner query, which is a SQL query placed as a part of another query called as outer query.
  • The inner query executes first before its parent query so that the results of inner query can be passed to the outer query.

Syntax :

sql subquery syntax

  • The subquery (inner query) executes once before the main query (outer query) executes.
  • The main query (outer query) use the subquery result.

SQL Subqueries Example :

In this section, you will learn the requirements of using subqueries. We have the following two tables 'student' and 'marks' with common field 'StudentID'.

student         marks
            student                                        marks

Now we want to write a query to identify all students who get better marks than that of the student who's StudentID is 'V002', but we do not know the marks of 'V002'.
- To solve the problem, we require two queries. One query returns the marks (stored in Total_marks field) of 'V002' and a second query identifies the students who get better marks than the result of the first query.

First query :

SELECT *  
FROM `marks`  
WHERE studentid = 'V002';

Query result :

student query

The result of the query is 80.
- Using the result of this query, here we have written another query to identify the students who get better narks than 80. Here is the query :

Second query :

SELECT a.studentid, a.name, b.total_marks
FROM student a, marks b
WHERE a.studentid = b.studentid
AND b.total_marks >80;

Query result :

student marks query

Above two queries identified students who get better number than the student who's StudentID is 'V002' (Abhay).

You can combine the above two queries by placing one query inside the other. The subquery (also called the 'inner query') is the query inside the parentheses. See the following code and query result :

SELECT a.studentid, a.name, b.total_marks
FROM student a, marks b
WHERE a.studentid = b.studentid AND b.total_marks >
(SELECT total_marks
FROM marks
WHERE studentid =  'V002');

Query result :

student marks query

Pictorial Presentation of SQL Subquery :

sql subqueries

Subqueries : General Rules

A subquery SELECT statement is almost similar to the SELECT statement and it is used to begin a regular or outer query. Here is the syntax of a subquery :

(SELECT [DISTINCT] subquery_select_argument
FROM {table_name | view_name}
{table_name | view_name} ...
[WHERE search_conditions]
[GROUP BY aggregate_expression [, aggregate_expression] ...]
[HAVING search_conditions])

Subqueries: Guidelines

There are some guidelines to consider when using subqueries :
- A subquery must be enclosed in parentheses. 
- A subquery must be placed on the right side of the comparison operator. 
- Subqueries cannot manipulate their results internally, therefore ORDER BY clause cannot be added in to a subquery.You can use a ORDER BY clause in the main SELECT statement (outer query) which will be last clause.
- Use single-row operators with single-row subqueries. 
- If a subquery (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.

Type of Subqueries

- Single row subquery : Returns zero or one row.
- Multiple row subquery : Returns one or more rows.
- Multiple column subquery : Returns one or more columns.
- Correlated subqueries : Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL statement.
- Nested subqueries : Subqueries are placed within another subqueries.

In the next session we have thoroughly discussed the above topics. Apart from the above type of subqueries you can use subquery inside INSERT, UPDATE and DELETE statement. Here is a brief discussion :

Subqueries with INSERT statement

INSERT statement can be used with subqueries. Here is the syntax and an example of subqueries using INSERT statement.

INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ];

If we want to insert those orders from 'orders' table which have the advance_amount 2000 or 5000 into 'neworder' table the following SQL can be used:

Sample table : orders

INSERT INTO neworder
SELECT * FROM  orders
WHERE advance_amount in(2000,5000);

Output

subquery Insert  output

To see more details of subqueries using INSERT statement click here.

Subqueries with UPDATE statement

In an UPDATE statement, you can set new column value equal to the result returned by a single row subquery. Here is the syntax and an example of subqueries using UPDATE statement.

UPDATE table  SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)     
[ WHERE) ]

If we want to update those ord_date in 'neworder' table with '15-JAN-10' which have the difference of ord_amount and advance_amount is less than the minimum ord_amount or 'orders' table the following SQL can be used:

Sample table : neworder

UPDATE neworder
SET ord_date='15-JAN-10'
WHERE ord_amount-advance_amount<
(SELECT MIN(advance_amount) FROM orders);

subquery Update  output

To see more details of subqueries using UPDATE statement click here.

Subqueries with DELETE statement

DELETE statement can be used with subqueries. Here is the syntax and an example of subqueries using DELETE statement.

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME    
FROM TABLE_NAME)    
[ WHERE) ]

If we want to delete those orders from 'neworder' table which advance_amount are less than the maximum advance_amount of 'orders' table, the following SQL can be used:

Sample table : neworder

DELETE FROM neworder
WHERE advance_amount<
(SELECT MAX(advance_amount) FROM orders);

subquery delete   output

To see more details of subqueries using DELETE statement click here.

Key points to remember

What Next ?

Single Row Subqueries
Multiple Row and Column Subqueries
Corelated subqueries using aliases
Nested subqueries

Note : Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.



<<PreviousNext>>