Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL: Use Subqueries for Complex Data Retrieval

- 05.07.26 - ErcanOPAK

📊 Subqueries = Nested Power

Single queries are limited. Subqueries nest queries inside queries. Complex conditions, derived data, advanced filtering.

📝 Subquery Examples

-- Users with orders over $100
SELECT name, email
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
    WHERE total > 100
);

-- Users with no orders
SELECT name, email
FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id
    FROM orders
);

-- Products with average order above 100
SELECT name
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

-- Customers with orders in last 30 days
SELECT name, email
FROM users
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE user_id = users.id
    AND order_date > CURRENT_DATE - INTERVAL 30 DAY
);

🎯 Advanced Subqueries

-- Subquery in SELECT (scalar)
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count,
    (SELECT COALESCE(SUM(total), 0) FROM orders WHERE user_id = users.id) as total_spent
FROM users;

-- Subquery with multiple columns
SELECT name, email
FROM users u
WHERE (SELECT COUNT(*) FROM orders WHERE user_id = u.id) > 5
AND (SELECT AVG(total) FROM orders WHERE user_id = u.id) > 100;

-- Correlated subquery
SELECT name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

-- Subquery in FROM (derived table)
SELECT 
    department_id,
    avg_salary
FROM (
    SELECT 
        department_id,
        AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_stats
WHERE avg_salary > 50000;

-- Subquery in HAVING
SELECT 
    department_id,
    COUNT(*) as employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > (
    SELECT AVG(employee_count)
    FROM (
        SELECT COUNT(*) as employee_count
        FROM employees
        GROUP BY department_id
    ) AS dept_counts
);

💡 Subquery Tips

  • Use EXISTS for existence checks
  • Use IN for list of values
  • Use scalar subqueries in SELECT
  • Correlated subqueries can be slow
  • Consider JOIN as alternative

“Subqueries add power to SQL. Complex conditions, derived data, advanced filtering. Essential for complex queries.”

— SQL Developer

Related posts:

SQL: Use COALESCE for First Non-Null Value

SQL: Use COALESCE for First Non-Null Value

SQL Server: Fix a Date Range Filter Silently Missing Rows Because BETWEEN Was Used Against a datetim...

SQL Server: Fix a Date Range Filter Silently Missing Rows Because BETWEEN Was Used Against a datetim...

SQL: Use PIVOT to Transform Rows into Columns

SQL: Use PIVOT to Transform Rows into Columns

Views: 17

Post navigation

.NET Core: Use Middleware to Handle HTTP Requests
C#: Use String Interpolation for Clean Formatting

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (661)
  • Add Constraint to SQL Table to ensure email contains @ (619)
  • Average of all values in a column that are not zero in SQL (577)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (553)
  • Find numbers with more than two decimal places in SQL (481)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store
  • WordPress: Fix a Navigation Block Losing Submenu Items After Sync
  • Visual Studio: Fix CodeLens Reference Counts Stuck at Stale Numbers
  • C#: Fix a UTF-8 String Literal Behaving Differently in a Switch
  • Photoshop: Fix a Liquify Mesh Snapping Back on a Smart Object
  • The AI Prompt That Turns a Crash Dump Into a Short Repro Script

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com