SQL Subquery – Types of Subqueries in SQL

1. Objective – Subquery in SQL

In our last tutorial, we discussed SQL Sequence. Today, in this SQL tutorial, we are going to learn about SQL Subquery. First, we will see the meaning of Subqueries in SQL. Moreover, we will look at types of SQL Subquery with syntax and example.

So, let us start SQL Subquery tutorial.

SQL Subquery – Types of Subqueries in SQL

2. What is SQL Subquery?

A Subquery or Inner query or a Nested query in SQL is a query inside another SQL query and inserted inside the WHERE clause.
A SQL Subquery is used to return information that will be used in the primary query as a condition to additionally limit the information to be recovered.
Have a look at SQL Like Clause
Subqueries in SQL can be used with the SELECT, INSERT, UPDATE, and DELETE command alongside the operators like =, <, >, >=, <=, IN, BETWEEN, and so on. 
There are a couple of standards that we should consider in SQL Subquery–

• Subqueries in SQL must be encased inside brackets.
• A SQL subquery can have just a single section in the SELECT clause, except if different segments are in the principal query for the subquery to analyze its chose segments.
• An ORDER BY charge can’t be used in a subquery, in spite of the fact that the principal query can use an ORDER BY. The GROUP BY charge can be used to play out indistinguishable capacity from the ORDER BY in a subquery.
• SQL Subqueries that arrival in excess of one column must be used with different value operators, for example, the IN operator.
• The SELECT rundown cannot exclude any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
• A SQL subquery can’t be quickly encased in a set function.
• The BETWEEN operator can’t be used with a subquery. Nonetheless, the BETWEEN operator can be used inside the subquery.

Do you know about SQL Auto Increment

3. Types of Subqueries in SQL

Following are the various types of SQL Subquery, let’s discuss them one by one:

a. Subqueries with the SELECT Statement

The syntax of SQL Select Statement Subqueries –

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

SQL Subquery in Select Statements

Statement to check
Let’s revise SQL Injection

SQL> SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500) ;

This would deliver the accompanying outcome.

Subqueries with Select Statements

b. Subqueries with the INSERT Statement

Subqueries additionally can be used with INSERT command. The INSERT statement uses the information came back from the subquery to embed into another table. The chose information in the subquery can be altered with any of the character, date or number values.
The syntax of Insert Statements–

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

Example of SQL Subquery Insert Statements–
Consider a table CUSTOMERS_BKP with the comparative structure as CUSTOMERS table. Presently to duplicate the entire CUSTOMERS table into the CUSTOMERS_BKP table, you can use the accompanying linguistic structure.
Have a look at SQL Operators

SQL> INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS) ;

c. Subqueries with the UPDATE Statement

The subquery can be used in conjunction with the UPDATE command. Either single or various sections in a table can be refreshed when utilizing a subquery with the UPDATE clause.
The syntax of SQL subquery Update Statement–

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

Accepting, we have CUSTOMERS_BKP table accessible which is a reinforcement of CUSTOMERS table. The accompanying case refreshes SALARY by 0.25 times in the CUSTOMERS table for every one of the clients whose AGE is more prominent than or equivalent to 27.

SQL> UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );

This would affect two columns lastly CUSTOMERS table would have the accompanying records.

SQL Subquery with Update Statement

Let’s take a tour to SQL Primary and Foreign Key

d. Subqueries with the DELETE Statement

The syntax of SQL Subquery Delete Statement–

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

The example of SQL Subquery Delete Statement–
Expecting, we have a CUSTOMERS_BKP table accessible which is a reinforcement of the CUSTOMERS table. The accompanying case erases the records from the CUSTOMERS table for every one of the clients whose AGE is more noteworthy than or equivalent to 27.

SQL> DELETE FROM CUSTOMERS
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );

This would affect two lines lastly the CUSTOMERS table would have the accompanying records.

SQL Subquery in Delete Statement

So, this was all in SQL Subquery. Hope you like our explanation.

4. Conclusion – SQL Subquery

Hence, in this SQL Subquery tutorial, we learned about the subqueries in SQL. Moreover, we discussed types of Subqueries in SQL. Also, we looked at all these types of SQL Subquery with syntax and example. Still, if you are getting any confusion, ask in the comment tab.
See also – 
SQL Constraints
For reference