Subquery - How to write in SELECT statement

What is a Subquery

A SQL Subquery(also called an inner query or inner select) is a sql query that is nested inside a statement(SELECT, INSERT, UPDATE, or DELETE), or inside another subquery.

Subquery basics

The statements that include a SQL Subquery usually use logical operators(in, exists, all, any or some):

WHERE expression [NOT] IN (subquery)
WHERE expression comparison_operator [ANY | ALL | SOME] (subquery)
WHERE [NOT] EXISTS (subquery)




Subquery examples

IN

select * from EMPLOYEES E
where E.ID IN (select s.SalesPersonID from Sales s);

ANY

select * from Customers c
where c.ID =ANY (select s.CustomerID from Sales s);

SOME

select * from Students s
where s.id =SOME (select l.Student_id from Library l);

EXISTS

select * from Students s
where EXISTS (select * from Students_Math m where m.id=s.id);

NOT EXISTS

select * from Students s
where NOT EXISTS (select * from Students_Math m where m.id=s.id);