SQL Tutorial: Complex Transformations & WITH Clause

Day 18/30 of SQL Challenge Today I learned: FULL JOIN After exploring INNER, LEFT, and RIGHT JOIN, today was about combining everything together. Concept: FULL JOIN returns all records from both tables. If there is a match, data is combined. If there is no match, NULL values appear for the missing side. Basic syntax: SELECT columns FROM table1 FULL JOIN table2 ON table1.column = table2.column; Example: SELECT customers.name, orders.id FROM customers FULL JOIN orders ON customers.id = orders.customer_id; Explanation: * All customers are included * All orders are included * Matching records are combined * Non-matching records show NULL values Key understanding: FULL JOIN gives a complete view of both tables, including matched and unmatched data. Practical use cases: * Finding all matched and unmatched records * Data comparison between two tables * Identifying missing relationships on both sides Important note: Not all databases support FULL JOIN directly (like MySQL). In such cases, it can be simulated using UNION of LEFT JOIN and RIGHT JOIN. Example (conceptual idea): SELECT ... FROM customers LEFT JOIN orders ON ... UNION SELECT ... FROM customers RIGHT JOIN orders ON ... Reflection: Today helped me understand how to analyze complete datasets, including gaps and mismatches not just perfect matches. #SQL #LearningInPublic #Data #BackendDevelopment #SQLPractice #BuildInPublic

🚀 Day 32/100 — SQL Subqueries: Thinking Inside Queries 🧠💻 Today I learned Subqueries, a powerful concept in SQL used to solve complex problems step by step. 📊 What is a Subquery? 👉 A query inside another query ➡️ Used to break down complex problems into simpler parts 📌 What I explored today: 🔹 Subqueries in SELECT 🔹 Subqueries in WHERE 🔹 Subqueries in FROM 🔹 Nested queries for filtering 💻 Example Scenario: 👉 Find customers who made orders above the average order value 📌 Example Query: SELECT customer_id, order_amount FROM orders WHERE order_amount > ( SELECT AVG(order_amount) FROM orders ); 📊 How it works: 👉 Inner query → calculates average 👉 Outer query → filters higher-than-average orders 🔥 Key Learnings: 💡 Subqueries help solve complex business questions 💡 Makes SQL more flexible and powerful 💡 Commonly asked in interviews 🚀 Real-world use cases: ✔ Filtering based on averages ✔ Comparing values within datasets ✔ Dynamic data selection 🔥 Pro Tip: 👉 Use subqueries when: You need step-by-step filtering OR when JOINs become complex 📊 Tools Used: SQL | MySQL ✅ Day 32 complete. 👉 Quick question: Do you prefer solving problems using JOINs or Subqueries? 🤔 #Day32 #100DaysOfData #SQL #Subqueries #DataAnalytics #LearningInPublic #CareerGrowth #JobReady #InterviewPrep

L38 (29) inner join: where your data overlaps. an `inner join` is the most common type of join in sql. it acts as a strict filter, returning *only* the rows that have matching values in both tables based on your join condition. if a row exists in table a but has no match in table b, it gets dropped from the result set. here is how you pull matching records from a `customer` table and an `orders` table: > the syntax: select columns  from table1  inner join table2  on table1.column = table2.column; > the real-world query: select customer.id, customer.name, orders.ordername from customer inner join orders on customer.id = orders.id; tip: the hidden default! did you know that `inner join` is the default join type in sql? if you are reading someone else's code and they simply typed `join` instead of `inner join`, the database engine is automatically executing an inner join under the hood. however, explicitly writing `inner join` is considered best practice for code readability! #DBMS #SQL #Databases

🚀 Day 27 – SQL Journey | Subqueries Deep Dive (Advanced Practice) Today, I explored one of the most powerful and essential concepts in SQL — Subqueries 🔍 I focused on understanding how subqueries work internally and how they help solve complex problems by breaking them into smaller, logical steps. 💡 What I Learned: ✔ Subqueries inside SELECT and WHERE clauses ✔ Handling intermediate results using nested queries ✔ Comparing values dynamically using subqueries ✔ Writing flexible and condition-based SQL queries 📌 Types of Subqueries Practiced: 🔹 Single Row Subqueries 🔹 Multi Row Subqueries (IN, ANY, ALL) 🔹 Correlated Subqueries (row-by-row execution) ⚙️ Key Takeaways: Subqueries execute from inside → outside Outer queries depend on inner results Data type compatibility is important Can be nested at multiple levels 🔥 Real-World Insight: Subqueries are powerful but can impact performance if not used efficiently. In many cases, JOINs can be a better alternative depending on the scenario. Subqueries are not just a concept — they are a problem-solving mindset in SQL. #SQL #Subqueries #AdvancedSQL #DataAnalytics #LearningJourney #SQLPractice #RDBMS

You write SQL from top to bottom. Your database reads it in a completely different order. 🧠🔍 Ever wondered why you can't use an alias you created in a SELECT statement inside your WHERE clause? Or why HAVING feels like a second WHERE? It’s because of the Logical Order of Execution. If you want to debug like a pro and stop guessing why your queries are failing, you need to memorize this "Cheat Code." 📜 The SQL Execution Cheat Sheet: Think of your query like a filter. It doesn't start at the top; it starts with the Source. FROM / JOIN: "Where is the data coming from?" (The database grabs the tables first). WHERE: "Which rows do I need?" (It filters the raw data). GROUP BY: "How should I bucket them?" (It organizes rows into groups). HAVING: "Which groups do I keep?" (It filters the groups, NOT the rows). SELECT: "What columns do I show?" (Finally! This is where aliases are born). DISTINCT: "Any duplicates?" (It cleans the final view). ORDER BY: "How should it look?" (The very last thing—sorting). TOP / LIMIT: "How many should I send back?" #SQL #DataEngineering #CodingTips #MsSQL #Database #CareerAdvice #TechHacks #SanthoshS

🧠 SQL Execution Plan — The Secret Behind Fast Queries Writing a SQL query is easy. Writing a fast SQL query is what makes the real difference in interviews and production systems 👇 Whenever a query is slow, the first thing every developer should check is the Execution Plan. 🔷 What is an Execution Plan? An Execution Plan shows how SQL Server decides to execute your query. 👉 It tells you: • Which table SQL Server accesses first • What type of joins are being used • Whether it is performing a Scan or a Seek • Which operation is taking the highest cost • Where the query is spending most of its time 💡 In simple words: it is the roadmap SQL Server follows to fetch your data. 🔷 Why is it Important? Two queries may return the same result, but one may take: ✅ 1 second ❌ 30 seconds The Execution Plan helps you understand why. It helps in: • Query optimization • Finding performance bottlenecks • Reducing logical reads • Improving production performance Without checking the execution plan, optimization becomes guesswork. 🔷 Types of Execution Plans ✅ Estimated Execution Plan → Shows what SQL Server plans to do before execution Shortcut: Ctrl + L ✅ Actual Execution Plan → Shows what SQL Server actually did after execution Shortcut: Ctrl + M 💡 Actual Execution Plan is more useful for performance tuning. 🔷 Common Operators You Should Know 🔸 Table Scan → Reads the entire table ❌ Slow for large tables 🔸 Index Scan → Scans many rows from an index ⚠️ Better than Table Scan 🔸 Index Seek → Directly jumps to required rows ✅ Fast and efficient 🔸 Key Lookup → Fetches extra columns from the main table ⚠️ Too many can slow performance 🔸 Nested Loop / Hash Match / Merge Join → Join strategies chosen by SQL Server 🔷 Interview Question Q: How do you identify why a query is slow? 👉 I first check the Actual Execution Plan, look for scans, key lookups, and expensive joins, then optimize the query accordingly. This shows practical knowledge, not just theory. 💡 Final Thought Anyone can write SQL queries. But understanding the Execution Plan is what makes you a better developer🚀 Stay tuned for my next post on how to use indexes according to the Execution Plan in SQL Server😊 #sqlserver #sql #executionplan #database #performanceoptimization #backenddeveloper #interviewprep #sqldeveloper #queryoptimization #dotnetdeveloper

Day 15/365 - SQL Tip: Mastering Conditional JOINs A Conditional JOIN is a powerful SQL technique where you add extra conditions directly inside the `ON` clause. Instead of simply matching rows using a key, you can control exactly which records should be joined. 📌 Basic Example SELECT   c.customer_name,   o.order_id,   o.order_status FROM customers c LEFT JOIN orders o   ON c.customer_id = o.customer_id   AND o.order_status = 'Completed'; In this query: * All customers are returned * Only completed orders are joined * Customers without completed orders still appear ❓Why This Matters Placing conditions in the `ON` clause preserves the behavior of an OUTER JOIN. If you move the condition to the `WHERE` clause, your `LEFT JOIN` can accidentally turn into an `INNER JOIN`. ❌ Risky Approach: The below query removes customers who have no completed orders. SELECT * FROM customers c LEFT JOIN orders o   ON c.customer_id = o.customer_id WHERE o.order_status = 'Completed'; ✅ Best Practice: Always place filtering conditions for the joined table inside the `ON` clause when working with `LEFT JOIN`. Where is this applicable in real-world scenarios? • Active customers only • Recent transactions • Date-range joins • Soft-delete handling • Category-specific matching Master this concept, and your SQL skills will level up instantly. #SQL #DataAnalytics #DataEngineering #LearnSQL #SQLTips #Database #Analytics #BusinessIntelligence #DataScience #ConditionalJoin

Day 86 – SQL JOIN (INNER, LEFT, RIGHT) Today I learned how to combine data from multiple tables using JOIN in SQL. JOIN is one of the most powerful concepts in databases because it helps us fetch related data from different tables. 🔹 What is JOIN? JOIN is used to combine rows from two or more tables based on a related column. 🔹 1️⃣ INNER JOIN INNER JOIN returns only the rows that have matching values in both tables. Example: SELECT E10.name, E10.id, E11.age FROM E10 INNER JOIN E11 ON E10.id = E11.id; ✔️ Returns only common matching records ✔️ Non-matching data will be ignored 🔹 2️⃣ LEFT JOIN LEFT JOIN returns: ✔️ All records from the left table ✔️ Matching records from the right table If no match → shows NULL Example: SELECT E12.name, E12.id, E13.age FROM E12 LEFT JOIN E13 ON E12.id = E13.id ORDER BY E12.id; ✔️ All data from left table (E12) ✔️ Non-matching rows show NULL values 🔹 3️⃣ RIGHT JOIN RIGHT JOIN is the opposite of LEFT JOIN. ✔️ All records from the right table ✔️ Matching records from the left table ✔️ Non-matching left values → NULL Example: SELECT E14.name, E14.id, E15.age FROM E14 RIGHT JOIN E15 ON E14.id = E15.id ORDER BY E15.id; 🔹 Quick Difference JOIN TypeResultINNER JOINOnly matching dataLEFT JOINAll left + matching rightRIGHT JOINAll right + matching left 🎯 Key Takeaways Today I learned: ✔️ How to combine tables using JOIN ✔️ Difference between INNER, LEFT, RIGHT JOIN ✔️ How NULL appears when no match is found ✔️ Importance of common column (id) in joins These concepts are very important when working with real-world relational databases. #SQL #MySQL #Database #BackendDevelopment #DataAnalysis #WebDevelopment

🚀 Another SQL problem solved — and this one was a great exercise in aggregation + date handling! **Problem:** For each user, find the number of days between their first and last post in 2021 — but only include users who posted at least twice. **My Approach:** * Filter data for the year 2021 * Group by `user_id` * Use `MIN()` and `MAX()` to get first & last post dates * Subtract dates to get the duration * Use `HAVING` to ensure at least 2 posts **Final Query (PostgreSQL):** SELECT    user_id,   MAX(post_date)::date - MIN(post_date)::date AS days_between FROM posts WHERE EXTRACT(YEAR FROM post_date) = 2021 GROUP BY user_id HAVING COUNT(*) >= 2; **Key Learnings 💡** * `MAX(date) - MIN(date)` is a clean way to compute activity span * `HAVING` is essential when filtering aggregated results * Type casting (`::date`) ensures correct subtraction behavior * Always think: filter → group → aggregate → filter again Shoutout to Nick Singh 📕🐒 for creating and sharing such practical SQL problems 🙌 #SQL #PostgreSQL #DataAnalytics #BackendDevelopment #DataEngineering  #InterviewPrep

🚀 Your SQL queries are SLOW — and you might not even know why. I've seen developers write perfect SQL logic… but still kill database performance. 💀 The problem isn't the query. It's the habits behind the query. Here are 6 SQL Query Optimization Techniques every data professional must know 👇 ⚡ Quick Summary: 1️⃣ Use Indexes Effectively → 90% Faster No index on WHERE column = full table scan every time. One line of index creation can change everything. 2️⃣ Avoid SELECT * → 50% Faster You don't need all 40 columns. Ask only what you need. Less I/O = faster results. 3️⃣ Use EXISTS instead of IN → 70% Faster IN evaluates every row. EXISTS stops the moment it finds a match. Smart difference. 🧠 4️⃣ Optimize JOINs with Indexed Columns → 80% Faster Joining on unindexed columns = disaster for large tables. Index your JOIN keys. Always. 5️⃣ Filter Early — WHERE before GROUP BY → 60% Faster Why group 1 million rows when a WHERE clause can reduce it to 10,000 first? 6️⃣ Avoid Functions on Indexed Columns → 85% Faster YEAR(log_date) = 2024 breaks the index. log_date >= '2024-01-01' uses it perfectly. ✅ 💡 The Real Truth: Writing SQL that works is easy. Writing SQL that performs is a skill. And in production environments with millions of rows — the difference between optimized and unoptimized SQL is the difference between 2 seconds and 2 minutes. That's the difference between a junior and a senior data professional. 🔥 🎯 Action Step for today: Open any query you wrote this week. Check — are you using SELECT *? Are you filtering before grouping? Fix one thing. Ship better code. 💪 📌 Save this post — you'll need it every time you write a complex query! ♻️ Repost to help your network write faster, cleaner SQL! 👇 Comment "OPTIMIZE" if you want the full SQL Performance Series! #SQL #SQLOptimization #QueryOptimization #DataEngineering #DatabasePerformance #DataAnalytics #SQLServer #MySQL #PostgreSQL #DataScience #TechSkills #CareerGrowth #DataAnalyst #SoftwareEngineering #BackendDevelopment #LinkedInLearning #ShankarMaheshwari #SQLTips #DataCommunity #LearnSQL