Sub query with example in sql server

Sub Query provides the combine data from two tables in to a single result.  Sub query are sometimes called nested queries.  A sub query is also called an inner query or inner select, while the statement containing a sub query is also called an outer query or outer select.

As we can see sub query contain one or more queries, one inside the other.

Some properties of sub query

·       A sub query should not have order by clause

·       A sub query should be placed in the right hand side of the comparison operator of the main query

·       A sub query should be enclosed in parenthesis because it needs to be executed first before the main query

·       More than one sub query can be included

Syntax :-

select select_list from table_name where expression_operator (select select_list from table)

Red line is a sub query (inner query)

Black line is main query (outer query)

Some important points about sub query:-

·       A sub query is just a SELECT statement inside of another.

·       Sub queries are always enclosed in parenthesis ().

·       A sub query that returns a single value can be used anywhere you would use an expression, such as in a column list or filter expression.

·       A sub query that returns more than one value is typically used where a list of values, such as those used in and IN operator.