DEV Community

Abhay Singh Kathayat
Abhay Singh Kathayat

Posted on

1

Understanding SQL Subqueries: A Complete Guide with Examples

What is a Subquery in SQL?

A subquery, also known as an inner query or nested query, is a query within another SQL query. It is enclosed within parentheses and is executed first to provide results that the outer query uses for further processing.


Types of Subqueries

  1. Single-row Subquery
    • Returns one row as a result.
    • Commonly used with comparison operators like =, <, >.

Example:

Find the name of the employee with the highest salary:

   SELECT Name
   FROM Employees
   WHERE Salary = (SELECT MAX(Salary) FROM Employees);
Enter fullscreen mode Exit fullscreen mode
  1. Multi-row Subquery
    • Returns multiple rows as a result.
    • Used with operators like IN, ANY, ALL.

Example:

Find employees who work in departments with more than 5 members:

   SELECT Name
   FROM Employees
   WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Members > 5);
Enter fullscreen mode Exit fullscreen mode
  1. Correlated Subquery
    • References columns from the outer query.
    • Executed repeatedly for each row of the outer query.

Example:

Find employees earning more than the average salary of their department:

   SELECT Name, Salary
   FROM Employees E
   WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = E.DepartmentID);
Enter fullscreen mode Exit fullscreen mode
  1. Nested Subquery
    • Contains another subquery within it.

Example:

Find departments where the average employee salary exceeds $50,000:

   SELECT DepartmentName
   FROM Departments
   WHERE DepartmentID IN (
       SELECT DepartmentID
       FROM Employees
       WHERE Salary > (SELECT AVG(Salary) FROM Employees)
   );
Enter fullscreen mode Exit fullscreen mode

Use Cases of Subqueries

  1. Filtering Data: Use subqueries to filter rows based on specific criteria.
   SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);
Enter fullscreen mode Exit fullscreen mode
  1. Data Comparison: Compare data between tables using subqueries.
   SELECT Name FROM Employees WHERE DepartmentID = (SELECT ID FROM Departments WHERE Name = 'HR');
Enter fullscreen mode Exit fullscreen mode
  1. Data Aggregation: Perform advanced aggregations by nesting queries.
   SELECT DepartmentID, (SELECT COUNT(*) FROM Employees WHERE DepartmentID = D.ID) AS EmployeeCount
   FROM Departments D;
Enter fullscreen mode Exit fullscreen mode
  1. Dynamic Updates: Update or delete rows dynamically based on a subquery.
   UPDATE Employees
   SET Bonus = 500
   WHERE Salary > (SELECT AVG(Salary) FROM Employees);
Enter fullscreen mode Exit fullscreen mode

Advantages of Subqueries

  1. Modularity: Break complex queries into smaller, manageable parts.
  2. Readability: Makes the query logic clearer in many cases.
  3. Flexibility: Can be used in SELECT, WHERE, FROM, and HAVING clauses.

Limitations

  1. Performance: Subqueries, especially correlated ones, can be slower as they execute multiple times.
  2. Complexity: Deeply nested subqueries can become hard to read and debug.
  3. Optimization Challenges: Sometimes, the same results can be achieved more efficiently with joins.

Conclusion

Subqueries are a powerful tool in SQL for breaking down complex logic into manageable parts, enabling dynamic data analysis, and facilitating data manipulation. However, understanding when to use subqueries versus alternatives like joins is critical for optimal query performance.

Billboard image

Synthetic monitoring. Built for developers.

Join Vercel, Render, and thousands of other teams that trust Checkly to streamline monitor creation and configuration with Monitoring as Code.

Start Monitoring

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay