SQL Subquery and Nested Query Usage

SQL subquery and nested query usage make it possible to write powerful and flexible queries in database applications. Especially for deriving complex data relationships with simple expressions, subqueries and nested queries are frequently preferred. In this article, fundamental questions such as what SQL subquery and nested query usage are, how they are applied, and in which situations they should be preferred will be answered.

What is a SQL Subquery?

Subquery means running another query within a SQL query. It can generally be used in SELECT, INSERT, UPDATE, or DELETE statements. A subquery is located in parts such as WHERE, HAVING, or FROM of the main query and passes its result to the main query.

Basic SQL Subquery Usage

Below is an example SELECT query using a subquery to return rows that meet a certain criterion from a table:

SELECT isim, maas
FROM calisanlar
WHERE maas > (SELECT AVG(maas) FROM calisanlar);

In this example, the SELECT AVG(maas) FROM calisanlar subquery returns the average salary of all employees. The main query lists the employees with salaries above the average. Since the subquery is used in the WHERE clause of the main query, the process is quite flexible.

Using Nested Query (Sub-Query Inside Another)

Nested query sometimes refers to queries that contain more than one layer of subquery. That is, another subquery can be used inside a subquery. This method is extremely useful for multi-layered data analysis.

Multi-Layered Queries with Nested Query

For example, a nested query can be used to find out in which department the employee with the lowest salary works:

SELECT departman
FROM calisanlar
WHERE maas = (
  SELECT MIN(maas)
  FROM calisanlar
  WHERE departman IN (
    SELECT departman FROM departmanlar WHERE aktif = 1
  )
);

Here, the SELECT departman FROM departmanlar WHERE aktif = 1 query first retrieves active departments. Then, the lowest salary in these departments is found, and the department of the lowest paid employee is queried. Thanks to the use of nested queries, step-by-step analysis and information gathering becomes easier.

Conclusion: The Importance of SQL Subquery and Nested Query

The use of SQL subquery and nested query increases operational ease in large and relational databases, improves code readability, and enables reusability. Subqueries provide direct access to data, while nested queries save time and resources in multiple returns. Developers who want to write effective and high-performance SQL should have a good knowledge of subquery and nested query structures.