Sub Queries

We can simply say query within another query.

we can say that a Subquery is a query that is embedded in WHERE clause of another SQL query.

Important rules for Subqueries:

  • You can place the Subquery in a number of SQL clauses: WHERE clause, HAVING clause, FROM clause.
  • Subqueries can be used with SELECT, UPDATE, INSERT, DELETE statements along with expression operator. It could be equality operator or comparison operator such as =, >, =, <= and Like operator.
  • A subquery is a query within another query. The outer query is called as the main query and an inner query is called a subquery.
  • The subquery generally executes first, and its output is used to complete the query condition for the main or outer query.
  • A subquery must be enclosed in parentheses.
  • Subqueries are on the right side of the comparison operator.
  • ORDER BY command cannot be used in a Subquery. GROUPBY command can be used to perform the same function as ORDER BY command.
  • Use single-row operators with single-row Subqueries. Use multiple-row operators with multiple-row Subqueries.

Syntax:

There is not any general syntax for Subqueries. However, Subqueries are seen to be used most frequently with SELECT statement as shown below:

SELECT column_name
FROM table_name
WHERE column_name expression operator 
    ( SELECT COLUMN_NAME  from TABLE_NAME   WHERE ... );

MASTER :

NAMEROLL_NODEPARTMENTPHONE_NUMBER
Mr. Bosco1Computer Science98######87
Robita2EC83######23
Mogita3Information and Technology99######47
Tonny4Mechenical91######67
Jecky shroff5Acting79######09

STUDENT :

NAMEROLL_NOSECTION
Tonny4A
Mr. Bosco1B
Jecky shroff5A

Sample Queries

To display NAME, DEPARTMENT, PHONE_NUMBER of the students from MASTER table whose section is A

Select NAME, LOCATION, PHONE_NUMBER from DATABASE 
WHERE ROLL_NO IN
(SELECT ROLL_NO from STUDENT where SECTION=’A’); 

Output:

NAMEROLL_NODEPARTMENTPHONE_NUMBER
Tonny4Mechenical91######67
Jecky shroff5Acting79######09

SUB-MASTER :

NAMEROLL_NODEPARTMENTPHONE_NUMBER
Julia30Information and Technology85######77
victory31Mechenical91######44
Aisa32Acting83######47
Lokesh33EC79######77
Potersen34Computer Science72######08

Insert Query Example:

INSERT INTO Master  SELECT * FROM Sub-Master;

Delete Query Example:

DELETE FROM Master 
WHERE ROLL_NO IN ( SELECT ROLL_NO 
                   FROM Sub-Master 
                   WHERE DEPARTMENT = ’Information And Technology’);


Delete Query Example:

UPDATE Master 
SET NAME=’geeks’ 
WHERE DEPARTMENT IN ( SELECT DEPARTMENT 
                    FROM Sub-MASTER 
                    WHERE NAME IN (‘Mr. Bosco’,’Tonny’));


Leave a Comment

Your email address will not be published. Required fields are marked *