SQL Sub Query and Types of Sub Query.

as a database developer you must we written sum query that take inputs from other query. That type of query called subquery. this artile describe about that sub query.

Sub Query: Subquery is that query which is the part of query and return the result to the parent query. exa-
Use Northwind
SELECT *
FROM Employees
WHERE (Country NOT IN
(SELECT DISTINCT country
FROM Employees))
in the above example you can see there is a main query and a sub query. the sub query return the list of distinct country to the main query.

Type Of Sub Query: There are two type of sub query
i. Self containted sub query
ii. co related sub query

self contained sub query: this is the sub query which have either scalre value or multi row values and return that value to the parent query.

ex. the example shown above is self contained sub query.

co related sub query: co related sub query are those query which has joined with other table. co related sub query is a part of joined query.

Here the sample of correlated subquery :

Select s.shipperid, (select count(*) from dbo.orders as o where o.shipperid = s.shipperid) as numberOfOrders

From dbo.shippers as s;

Another operators that handle correlated subquery is EXISTS (NOT EXISTS) and IN (NOT IN).