SQL Subquery: A Definitive Guide With Practical Examples (2023)

Summary: In this tutorial, you will learn about SQL subquery and how to use subqueries to form flexible SQL statements.

Basic SQL Subquery

Consider the followingEmployeesYdepartmentstables ofdefault database:

Suppose you need to find all employees in location ID 1700. The following solution might come to your mind.

First find all apartments on site with ID 1700:

TO CHOOSE*OUTSIDE OFdepartmentsOSlocation_id =1700;
Code message: SQL (Structured Query Language) (sql)

Second, find all employees belonging to location 1700 using the list of department IDs from the query above:

TO CHOOSEEmployee_ID, First Name, Last NameOUTSIDE OFEmployeesOSdepartment idEM(1,3,8,10,11)DOMAIN VONName surname;
Code message: SQL (Structured Query Language) (sql)

This solution has two problems. first you looked at themdepartmentsTable to check which department belongs to site 1700. However, the original question did not refer to specific departments; he was referring to the 1700 location.

Due to the small volume of data, you can easily get a list of departments. However, in real system with large amounts of data, this can be problematic.

Another problem was that every time you want to find employees who are in a different location, you have to go through queries.

A much better solution to this problem is to use a subquery. By definition, a subquery is a query nested within another query, for exampleTO CHOOSE,INSERTION,TO UPDATE, oEXTINGUISHOpinion. In this tutorial, we will focus on the subquery used with theTO CHOOSEOpinion.

In this example, you can rewrite the above two queries as follows:

TO CHOOSEEmployee_ID, First Name, Last NameOUTSIDE OFEmployeesOSdepartment idEM(TO CHOOSEdepartment idOUTSIDE OFdepartmentsOSlocation_id =1700)DOMAIN VONName surname;
Code message: SQL (Structured Query Language) (sql)

The query enclosed in parentheses is called a subquery. It is also known as inner query or inner select. The query that contains the subquery is called an outer query or outer select.

To run the query, the database system must first run the subquery, replacing the subquery in parentheses with its result, a department ID number, which is at position 1700, and then run the outer query.

You can use a subquery in many places, for example for example.:

• With himEMoNOT INOperator
• Swindlercomparison operators
• With himAVAILABLEoDOES NOT EXISTOperator
• With himANYoEMOperator
• MilitaryOUTSIDE OFclause
• MilitaryTO CHOOSEclause

Examples of SQL subqueries

Let's give some examples of using subqueries to understand how they work.

Subconsulta SQL con operador IN o NOT IN

In the example above, you saw the subquery used withEMOperator. The following example uses a subquery with theNOT INOperator to find all employees not in location 1700:

TO CHOOSEEmployee_ID, First Name, Last NameOUTSIDE OFEmployeesOSdepartment idNO EM(TO CHOOSEdepartment idOUTSIDE OFdepartmentsOSlocation_id =1700)DOMAIN VONName surname;
Code message: SQL (Structured Query Language) (sql)

SQL subquery with comparison operator

The following syntax illustrates how to use a subquery with a comparison operator:

Comparison operator (subquery)
Code message: SQL (Structured Query Language) (sql)

whereincomparison operatoris one of these operators:

• equal (=)
• greater than (>)
• less than (<)
• Greater than or equal to (>=)
• Less than equals (<=)
• Not equal to (!=) or (<>)

The following example finds the employees with the highest salary:

TO CHOOSEEmployee_ID, First Name, Last Name, SalaryOUTSIDE OFEmployeesOSsalary = (TO CHOOSE maximum(Wage)OUTSIDE OFEmployees)DOMAIN VONName surname;
Code message: SQL (Structured Query Language) (sql)

In this example, the subquery returns the highest salary of all employees, and the outer query finds employees whose salary equals the highest.

The following statement finds all employees whose salaries are greater than the average salary of all employees:

TO CHOOSEEmployee_ID, First Name, Last Name, SalaryOUTSIDE OFEmployeesOSSalary > (TO CHOOSE average(Wage)OUTSIDE OFEmployees);
Code message: SQL (Structured Query Language) (sql)

In this example, the subquery first returns the average salary of all employees. The outer query uses the greater than operator to find all employees whose salaries are above average.

SQL-Unterabfrage sends either EXIST or NOT EXIST operator

HeAVAILABLEThe operator checks if there are any rows returned by the subquery. Returns true if the subquery contains rows. Otherwise, false is returned.

The syntax ofAVAILABLEThe operator is as follows:

EXISTS (subquery)
Code message: SQL (Structured Query Language) (sql)

HeDOES NOT EXISTThe operator is the opposite ofAVAILABLEOperator.

DOES NOT EXIST (subquery)
Code message: SQL (Structured Query Language) (sql)

The following example finds all departments that have at least one employee with a salary greater than 10,000:

TO CHOOSEdepartment nameOUTSIDE OFdepartments ofOS AVAILABLE(TO CHOOSE 1 OUTSIDE OFemployees, for exampleOSsalary >10000 Ye.department_id = d.department_id)DOMAIN VONDepartment name;
Code message: SQL (Structured Query Language) (sql)

Likewise, the following statement finds all departments that do not have an employee with a salary greater than 10,000:

TO CHOOSEdepartment nameOUTSIDE OFdepartments ofOS NO AVAILABLE(TO CHOOSE 1 OUTSIDE OFemployees, for exampleOSsalary >10000 Ye.department_id = d.department_id)DOMAIN VONDepartment name;
Code message: SQL (Structured Query Language) (sql)

SQL subquery with the ALL operator

The subquery syntax when used with theEMThe operator is as follows:

ALL comparison operator (subquery)
Code message: SQL (Structured Query Language) (sql)

The following condition evaluates to true ifxis greater than any value returned by the subquery.

x > ALL (Unterabfragge)
Code message: SQL (Structured Query Language) (sql)

Suppose the subquery returns three values, one, two, and three. The following condition evaluates to true ifxis greater than 3.

x > ON (1,2,3)
Code message: SQL (Structured Query Language) (sql)

The following query uses theGROUP BYclause andMINIMUM()Function to find the lowest salary by department:

TO CHOOSE MINIMUM(Wage)OUTSIDE OFEmployeesGROUP VONdepartment idDOMAIN VON MINIMUM(Wage)DESCRIPTION;
Code message: SQL (Structured Query Language) (sql)

The following example finds all employees whose salaries are greater than the lowest salary in each department:

TO CHOOSEEmployee_ID, First Name, Last Name, SalaryOUTSIDE OFEmployeesOSSalary >=EM(TO CHOOSE MINIMUM(Wage)OUTSIDE OFEmployeesGROUP VONdepartment ID)DOMAIN VONName surname;
Code message: SQL (Structured Query Language) (sql)

SQL-Unterabfrage with the ANY operator

Below is the syntax for a subquery using theANYOperator:

ANY comparison operator (subquery)
Code message: SQL (Structured Query Language) (sql)

For example, the following condition evaluates to true if x is greater than any value returned by the subquery. So the conditionx > SOME (1,2,3)evaluates to true if x is greater than 1.

x > QUALQUER (subquery)
Code message: SQL (Structured Query Language) (sql)

Note that theSOMEoperator representsANYoperator so you can use them interchangeably.

The following query finds all employees whose salaries are greater than or equal to the highest salary in each department.

TO CHOOSEEmployee_ID, First Name, Last Name, SalaryOUTSIDE OFEmployeesOSSalary >=SOME(TO CHOOSE maximum(Wage)OUTSIDE OFEmployeesGROUP VONdepartment identification);
Code message: SQL (Structured Query Language) (sql)

In this example, the subquery finds the highest salary for employees in each department. The external query looks at these values ​​and determines which employee salaries are greater than or equal to the highest salary per department.

SQL subquery in FROM clause

You can use a subquery inOUTSIDE OFclause ofTO CHOOSEdeclaration as follows:

TO CHOOSE*OUTSIDE OF(subquery)Etable name
Code message: SQL (Structured Query Language) (sql)

In this syntax, thetable nicknamesis necessary because all the tables in theOUTSIDE OFThe clause must have a name.

Note that thesubquerydeclared notOUTSIDE OFClause means aderived table in mysqloOnline Preview in Oracle.

The following statement returns the average salary for each department:

TO CHOOSE average(salary) average_salaryOUTSIDE OFEmployeesGROUP VONdepartment identification;
Code message: SQL (Structured Query Language) (sql)

You can use this query as a subquery inOUTSIDE OFClause for calculating the average salary of the departments as follows:

TO CHOOSE REDONDO(average(average salary)0)OUTSIDE OF(TO CHOOSE average(salary) average_salaryOUTSIDE OFEmployeesGROUP VONdepartment ID) department salary;
Code message: SQL (Structured Query Language) (sql)

SQL subquery in SELECT clause

A subquery can be used anywhere an expression can be used in theTO CHOOSE-Clause. The following example finds the salaries of all employees, their average salary, and the difference between each employee's salary and the average salary.

TO CHOOSEEmployees_id, first, last, salary, (TO CHOOSE REDONDO(average(Wage),0)OUTSIDE OFemployees) average_salary, salary - (TO CHOOSE REDONDO(average(Wage),0)OUTSIDE OFEmployees)DifferenceOUTSIDE OFEmployeesDOMAIN VONName surname;
Code message: SQL (Structured Query Language) (sql)

Now you should understand what an SQL subquery is and how to use subqueries to create flexible SQL statements.