SQL Subqueries: Understanding Single, Multiple, and Multiple Column Subqueries, Lecture notes of Database Management Systems (DBMS)

Download SQL Subqueries: Understanding Single, Multiple, and Multiple Column Subqueries and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!

SQL Subquery

By raveena thakur

What is SQL Sub query? A sub query is a SQL query nested inside a larger query.

SUBQUERY SYNTAX

• The subquery (inner query) executes before the main query(outer query).
• The result of the subquery is used by the main query.

TYPE OF 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.

• Suppose you want to find out the ename, job,sal of the employees whose salaries are less than that of an employee whose empno= 7876 from EMP table. Now you need to perform two queries in order to get the desired result.

1. We will find out the salary of the employee whose empno=7876. the query is as under:
2. Now in second query we will apply the condition from which we will find the ename, job and sal. We will use the query as under:

• The above two queries can be used as single query by using the concept of subquery. It will combine the result into a single query as under:

IN Operator

• The IN operator retirns true if the comparison value is contained in the list.
• The following statement finds the employee whose salary is the same as the minimum salary of the employees in the department.

ANY Operator

• The ANY operator return true if the comparison value matches any of the values in the list.
• Display the employees whose salary is more than the minimum salary of the employees in any department.

MULTIPLE COLUMN SUBQUERY

• A subquery that compares more than one column between the parent query and subquery is called the multiple column subqueries.
• List the employees that makes the same salary as other employee with empno=7521 with the same job also.

Deleting Tables

• One can delete from a table by using DELETE FROM statement. The DELETE statement removes rows from a table, but it does not release storage space.
• SYNTAX: DELETE FROM table_name;
• Example: To remove all rows in the student table, you just execute the following query: DELETE FROM student;