SQL Server subquery

A subquery is a SELECT statement embedded in other SQL statements. It is also called a nested query.

Each subquery statement is a SELECT statement, so it has a return value.

The returned value can be in the following three cases:

① Single value

② A Field

③ A table

The purpose of using a subquery is to use its return value.

Figure 1 employee table

In the following example, an instance is described based on the three return values:

Example 1: show all information about the employees with the highest salaries in the employee table. (Return value is a single value)

Select * from employee where salary = (select max (salary) from employee)

Running result:

Figure 2

Example 2: In the employee table, the salary is from high to low, and the "name" and "salary" of employees ranked between 3rd and 6th are displayed ". (Return value is a field)

Select Top 4 name, salary from employee where employee ID not in (select Top 2 employee ID from employee order by salary DESC) order by salary DESC

Note: Here is a Syntax: Top N indicates taking the first N records.

Running result:

Figure 3

Example 3: The employee name, salary, Department number, and average salary of the employee whose "salary" is higher than the average salary of the Department are displayed in the employee table. (Return value is a table)

Select name, salary, employee. department No., Department average salary from employee, (select AVG (salary) 'department average sale', Department No. From employee group by Department No.) tempwhere employee. department No. = temp. department No. and salary> temp. average Department salary

Note: Here, a temporary table with the returned value is named as "Temp ".

Running result:

Figure 4