DBMS SQL Sub Queries | PDF | Sql | Data Management
Scribd
Upload
198 views4 pages

DBMS SQL Sub Queries

A SQL subquery is a query embedded within another SQL query, most commonly used with SELECT, INSERT, UPDATE, and DELETE statements. Subqueries allow you to compare values from the inner query in the WHERE clause of the outer query. For example, a subquery can be used to select rows from a table where a column value matches a value returned from another SELECT statement. Subqueries return a single or multiple columns and rows that can then be used by the outer query.

Uploaded by

Swathi Tudicherla
Copyright
© © All Rights Reserved
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
Download as doc, pdf, or txt
198 views4 pages

DBMS SQL Sub Queries

A SQL subquery is a query embedded within another SQL query, most commonly used with SELECT, INSERT, UPDATE, and DELETE statements. Subqueries allow you to compare values from the inner query in the WHERE clause of the outer query. For example, a subquery can be used to select rows from a table where a column value matches a value returned from another SELECT statement. Subqueries return a single or multiple columns and rows that can then be used by the outer query.

Uploaded by

Swathi Tudicherla
Copyright
© © All Rights Reserved
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
Download as doc, pdf, or txt
Download as doc, pdf, or txt
You are on page 1/ 4

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.

1. 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 ... );

Example

Consider the EMPLOYEE table have the following records:

ID NAME AGE ADDRESS SALARY


1 John 20 US 2000.00

2 Stephan 26 Dubai 1500.00

3 David 27 Bangkok 2000.00

4 Alina 29 UK 6500.00

5 Kathrin 34 Bangalore 8500.00

6 Harry 42 China 4500.00

7 Jackson 25 Mizoram 10000.00

The subquery with a SELECT statement will be:

SELECT *
FROM EMPLOYEE

WHERE ID IN (SELECT
ID FROM EMPLOYEE
WHERE SALARY > 4500);

This would produce the following result:


ID NAME AGE ADDRESS SALARY

4 Alina 29 UK 6500.00

5 Kathrin 34 Bangalore 8500.00

7 Jackson 25 Mizoram 10000.00

2. 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.

Syntax:

INSERT INTO table_name (column1, column2, column3.... )


SELECT *

FROM table_name
WHERE VALUE
OPERATOR
Example

Consider a table EMPLOYEE_BKP with similar as EMPLOYEE.

Now use the following syntax to copy the complete EMPLOYEE table into
the EMPLOYEE_BKP table.

INSERT INTO EMPLOYEE_BKP


SELECT * FROM EMPLOYEE
WHERE ID IN (SELECT ID
FROM EMPLOYEE);
3. 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.

Syntax

UPDATE table

SET column_name = new_value


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 updates the SALARY by .25 times in the
EMPLOYEE table for all employee whose AGE is greater than or equal to 29.

UPDATE EMPLOYEE
SET SALARY = SALARY * 0.25

WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP


WHERE AGE >= 29);

This would impact three rows, and finally, the EMPLOYEE table would have the
following records.

ID NAME AGE ADDRESS SALARY

1 John 20 US 2000.00

2 Stephan 26 Dubai 1500.00

3 David 27 Bangkok 2000.00

4 Alina 29 UK 1625.00

5 Kathrin 34 Bangalore 2125.00

6 Harry 42 China 1125.00

7 Jackson 25 Mizoram 10000.00

4. 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);
⇧

https://www.javatpoint.com/dbms-sql-sub-queries 3/
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 );

This would impact three rows, and finally, the EMPLOYEE table would have the
following records.

ID NAME AGE ADDRESS SALARY

1 John 20 US 2000.00

2 Stephan 26 Dubai 1500.00

3 David 27 Bangkok 2000.00

7 Jackson 25 Mizoram 10000.00

https://www.javatpoint.com/dbms-sql-sub-queries 4/

You might also like