SQL Subquery

SQL Subquery is a query within a query. It is also called a nested or query. It is written in DELETE, SELECT, INSERT, and UPDATE statements. Mostly it is used in the where clause. SQL subquery is always written within parentheses ().

SQL Subquery

 

  • Subquery has only one column in the select statement.
  • ORDER BY clause can’t be used within a subquery.
  • The In operator is used to compare multiple values of the subquery result.
  • we cannot use subquery with set operators.
  • We can use it between operator within a subquery but we cannot use it along with a subquery.

SQL Subquery Types

  • Subquery With the IN or NOT IN
  • Subquery With the ANY or ALL
  • Subquery With the EXISTS or NOT EXISTS
  • Subquery With the UPDATE, DELETE or INSERT statement
  • Subquery In the FROM clause
  • Subquery In the select list of select statement
Subquery With the IN or NOT IN

SQL Subquery which is used with IN operator returns values or no values. Once the subquery returns the output based on that output outer query provides results to the end user.

IN

syntax and example

syntax

select columnA,columnB
from TableQ
where columnA in(select columnC from TableR)

example

select studentname,studentid
from student
where deptid in(select deptid from department where deptid='Arts')

In the above example, we can see that we are getting data from only those students who belong to the Arts department.

NOT IN

syntax and example

syntax

select columnA,columnB
from TableQ
where columnA not in(select columnC from TableR)

example

select studentname,studentid
from student
where deptid not in(select deptid from department where deptid='Arts')

In the above example, we can see that we are getting data from only those students who do not belong to the Arts department.

Subquery With the ANY or ALL

ANY

The any operator returns true if at least one of the value of the subquery satisfy the specified condition. the conditions are defined using a comparison operator that is =,>,<, etc.

syntax and example

syntax

select columnA,columnB
from TableQ
where columnA >=ANY (select columnC from TableR)

example
select studentname,studid
from students
where marks>=ANY ( select avg(marks) as avgmarks from student)

In the above example, we retrieved those students data who got average or more than average marks.

ALL

The ALL operator returns true if all values of the sql subquery satisfy the specified condition. the conditions are defined using a comparison operator that is =,>,<, etc.

syntax and example

syntax

select columnA,columnB
from TableQ
where columnA >ALL (select columnC from TableR)

example
select studentname,studid
from students
where marks>ALL ( select avg(marks) as avgmarks from student)

In the above example, we retrieved those students’ data who got more than average marks.

Subquery With the EXISTS or NOT EXISTS

Exists operator returns true if the subquery returns any value else it returns false
Not Exists operator returns results excluding output of subquery.

EXISTS

syntax and example

syntax

select columnA,columnB
from TableQ
where columnA exits (select columnC from TableR where Year='2017')

example
select studentname,studid
from students
where studid exists ( select studid  from student where admissiondate='2017')

Exists operator returns true if the subquery returns any value else it returns false Not Exists operator returns results excluding output of  sql subquery.

NOT EXISTS

syntax and example

syntax

select columnA,columnB
from TableQ
where columnA not exits (select columnC from TableR where Year='2017')

example
select studentname,studid
from students
where studid not exists( select studid  from student where admissiondate='2017')

In the above example exists operator will return those students who have taken admission in 2017 and not exits will return all student data excluding students who have admission in 2017.

Subquery With the UPDATE, DELETE or INSERT statement

 

INSERT
We can perform data insertion using SQL subquery.

syntax and example

syntax
 
insert into TableM (columnQ,columnW)
select columnA,columnB
from TableQ
where subquery condition

example
insert into students_BKP (studentname,studid)
select studentname,studid
from students
where admissiondate=(select admissiondate from students where admissiondate='2017')

In the above example, we are inserting 2017 year students data in student backup table.

UPDATE

We can update table data using subquery.

syntax
UPDATE table
SET columnAD = (select columnDF from Table_BHR where condition)

example
update students
set studentname=(select studentname from students where studid=25)

In the above example, we are updating student name of a student whose studentID is 25.

DELETE

The  Delete data can be performed using subquery

syntax and example

 

syntax
 
delete from  TableM 
where subquery condition

example
delete from  students_BKP 
where admissiondate=(select admissiondate from students where admissiondate='2017')

In the above example, we are deleting data from the student backup table.

Subquery In the FROM clause

The sql subquery can be used to in the from clause while working with complex queries and joins.

syntax and example

syntax

select * from (select * from Table_MQ where condition)

example
select * from (select * from students where department in('arts','science') )

In the above example, the sql subquery returns data from the arts and science department to from clause.

Subquery In the select list of select statement

 

The sql subquery can be used in the select list to get data such as cumulative, aggregate etc.

example

SELECT c.product_name, (SELECT AVG(salesnumber) FROM customer_product 
    WHERE deal_code = c.deal_code) AVG_SAL_PROD FROM customer_product