SQL Subqueries Example | Subqueries In SQL Tutorial
SQL subqueries are known as nested queries within another SQL query and is embedded within the where clause. It is usually added within the where clause of another SQL SELECT statement. It is used to return data, which will further be used in the main query as a condition to restrict the data also to be retrieved. It can be used with the SELECT, INSERT, UPDATE and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
- SQL Subqueries must be enclosed within parentheses.
- We can place the Subquery in the number of SQL clauses such as Where, Having, and From Clause.
- A subquery has only one column in the SELECT clause if and only if multiple columns are in the main query for a subquery to compare its selected columns.
- The ORDER BY command cannot be used with a subquery, and a GROUP BY command can perform the same operation that can be performed by the ORDER BY clause in the particular subquery.
- The List generated by a SELECT statement cannot include any references to values that evaluate to BLOB, ARRAY, CLOB, or NCLOB.
- They cannot be immediately enclosed in a set function.
- BETWEEN operator can be used within subquery but not with the subquery.
Let’s clear the above statements with proper queries and examples.
#SUBQUERY WITH SELECT STATEMENT:
Subqueries are most frequently used with Select statement.
SELECT columns FROM table where column OPERATOR (SELECT columns from table where);
Let’s consider a table CUSTOMERS:
Select * from Customers where ID IN (Select ID from Customers where salary >=600);
In the above query, the details of customers have been displayed whose salary was greater than 600 or equal to it. Here IN operator is used which checks whether there is any value returned by the inner query.
#Subquery with an Insert statement
INSERT statement is used to insert the values in a table that are returned by the subquery.
The selected data in the subquery can also be modified with any character, date, or number functions.
INSERT INTO new_table(columns) Select columns from old_table [where value operator]
INSERT INTO New_Customers SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS);
So, here in the above query New_Customers table will be created which will be having the same details of the customer as that of Customers table.
#SUBQUERY WITH UPDATE STATEMENT:
We can also update subquery using an update statement as conjunction. We can either update single or multiple columns in a table.
UPDATE TABLE SET column_name = new_value [Where operator [VALUE] (SELECT COLUMN_NAME FROM TABLE_NAME [where]);
UPDATE CUSTOMERS SET SALARY = SALARY +500 WHERE AGE IN (SELECT AGE FROM New_Customers WHERE AGE>=23);
So, here in the above query salary of the customers was incremented by 500 whose age was checked as per the given condition and the details of those customers was fetched from New_customers were previously we entered values using the INSERT statement.
Here IN operator was used to check whether there were any values returned by the inner subquery.
#Subquery with Delete Statement
The subquery can be used as a conjunction with a DELETE statement to delete some of the values from the database table.
DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE] (SELECT COLUMN_NAME FROM TABLE_NAME [WHERE]);
DELETE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM New_Customers WHERE AGE>=23);
So, here in the above query, the details of the customers were deleted by the CUSTOMERS table whose age was checked as per the given condition. The details of those customers were fetched from the New_Customers table, which is a backup of the CUSTOMERS table. Here IN operator was used to check whether there were any values returned by the inner subquery.
Finally, SQL Subqueries Example | Subqueries In SQL Tutorial is over.