SQL Sub Query
SQL Sub Query
A
Subquery is a query within another SQL query and embedded within the WHERE
clause.
Important Rule:
- A
subquery can be placed in a number of SQL clauses like WHERE clause, FROM
clause, HAVING clause.
- You
can use Subquery with SELECT, UPDATE, INSERT, DELETE statements along with
the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
- A
subquery is a query within another query. The outer query is known as the
main query, and the inner query is known as a subquery.
- Subqueries
are on the right side of the comparison operator.
- A
subquery is enclosed in parentheses.
- In
the Subquery, ORDER BY command cannot be used. But GROUP BY command can be
used to perform the same function as ORDER BY command.
Subqueries with the Select
Statement
SQL subqueries are most
frequently used with the Select statement.
Syntax
SELECT column_name
FROM table_name
WHERE column_name expression operator
( SELECT column_name from table_name WHERE ... );
|
ID
|
NAME
|
AGE
|
ADDRESS
|
SALARY
|
|
|
1
|
Laxmi
|
20
|
US
|
2000.00
|
|
|
2
|
Sonali
|
26
|
Dubai
|
1500.00
|
|
|
3
|
Ashwini
|
27
|
Delhi
|
2000.00
|
|
|
4
|
Sanjay
|
29
|
HYD
|
6500.00
|
|
|
5
|
Abhi
|
34
|
Bangalore
|
8500.00
|
|
|
6
|
Sagar
|
42
|
China
|
4500.00
|
|
|
7
|
Pooja
|
25
|
chennai
|
10000.00
|
|
SELECT *
FROM EMPLOYEE
WHERE ID IN (SELECT ID
FROM EMPLOYEE
WHERE SALARY > 4500);
|
ID
|
NAME
|
AGE
|
ADDRESS
|
SALARY
|
|
4
|
Sanjay
|
29
|
Hyd
|
6500.00
|
|
5
|
Abhi
|
34
|
Bangalore
|
8500.00
|
|
7
|
Pooja
|
25
|
Chennai
|
10000.00
|
Subqueries
with the INSERT Statement
- SQL
subquery can also be used with the Insert statement. In the insert
statement, data returned from the subquery is used to insert into another
table.
- In
the subquery, the selected data can be modified with any of the character,
date functions.
INSERT INTO table_name (column1, column2, column3....)
SELECT *
FROM table_name
WHERE VALUE OPERATOR
Example
INSERT INTO EMPLOYEE_BKP
SELECT * FROM EMPLOYEE
WHERE ID IN (SELECT ID
FROM EMPLOYEE);
Subqueries
with the UPDATE Statement
The
subquery of SQL can be used in conjunction with the Update statement. When a subquery is used with the Update
statement, then either single or multiple columns in a table can be updated.
UPDATE table
SET column_name = new_value
WHERE VALUE OPERATOR
(SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE condition);
Example
UPDATE EMPLOYEE
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 29);
|
ID
|
NAME
|
AGE
|
ADDRESS
|
SALARY
|
|
1
|
Laxmi
|
20
|
US
|
2000.00
|
|
2
|
Sonali
|
26
|
Dubai
|
1500.00
|
|
3
|
Ashwini
|
27
|
Delhi
|
2000.00
|
|
4
|
Sanjay
|
29
|
HYD
|
1625.00
|
|
5
|
Abhi
|
34
|
Bangalore
|
2125.00
|
|
6
|
Sagar
|
42
|
China
|
1125.00
|
|
7
|
Pooja
|
25
|
chennai
|
10000.00
|
Subqueries
with the DELETE Statement
The subquery of SQL can
be used in conjunction with the Delete statement just like any other statements
mentioned above.
Syntax
DELETE FROM TABLE_NAME
WHERE VALUE OPERATOR
(SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE condition);
Example
Let's assume we have an
EMPLOYEE_BKP table available which is backup of EMPLOYEE table. The given
example deletes the records from the EMPLOYEE table for all EMPLOYEE whose AGE
is greater than or equal to 29.
DELETE FROM EMPLOYEE
WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP
WHERE AGE >= 29 );
|
ID
|
NAME
|
AGE
|
ADDRESS
|
SALARY
|
|
1
|
Laxmi
|
20
|
US
|
2000.00
|
|
2
|
Sonali
|
26
|
Dubai
|
1500.00
|
|
3
|
Ashwini
|
27
|
Delhi
|
2000.00
|
|
7
|
Pooja
|
25
|
chennai
|
10000.00
|
