What is subquery?
Subqueries in SQL are queries inside queries. Users can specify a Query in either the FROM clause or WHERE clause. The internal query filters rows and makes it easier for the external query to operate. You can bypass the use of subquery but it will make the query long with less readability.
Syntax of Subqueries:
SELECT * FROM demo_tab WHERE ( SELECT * FROM demo_tab CLAUSE ) ;
Here in the above syntax used in an demo example
SELECT col_1, col_2, col_3 FROM demo_table WHERE ( get some value using SELECT Statement );
here is our demo table named MARKS_DATA–
Example 1 : Using Subquery to get value of average marks
SELECT * FROM MARKS_DATA WHERE MARKS > (SELECT AVG(MARKS) as AVG FROM MARKS_DATA )
result of the inside query is
SELECT AVG(MARKS) as AVG FROM MARKS_DATA ;
As you can see here ,all the students have scored more than the average(i.e. 84) of marks
Thanks For Reading !
SOURCE: Microsoft T-SQL Documentation For SubQueries