SQL Subqueries

SQL subqueries are queries attached to another query. Subqueries can be used:

• In the SELECT instruction;
• In the FROM instruction;
• In the WHERE condition.

What is a subquery in SQL?

The subquery can be nested in the SELECT, INSERT, UPDATE or DELETE instructions as well as in another subquery.  The subquery is usually added to the WHERE condition of the SQL SELECT operator. Comparison operators such as >, <, or = can be used. IN, ANY, or ALL. A subquery is also called an internal query. The operator containing a subquery is also called an external query. The internal query is executed before the parent query so that the results can be transmitted to the external query.

You can use the subquery in the SELECT, INSERT, DELETE or UPDATE instructions to perform the following tasks:

• Comparison of an expression with a query result;
• Determining whether an expression is included in the query result;
• Verifying whether the query chooses any strings.

Syntax:

• The SQL subquery (internal query) is executed before the main query (external query) is executed;
• The main query uses the result of the subquery execution.

Examples of SQL subqueries

In this section, we will consider how to use subqueries. We have the following two tables: ‘student’ and ‘marks’ with the common field ‘StudentID’:

students

markers

Now you need to create a query that identifies all students who get better grades than the student with StudentID – “V002”. But we don’t know the marks of the student with “V002”.

So we need to make two SQL subqueries in Select. One query returns marks (stored in the “Total_marks” field) for “V002”, and the second query selects students who get better grades than the first one.

The first query:

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

The result of the request:

The result of the request will be 80.

Using the result of this query, we have written another query to identify students who are getting better grades than 80.

Second request:

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

The result of the request:

The two queries listed identify students who receive better grades than StudentID “V002” (Abhay).

You can combine these two queries by attaching one query to the other. A subquery is a query within parentheses. Consider a subquery in an SQL example:

SQL code:

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');

The result of the query:

Graphic representation of an SQL subquery:

Subqueries: general rules

The syntax of the subquery is given below:

Syntax:

(SELECT [DISTINCT] subquery_selection_ arguments
FROM { table_name | presentation_name}
{ table_name | presentation_name } ...
[WHERE search terms]
[GROUP BY association expression [,association expression] ...]
[HAVING conditions_search]).

Subqueries: recommendations for use

Below are some recommendations to follow when using SQL subqueries:

• A subquery should be enclosed in parentheses;
• The subquery should be specified on the right side of the comparison operator;
• Subqueries cannot process their results, so the ORDER BY condition cannot be added to a subquery;
• Use single-line operators with single-line subqueries;
• If a subquery returns null to an external query, the external query will not return any strings when using comparison operators in the WHERE condition.

Types of subqueries

• Single-line subquery: returns zero or one line;
• Multi-line subquery: returns one or more lines;
• Multi-column subquery: returns one or more columns;
• Corrected subqueries: specify one or more columns in an external SQL instruction. This is called a correlated subquery because it is associated with an external SQL instruction;
• Nested subqueries: subqueries placed in another subquery.

You can also use a subquery inside the INSERT, UPDATE and DELETE instructions.

Subqueries with the INSERT instruction

The INSERT instruction can be used with SQL subqueries.

Syntax:

INSERT INTO table_name [ (column1 [, column2 ]) ].
SELECT [ *|столбец1 [, column 2 ]
FROM Table 1 [, Table 2 ]
[ WHERE VALUE OPERATOR ]

If we want to insert orders from the ‘orders’ table for which the value of advance_amount in the ‘neworder’ table is 2000 or 5000, we may use the following SQL code:

Example of a table: orders

ORDER_NUM ORDER_AMOUNT ADVANCE_AMOUNT ORDER_DATE CUST_CODE AGENT_CODE ORDER_DESCRIPTION
———- ———- ————— ——— ————— ————— ——————
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012

SQL code:

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

Result:

Subqueries with UPDATE instructions

In the UPDATE instruction you can set a new column value equal to the result returned by the single-line subquery. Below is the syntax and example of UPDATE with the SQL subquery.

Syntax:

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

If we want to change the order_date parameters in the ‘neworder’ table from ’15 -JAN-10′ for which the difference between order_amount and advance_amount is less than the minimum order_amount in the ‘orders’ table, we can use the following SQL code:

Example of a table: neworder

ORDER_NUM ORDER_AMOUNT ADVANCE_AMOUNT ORDER_DATE CUST_CODE AGENT_CODE ORDER_DESCRIPTION
———- ———- ————— ——— ————— ————— ——————
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012

SQL code:

UPDATE neworder
SET order_date='15-JAN-10'.
WHERE order_amount-advance_amount<
(SELECT MIN(order_amount) FROM orders);

Result:

Subqueries with DELETE instructions

Below is the syntax and example of using SQL subqueries with DELETE instruction.

Syntax:

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

If you want to remove orders from the “neworder” table, for which the advance_amount is less than the maximum advance_amount value from the “orders” table, you can use the following SQL code:

Example of a table: neworder

ORDER_NUM ORDER_AMOUNT ADVANCE_AMOUNT ORDER_DATE CUST_CODE AGENT_CODE ORDER_DESCRIPTION
———- ———- ————— ——— ————— ————— ——————
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012

SQL code:

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

Result:

SQL Subqueries. Subqueries in SQL with examples