What is a Sub-Query? | SQL Server interview questions

A query nested inside a SELECT statement is known as a subquery and is an alternative to complex join statements. A subquery combines data from multiple tables and returns results that are inserted into the WHERE condition of the main query. A subquery is always enclosed within parentheses and returns a column. A subquery can also be referred to as an inner query and the main query as an outer query. JOIN gives better performance than a subquery when you have to check for the existence of records.
For example, to retrieve all EmployeelD and CustomerlD records from the ORDERS table that have the EmployeelD greater than the average of the EmployeelD field, you can create a nested query, as shown:
SELECT DISTINCT EmployeelD, CustomerlD FROM ORDERS
WHERE EmployeelD > (SELECT AVG (EmployeelD) FROM ORDERS)