SQL SUBQUERY

Introduction

• Subquery or Inner query or Nested query is a query in a query.
• SQL subquery is usually added in the WHERE Clause of the SQL statement.
• Generally we use subquery when we know how to search for a value using a SELECT statement, but do not know the exact value in the database.
• Subqueries are an alternate way of returning data from multiple tables.
• Subqueries can be used with the following SQL statements along with the comparision operators like =, <, >, >=, <= etc.

◦ SELECT

◦ INSERT

◦ UPDATE

◦ DELETE

Using Subquery in SQL Query :

Lets consider the student_details table which we have used earlier. If you know the name of the students who are studying science subject, you can get their id's by using this query below:

SELECT id, first_name

FROM student_details

WHERE first_name IN ('Rahul', 'Stephen');

but, if you do not know their names, then to get their id's you need to write the query in this manner:

SELECT id, first_name

FROM student_details

WHERE first_name IN (SELECT first_name

FROM student_details

WHERE subject= 'Science');

In the above sql statement, first the inner query is processed first and then the outer query is processed.

Using Subquery in insert statement:

Subquery can be used with INSERT statement to add rows of data from one or more tables to another table. Lets try to group all the students who study 

Maths in a table 'maths_group'.

INSERT INTO maths_group(id, name)

SELECT id, first_name || ' ' || last_name

FROM student_details WHERE subject= 'Maths'

Using Subquery in select statement:

A subquery can be used in the SELECT statement as follows. Lets use the product and order_items table defined in the sql_joins section.

select p.product_name, p.supplier_name, (select order_id from 

order_items where product_id = 101) as order_id from product p where 

p.product_id = 101;

Correlated Subquery

A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

SELECT p.product_name FROM product p

WHERE p.product_id = (SELECT o.product_id FROM order_items o

WHERE o.product_id = p.product_id);