What is Subquery

A subquery is a query within another SQL query. It is used to fetch data from the database that will be used by the main query. A subquery allows you to perform operations in multiple stages, using the result of one stage as the input of the next.

A subquery can be used in various parts of a SQL statement, including the SELECT, FROM, WHERE, and HAVING clauses. The data returned by the subquery is then used by the outer query for further operations. Subqueries can return a single value (scalar), a single row, multiple rows, or a table, depending on how they are used.

Syntax and Usage of Subqueries

I will explain the syntax and various usages of subqueries in SQL. We will be using a simple database that includes two tables: Employees and Departments. The tables are defined as follows:

Employees:

Departments:

Basic Syntax of Subqueries

A subquery is written inside parentheses and can be placed in various parts of the main SQL query. Here's the basic syntax for a subquery in the WHERE clause:

SELECT column_name(s)
FROM table_name
WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);

For instance, if we wanted to find all employees who earn more than the average salary:

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

The result of this query would be:

Inline Views

An inline view is a subquery in the FROM clause. It can be used to simplify complex queries. Here's an example:

SELECT e.FirstName, e.LastName, e.Salary
FROM (SELECT * FROM Employees WHERE Salary > 60000) as e;

The result of this query would be: