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 CTEs to Write Readable Complex Queries

- 31.05.26 - ErcanOPAK

📝 Name Your Subqueries

Subqueries inside subqueries are unreadable. CTEs (WITH clause) give subqueries names. Your future self will thank you.

📝 Without CTE (Unreadable)

SELECT name, total_orders
FROM (
  SELECT u.name, COUNT(o.id) as total_orders
  FROM users u
  JOIN orders o ON u.id = o.user_id
  WHERE o.status = 'completed'
  GROUP BY u.id, u.name
) order_stats
WHERE total_orders > 5
ORDER BY total_orders DESC;

✅ With CTE (Readable)

WITH order_stats AS (
  SELECT u.name, COUNT(o.id) as total_orders
  FROM users u
  JOIN orders o ON u.id = o.user_id
  WHERE o.status = 'completed'
  GROUP BY u.id, u.name
)
SELECT name, total_orders
FROM order_stats
WHERE total_orders > 5
ORDER BY total_orders DESC;

✅ Multiple CTEs

WITH 
recent_users AS (
  SELECT * FROM users WHERE created_at > NOW() - INTERVAL '30 days'
),
active_orders AS (
  SELECT * FROM orders WHERE status = 'completed'
),
user_totals AS (
  SELECT u.id, u.name, SUM(o.total) as revenue
  FROM recent_users u
  JOIN active_orders o ON u.id = o.user_id
  GROUP BY u.id, u.name
)
SELECT name, revenue
FROM user_totals
WHERE revenue > 1000
ORDER BY revenue DESC;

“Inherited 200-line query with 5 subquery levels. Rewrote with CTEs. Now 30 lines, each step named. Junior dev understood it in 5 minutes.”

— Database Developer

Related posts:

Why EXISTS Beats COUNT(*) in Conditional Queries

Why EXISTS Beats COUNT(*) in Conditional Queries

SQL: Use ORDER BY to Sort Query Results

SQL: Use ORDER BY to Sort Query Results

SQL: Use CTEs (WITH Clause) for Readable Complex Queries

SQL: Use CTEs (WITH Clause) for Readable Complex Queries

Views: 12

Post navigation

.NET Core: Add Health Checks to Monitor Your App’s Status
C#: Advanced String Interpolation Tricks You Didn’t Know

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,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

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

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

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