Understanding Subquery and Common Table Expressions(CTEs)

What is a Subquery? A subquery is simply a select statement written inside another select statement. The inside query runs first and results is handed to outer query for use. Example: Explanation i)First it finds the Average Score ii)It finds all rows which are greater than the Average score. Types of Subqueries 1. Scalar Subquery It returns a single value It will output the single highest score recorded. 2. List Subquery It returns a column of values. e.g List of IDs. It is used with IN or NOT