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.

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry 🕒

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay