SQL subqueries with operations & examples

SQL Subqueries are also called inner queries or inner select or a nested queries. The statement containing the subquery is also called an outer query or outer select. It is a query within another SQL query and embedded within the WHERE or HAVING clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query. There is no limit to the number of nested subqueries you can create. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc for below mentioned activities

• Compare an expression to the result of the query.
• Determine if an expression is included in the results of the query.
• Check whether the query selects any rows.

Type of SQL subqueries

• Single row subquery: Returns zero or one row.
• Multiple row subquery: Returns one or more rows.
• Multiple column subqueries: 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 subquery.

Rules of SQL subqueries

• Subqueries must be enclosed within parentheses.
• A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
• An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery.
• Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.
• The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
• A subquery cannot be immediately enclosed in a set function.
• The BETWEEN operator cannot be used with a subquery. However, the BETWEEN operator can be used within the subquery.
• You can only use subqueries that return more than one row with multiple value operators, such as the IN or NOT IN operator.
• A subquery cannot be a UNION. Only a single SELECT statement is allowed.

Table: customers

customer_id	customer_nm	address	city	Zip	country
1	Alex Angles	230 Erwin Street	Woodland Hills	91367	USA
2	Frank Wilson	New Delhi Road	Delhi	87653	India
3	Daniel Perker	Hilton street	Paris	75016	France
4	Mark Punk	Italiano blvd	Berlin	10101	Italy
5	Thomas Wilson	1007 N Jackson street	Milwakee	78881	USA

Table: orders

order_id	customer_id	order_date	order_amt	Ship_id
10001	1	2021-01-01	100.00	1
10002	5	2021-06-01	150.00	10
10003	3	2021-07-01	1000.00	3
10004	4	2021-07-15	250.50	2
10005	3	2021-08-01	225.50	5

SQL subqueries with the SELECT Statement

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows

Syntax
SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])

Query
SELECT * FROM customers
WHERE customer_id IN 
(SELECT DISTINCT customer_id 
FROM orders 
WHERE order_amt > 200);

Explanation

Here the inner query will get the distinct customer ids that have an order amount of more than 200 dollars. So from the orders table, the customer id 3 & 4 have an order values of more than 200 dollars.

Result

customer_id	customer_nm	address	city	Zip	country
3	Daniel Perker	Hilton street	Paris	75016	France
4	Mark Punk	Italiano blvd	Berlin	10101	Italy

SQL subqueries with the INSERT Statement

The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions.

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

INSERT INTO premium_customers 
SELECT * FROM customers 
WHERE customer_id IN 
(SELECT DISTINCT customer_id 
FROM orders 
WHERE order_amount > 1000);

Explanation

The above query will insert the records of premium customers into a table called premium_customers, by using the data returned from the subquery. Here the premium customers are the customers who had placed order worth more than 1000 dollars.

Result

premium_customers

customer_id	customer_nm	address	city	Zip	country
3	Daniel Perker	Hilton street	Paris	75016	France

SQL subqueries with the UPDATE Statement

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.

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

Query
UPDATE orders
SET order_amount = order_amount + 10
WHERE customer_id IN 
(SELECT customer_id 
FROM customers 
WHERE country = ‘USA’);

The above statement will update the order value in the orders table for those customers who live in country USA, by increasing the current order value by 10 dollars.

Result

The customers who are from USA is 

order_id	customer_id	order_date	order_amt	Ship_id
10001	1	2021-01-01	110.00	1
10002	5	2021-06-01	160.00	10

The final orders table will be 

order_id	customer_id	order_date	order_amt	Ship_id
10001	1	2021-01-01	110.00	1
10002	5	2021-06-01	160.00	10
10003	3	2021-07-01	1000.00	3
10004	4	2021-07-15	250.50	2
10005	3	2021-08-01	225.50	5

SQL subqueries with the DELETE Statement

The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.

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

DELETE FROM orders
WHERE customer_id IN 
(SELECT customer_id 
FROM customers 
WHERE country = ‘France’);

Result

Below two rows will be deleted as the customer_id for country ‘France’ is 3.

order_id	customer_id	order_date	order_amt	Ship_id
10003	3	2021-07-01	1000.00	3
10005	3	2021-08-01	225.50	5

The final table is

order_id	customer_id	order_date	order_amt	Ship_id
10001	1	2021-01-01	100.00	1
10002	5	2021-06-01	150.00	10
10004	4	2021-07-15	250.50	2

SQL subquery Examples

Table: department
DEPT_ID DEPT_NAME        DEPT_BUDGET
-------- --------------- -------------
      50 IT                     165000
      60 Finance                 10000
      40 HR                     300000
      20 Management              35000
      80 Legal                  175000

Table: employee
EMP_ID    EMP_FNAME       EMP_LNAME         EMP_DEPT
--------- --------------- ---------------   ----------
   10     Michale         Stack                   50
   50     Ian             Moya                    60
   80     Norman          Lu                      50
   30     Jhon            Hustol                  60
   40     Jose            Sandes                  40
   60     Jack            Jose                    40
   81     Karl            Mayor                   50
   70     Henrey          Fox                     40
   51     Alex            Samuel                  50
   52     George          Ford                    20
   71     Carl            Samuel                  60
   61     Alan            Walker                  20
   82     Maria           Mendoza                 50

Question: Write a query in SQL to find the first name and last name of employees working for departments which budget amount is the second-lowest.

Query
SELECT emp_fname, emp_lname 
FROM employee 
WHERE emp_dept IN (
  SELECT dept_id
  FROM emp_dept
  WHERE dept_budget= (
    SELECT MIN(dept_budget)
    FROM department 
    WHERE dpt_budget >
 (SELECT MIN(dpt_budget) 
      FROM department )));

The HR department has the highest budget & the lowest budget department is finance. The second-lowest budget department is Management.

Result
emp_fname emp_lname
Alan  Walker
George         Ford

Question: Write a query to find the names of departments with more than two employees are working.

Query
SELECT dept_name FROM department
  WHERE dept_id IN
  (
    SELECT emp_dept
      FROM employee
      GROUP BY emp_dept
      HAVING COUNT(*) >2
  );

The department ID which have more than 2 employees are 50,60 & 40.

Result
dept_name
IT
HR
Finance

Question: Write a query in SQL to find the departments which sanction amount is larger than the average sanction amount of all the departments.

Query
SELECT *
  FROM department
  WHERE dept_budget >
  (
    SELECT AVG(dept_budget)
    FROM department
  );

The average department budget is 137000. The department which has move budget then this average are given below.

Result
DEPT_ID DEPT_NAME        DEPT_BUDGET
-------- --------------- -------------
      50 IT                     165000
      40 HR                     300000
      80 Legal                  175000

EXISTS vs NON EXISTS : Click Here ANY or ALL : Click Here IBM DB2 Manual :Click Here