Mastering SQL Subqueries: Types & Practical Clarity

🚀 SQL Journey – Day 31: Introduction to CTE (Common Table Expression) Today’s focus was on CTEs (Common Table Expressions) — one of the most powerful concepts for writing clean and structured SQL queries. 🔹 What is a CTE? A CTE is a temporary result set defined using the "WITH" clause, which can be referenced within a SELECT, INSERT, UPDATE, or DELETE query. 👉 It helps break complex queries into smaller, readable parts. 🔹 Basic Syntax WITH cte_name AS (  SELECT column_name  FROM table_name  WHERE condition ) SELECT * FROM cte_name; 🔹 Why CTE is Useful? • Improves query readability • Avoids writing subqueries multiple times • Helps structure complex logic step-by-step • Makes debugging easier 🔹 Concept Understanding (From Today’s Notes) Instead of writing nested subqueries like: SELECT customer, SUM(amount) FROM sales GROUP BY customer HAVING SUM(amount) > 8000; 👉 We can first create a temporary result: WITH total_sales AS (  SELECT customer, SUM(amount) AS total  FROM sales  GROUP BY customer ) SELECT * FROM total_sales WHERE total > 8000; ✔ Clean ✔ Readable ✔ Easy to maintain 🔹 Key Insight CTE acts like a temporary table inside a query that can be reused multiple times within the same query. 💡 Day 31 Realization CTEs are not just about syntax — they change the way you think about writing queries. Instead of solving everything at once, you break problems into logical steps. That’s how real-world SQL is written. From messy queries → to structured thinking. HAPPY LEARNING!✨ #SQL #CTE #DataAnalytics #LearningJourney #SQLPractice #RDBMS #TechJourney #CSE

SQL Just Got Smarter. Meet the WITH Clause. How one keyword made complex queries feel surprisingly human. Just when I thought SQL was beginning to feel familiar, my tutor introduced something that changed how I see query writing entirely. Common Table Expressions. CTEs. And they are implemented with just one word: WITH. The idea is beautifully simple: instead of cramming all your logic into one long, tangled query, you give each step its own name and build on it. Like writing instructions a human can actually follow. -- Instead of one overwhelming query, you build in steps WITH high_earners AS (   SELECT name, salary   FROM employees   WHERE salary > 100000 ) SELECT * FROM high_earners; The Pros Easy to read. Complex logic broken into clear, named steps. Reusable. Reference the same result multiple times without rewriting. Cleaner thinking. Forces you to structure logic before you query. The Trade-off Memory cost. SQL saves the CTE as a temporary table in memory. Slightly slower. That memory usage means execution takes longer than a direct query. What This Really Means A CTE is a trade: you exchange a little speed for a lot of clarity. On small to mid-sized datasets, that trade is almost always worth it. On massive databases where every millisecond counts, a direct query may serve you better. Know your data. Choose accordingly. As someone still building experience, I will take readable over clever every time. SELECT. WHERE. JOIN. Subqueries. Now CTEs. The SQL toolkit is growing and so is the thinking behind it. Still in it. Follow the journey. Do you prefer CTEs or subqueries? Guided by Obumneme Udeinya ©️ Intrigued by Data #CTEs #DataAnalysis #LearningInPublic #DataAnalyst #SQLTips #BeginnersJourney #Cohort6

SQL Journey – Day 27: Subqueries Deep Dive (Advanced Practice) Today’s focus: Understanding how subqueries work internally and how to use them effectively for real-world problem solving. This was not just theory — practiced multiple scenarios to understand execution flow and logic building. ⸻ 🔹 What I Explored Subqueries inside SELECT, WHERE: • Using subqueries to fetch intermediate results • Comparing values using nested queries • Writing conditions based on dynamic results ⸻ 🔹 Types of Subqueries Practiced ✅ Single Row Subquery • Returns one value • Used with operators (=, >, <, etc.) ✅ Multi Row Subquery • Returns multiple values • Used with IN, ANY, ALL • Executes row by row ✅ Correlated Subquery • Depends on outer query • Executes row by row ⸻ 🔹 Key Concepts Understood • Subqueries execute inside → outside • Outer query depends on inner query results • Must maintain data type compatibility • Can be nested multiple levels ⸻ 🔹 Real Practice Scenarios • Example: Finding average value using subquery • Correlated subqueries are powerful but expensive • Poor usage can impact performance • Sometimes JOINs are a better alternative ⸻ 💡 Day 27 Realization • Subqueries are not just a concept — they are a thinking pattern • They help break complex problems into smaller logical steps • Mastering them = writing smarter SQL, not longer SQL ⸻ 🔖 Hashtags #SQL #Subqueries #AdvancedSQL #DataAnalytics #LearningJourney #RDBMS #TechCSE

𝗚𝗼𝗼𝗱 𝗦𝗤𝗟 𝘄𝗼𝗿𝗸𝘀. 𝗖𝗹𝗲𝗮𝗻 𝗦𝗤𝗟 𝗶𝘀 𝗲𝗮𝘀𝘆 𝘁𝗼 𝗿𝗲𝗮𝗱, 𝗱𝗲𝗯𝘂𝗴, 𝗮𝗻𝗱 𝘀𝗰𝗮𝗹𝗲. When you start learning SQL, the main focus is usually getting the correct result. But in real-world projects, writing clean and readable SQL is just as important. Because your queries will be read by: • teammates • analysts • engineers • your future self Here are 4 simple practices that instantly improve your SQL quality 👇 1️⃣ Use aliases for readability Aliases make queries shorter and easier to understand. Instead of repeating long table names, use meaningful aliases. Example: SELECT u.id, u.name, SUM(o.amount) AS total_spent FROM users AS u JOIN orders AS o ON u.id = o.user_id GROUP BY u.id, u.name; 2️⃣ Format queries properly Well-formatted SQL is much easier to debug and maintain. Best practices: • Use uppercase for SQL keywords • Place each clause on a new line • Align JOIN conditions 3️⃣ Follow naming conventions Consistent naming makes databases easier to navigate. Common convention: • snake_case for tables and columns • descriptive column names Example: customer_id order_date total_amount 4️⃣ Avoid SELECT * It might feel convenient, but it can: • slow down queries • retrieve unnecessary data • break code when schema changes Better approach: SELECT order_id, order_date, total_amount FROM orders; 💡 Key takeaway Clean SQL isn't just about style — It makes your queries faster to understand, easier to maintain, and more production-ready. Small habits like these make a big difference in real data projects. Curious to know 👇 What’s one SQL habit that improved your queries the most? #SQL #DataAnalytics #LearningInPublic #SQLTips #DataAnalyticsJourney

📘 SQL Journey – Day 28: Mastering Subqueries (Types & Practical Clarity) Today’s focus was on strengthening my understanding of different types of subqueries and when to use each in real-world scenarios. Instead of just writing queries, I focused on choosing the right type of subquery for the problem. ⸻ 🔹 Types of Subqueries (Deep Understanding) ✅ Single Row Subquery • Returns only one value • Used with operators (=, >, <, >=, <=) • Example use: Compare salary with average salary ✅ Multi Row Subquery • Returns multiple values • Example use: Filter records based on multiple matching values ✅ Correlated Subquery • Depends on outer query → Executes once for each row • Used for row-wise comparisons • More powerful but can impact performance if not optimized ⸻ 🔹 Key Learning Shift ✔ Earlier: “Write a subquery” ✔ Now: “Which subquery type fits this problem?” • That’s the real difference ⸻ 🔹 Important Rules ✔ Subquery executes inside → outside ✔ Data types must match between inner & outer query ✔ Correlated subqueries run multiple times ✔ Can be used in SELECT, WHERE, FROM ⸻ 🔹 Real Use Cases Practiced • Employees earning above department average • Filtering based on dynamic conditions • Replacing row values with aggregated results • Replacing complex joins with subqueries (and vice versa) ⸻ 💡 Day 28 Realization Choosing the right approach (JOIN vs Subquery) That’s what makes you strong. That’s where real problem-solving begins. ⸻ #SQL #Subqueries #AdvancedSQL #DataAnalytics #LearningJourney #RDBMS #HappyToCode

🚀 SQL Roadmap: From Beginner to Advanced 📊 Want to master SQL step-by-step? Here’s a clean, structured roadmap to guide your journey 👇 🔰 1. Basics 📌 Understand what SQL is 📌 Learn Databases & Tables 📌 Master CRUD operations (Create, Read, Update, Delete) 📌 Get familiar with DDL & DML 📊 2. Queries ✨ SELECT, WHERE (AND/OR/NOT) ✨ ORDER BY, GROUP BY ✨ LIMIT & DISTINCT ⚙️ 3. Functions 🔢 Aggregate: COUNT, SUM, AVG, MIN, MAX 🔤 String: UPPER, LOWER, CONCAT 📅 Date: NOW, DATE, DATEDIFF 🧩 NULL handling with COALESCE 🔄 4. Joins 🔗 INNER JOIN 🔗 LEFT & RIGHT JOIN 🔗 FULL JOIN 🔗 SELF & CROSS JOIN 🧠 5. Subqueries 📍 Subqueries in SELECT, FROM, WHERE 📍 Correlated subqueries 🚀 6. Constraints 🔒 PRIMARY KEY, FOREIGN KEY 🔒 UNIQUE, NOT NULL 🔒 CHECK & DEFAULT ⚡ 7. Indexes & Views 📈 Indexing for performance ⚖️ Index trade-offs 👁️ Creating & using views 🔁 8. Normalization 📚 1NF, 2NF, 3NF 📉 Reduce redundancy ⚖️ When to denormalize 🔐 9. Transactions 💾 BEGIN, COMMIT, ROLLBACK 🛡️ ACID properties ⚙️ Isolation levels 🔥 10. Advanced SQL 💡 Window Functions 💡 Common Table Expressions (CTEs) 💡 Stored Procedures & Triggers 🎯 11. Practice & Next Steps 🛠️ Build real-world projects 🎤 Prepare for interviews ⚡ Practice query optimization 💬 Consistency + Practice = SQL Mastery! #SQL #DataAnalytics #DataScience #Learning #TechSkills #Programming #DataEngineering #Database #LearningPath

Day 8 of my SQL Journey 🚀 Today’s challenge: The classic "Duplicate Emails" problem. For today's solution, I focused on an intuitive approach using data aggregation in SQL. Sometimes the best way to solve a problem is to group identical data together and filter based on the size of those groups right out of the gate! 🧠 My Approach: Select the email column from the Person table. Use the GROUP BY clause to combine identical emails into single, distinct groups. Apply the HAVING clause alongside the COUNT() function to filter the results, keeping only the groups where the email count is strictly greater than 1. ⚡ Key Learnings & SQL Gotchas: WHERE vs. HAVING: I learned the crucial difference between these two filtering clauses. WHERE filters individual rows before any grouping happens, while HAVING filters aggregated groups after the GROUP BY operation. Since I needed to filter based on a mathematical count of the groups, HAVING was the exact tool needed! The Power of Aggregation: It’s a great reminder of how powerful database engines are at instantly summarizing large datasets without needing to write custom iterative loops like you would in a standard programming language. 📌 Expected Complexity: Time: O(N) — where N is the number of rows. We can expect this performance assuming the database engine utilizes an efficient hash aggregation method to count the emails in a single scan of the table. Space: O(U) — where U is the number of unique emails. The database needs to allocate temporary memory to store the hash table of distinct emails and their respective counts during the aggregation process.

SQL Optimization isn't about writing less code. It's about understanding what happens AFTER you hit run. Most engineers I know can write SQL. Very few understand what it costs. Here's everything that actually matters: 1. The Query Optimizer isn't magic It builds an execution plan based on statistics. Old or missing statistics = bad plan = slow query. Update your stats. Trust the plan less. 2. SARGability is everything SARG = Search ARGument Able. If your filter can't use an index, it scans the whole table. This breaks SARGability: WHERE YEAR(created_at) = 2024 This doesn't: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' Same result. Completely different cost. 3. Implicit conversions are silent killers ISNULL(Amount, 0) when Amount is decimal? The engine converts everything to int quietly. Your index? Ignored. 4. Execution Plans > Gut Feeling Before optimizing anything read the plan. Look for: Table Scans, Key Lookups, Sort operators. These are your cost red flags. 5. Indexes aren't free Every index you add speeds up reads. But slows down writes. Design for your actual workload. The real lesson? Writing SQL is a skill. Understanding SQL cost is a discipline. One gets the query working. The other keeps the system alive at 3AM. Which of these did nobody teach you formally?👇 Found Insightful? ♻️ Repost in your network and follow Sahil Alam for more. #SQL #DataEngineering #Analytics #Debugging #DataQuality #Learning

🚀 SQL Journey – Day 32: Recursive CTE (Hierarchical Queries) Today’s focus was on Recursive CTEs, one of the most powerful SQL concepts used to work with hierarchical or repeating data. 🔹 What is a Recursive CTE? A Recursive CTE is a CTE that calls itself repeatedly to process hierarchical or sequential data. 👉 Used when data has parent-child relationships 🔹 Basic Structure WITH cte_name AS (  -- Anchor Query (starting point)  SELECT ...  UNION ALL  -- Recursive Query (calls itself)  SELECT ...  FROM table t  JOIN cte_name c ON condition ) SELECT * FROM cte_name; 🔹 Key Components ✔ Anchor Query → Starting rows ✔ Recursive Query → Repeats logic ✔ UNION ALL → Combines results ✔ Stops when no new rows are returned 🔹 Where is it Used? • Employee → Manager hierarchy • Category → Subcategory structure • Organizational charts • Tree-like data traversal 🔹 Concept Understanding (From Today’s Notes) Recursive CTE works step-by-step: 1️⃣ Start with base data (Anchor) 2️⃣ Use result to fetch next level 3️⃣ Repeat until condition fails 👉 Like traversing a tree or graph 🔹 Important Rules • Must use UNION ALL (not UNION) • Recursive part must reference CTE name • Be careful with infinite loops • Can control depth using conditions 🔹 Interview Insight 💡 If a problem involves: • Hierarchy • Levels • Parent-child relationships 👉 Think Recursive CTE immediately 💡 Day 32 Realization Recursive CTE is not just SQL — it’s logic + iteration inside queries. Once you understand this, you can solve complex hierarchical problems easily. SQL is getting deeper. Thinking is getting sharper. HAPPY LEARNING!✨ #SQL #CTE #RecursiveCTE #DataAnalytics #LearningJourney #SQLPractice #RDBMS #TechJourney #CSE

🚀 Day 4 – SQL Learning Journey | Joins Basics Today I explored one of the most important SQL concepts — Joins, which are essential for working with relational data. 📚 What I learned today: 🔗 Joins + Types – INNER JOIN → Matching records only – LEFT JOIN → All left + matched right – RIGHT JOIN → All right + matched left – FULL JOIN → All records from both sides ❌ Cross Join – Produces Cartesian Product – All possible combinations of rows 🔁 Self Join – Join a table with itself – Useful for hierarchy (e.g., employee-manager) ⚖️ Equi Join – Uses = operator for matching columns 📊 Non-Equi Join – Uses conditions like >, <, BETWEEN ⚡ Join Optimization – Use indexes for faster queries – Write efficient join conditions 💡 Key Takeaway: Joins are the backbone of relational databases — mastering them means you can combine, analyze, and extract meaningful data from multiple tables efficiently. Step by step becoming more confident with SQL 🚀 Code pushed to GitHub 📂 🔗 GitHub Repository: https://lnkd.in/g9-fi5GQ #SQL #Database #LearningJourney #AspNetDeveloper #TechGrowth #InterviewPreparation