Use of sub query

Sub query

• Sub query is also known as Inner Query, Nested Query or Query with in Query.
• Result of sub query is assign to main query as where clause condition..
• Sub query is use with any DML and DQL Sql commands.
• Sub query is enclosed with in parentheses .
• In use of sub query select statement only one column selection is allow, multiple column selection are allow in main query.
• Between operator can not use with sub query but it can use with in sub query.

Example

SELECT * FROM dbo.Orders 

WHERE CustomerId IN (

SELECT CustomerId FROM Customers WHERE Age >= 18

)

Disadvantage 

• As part of query optimization we should use JOIN instead of sub query
• As in use of sub query there are multiple select statement so it will affect performance.