SQL Index vs No Index: 1200x Faster with Indexing

📊 SQL Concepts I Revisited Recently — and Why They Matter More Than You Think! As someone working in the tech space, I always knew SQL was important. But recently when I sat down to truly practice it, I realised how many small but critical mistakes we make without even noticing. Here are 13 concepts that genuinely changed how I think about writing queries: 1️⃣ NULL is special Never compare NULL with = — it will always fail silently. Always use IS NULL or IS NOT NULL 2️⃣ WHERE vs HAVING WHERE filters rows before grouping. HAVING filters after grouping and only works with aggregate functions. Mixing these up is one of the most common SQL mistakes! 3️⃣ GROUP BY logic Always group by the column you want to summarise across — not the unique ID. Grouping by a unique ID defeats the entire purpose! 4️⃣ Finding duplicates GROUP BY + HAVING COUNT > 1 is your best friend here. Simple but powerful. 5️⃣ Finding missing records LEFT JOIN on the table you want all records from + IS NULL on the right table column — this pattern will save you in real investigations! 6️⃣ Aggregate functions belong in HAVING Never use SUM, COUNT or AVG in WHERE — they only work after grouping happens. 7️⃣ Subqueries When you can't answer something in one step — break it into two. Inner query runs first, outer query uses that result. 8️⃣ CASE WHEN SQL's version of IF/ELSE. Cleaner than you think and incredibly useful for handling NULLs and conditional logic. 9️⃣ SELECT with GROUP BY When using GROUP BY, your SELECT can only have the grouped column or aggregate functions. No individual columns allowed! 🔟 COUNT vs COUNT(DISTINCT) COUNT(column) counts everything including duplicates. COUNT(DISTINCT column) counts only unique values. Know when to use which! 1️⃣1️⃣ JOIN direction matters Always start your JOIN from the table you want all records from — the direction changes your entire result set! 1️⃣2️⃣ When to use which JOIN Want all records from one side → LEFT JOIN Want only matching records → INNER JOIN 1️⃣3️⃣ Finding missing records using JOINs LEFT JOIN on the main table + IS NULL on the right table column = powerful pattern to find records that don't exist in another table! SQL is one of those skills that seems simple on the surface but has so many nuances underneath. The best way to learn it? Practice with real world scenarios, not just textbook examples. What's the one SQL concept that took you the longest to truly understand? Drop it in the comments! 👇 #SQL #DataAnalysis #TechLearning #FinTech #CareerGrowth #LearningInPublic

Most people don’t struggle with SQL because it’s hard. They struggle because they only learn pieces of it. SQL is not a tool. It’s a system. And if you don’t know the full board, you can’t play the game well. Here are 64 SQL commands every data professional should know 👇 Core Queries SELECT – Retrieve data from a table DISTINCT – Remove duplicate rows WHERE – Filter rows based on conditions ORDER BY – Sort results GROUP BY – Aggregate data into groups HAVING – Filter aggregated results LIMIT – Restrict number of rows returned OFFSET – Skip a number of rows Joins INNER JOIN – Return matching records from both tables LEFT JOIN – All records from left + matching from right RIGHT JOIN – All records from right + matching from left FULL OUTER JOIN – All records from both tables CROSS JOIN – Cartesian product of both tables SELF JOIN – Join a table with itself Filtering & Conditions IN – Match values within a list BETWEEN – Filter within a range LIKE – Pattern matching IS NULL – Check for NULL values IS NOT NULL – Check for non-NULL values EXISTS – Check if subquery returns rows NOT EXISTS – Check if subquery returns no rows Aggregations COUNT – Count rows SUM – Total of values AVG – Average value MIN – Smallest value MAX – Largest value Table Operations CREATE TABLE – Create a new table ALTER TABLE – Modify table structure DROP TABLE – Delete table permanently TRUNCATE TABLE – Remove all rows quickly RENAME – Rename table Data Manipulation INSERT INTO – Add new records UPDATE – Modify existing records DELETE – Remove records MERGE – Upsert (insert/update based on match) Constraints PRIMARY KEY – Unique identifier for rows FOREIGN KEY – Link between tables UNIQUE – Ensure unique values NOT NULL – Prevent NULL values CHECK – Enforce condition on column DEFAULT – Set default value Index & Performance CREATE INDEX – Improve query performance DROP INDEX – Remove index Views & Advanced CREATE VIEW – Virtual table from query DROP VIEW – Remove a view MATERIALIZED VIEW – Precomputed stored view Window Functions ROW_NUMBER – Assign unique row numbers RANK – Rank with gaps DENSE_RANK – Rank without gaps LEAD – Access next row value LAG – Access previous row value Set Operations UNION – Combine results (remove duplicates) UNION ALL – Combine results (keep duplicates) INTERSECT – Common records EXCEPT – Records in first not in second Advanced SQL CASE – Conditional logic COALESCE – First non-null value NULLIF – Return NULL if values match WITH (CTE) – Temporary result set SUBQUERY – Query inside a query Transactions BEGIN – Start transaction COMMIT – Save changes ROLLBACK – Undo changes If SQL is your chessboard, these are your pieces. Master them, and you stop writing queries… you start thinking in SQL. 📌 𝗥𝗲𝗴𝗶𝘀𝘁𝗿𝗮𝘁𝗶𝗼𝗻𝘀 𝗮𝗿𝗲 𝗼𝗽𝗲𝗻 𝗳𝗼𝗿 𝗼𝘂𝗿 𝟮𝗻𝗱 𝗯𝗮𝘁𝗰𝗵 𝗼𝗳 𝗗𝗮𝘁𝗮 𝗘𝗻𝗴𝗶𝗻𝗲𝗲𝗿𝗶𝗻𝗴 𝗖𝗼𝗵𝗼𝗿𝘁 , 𝗘𝗻𝗿𝗼𝗹𝗹 𝗵𝗲𝗿𝗲- https://rzp.io/rzp/May2026

Day 10 100 Days SQL challenge by Ankit Bansal create table spending  ( user_id int, spend_date date, platform varchar(10), amount int ); insert into spending values(1,'2019-07-01','mobile',100),(1,'2019-07-01','desktop',100),(2,'2019-07-01','mobile',100) ,(2,'2019-07-02','mobile',100),(3,'2019-07-01','desktop',100),(3,'2019-07-02','desktop',100); Question: Write a SQL query to find the total number of users and total amount spent using mobile only,desktop only and both mobile and desktop  together for each date? Approach: 1. The CTE (all_spend) This part "pre-categorizes" every user for every day they made a purchase. It uses UNION ALL to combine three different scenarios: Scenario A: Single Platform Users Having count(distinct platform) = 1: This filters for users who only used one platform (either 'mobile' or 'desktop') on that specific day. max(platform): Since they only used one, the max() (or min()) simply returns the name of that platform. Scenario B: Multi-Platform Users Having count(distinct platform) = 2: This identifies users who made purchases on both mobile and desktop on the same day. 'Both' as platform: It manually overrides the platform name to "Both" for these users. Scenario C: Placeholder Records This part ensures that "Both" appears as a category for every date in the final output, even if no user actually bought on both platforms that day. It adds a dummy row with $0 amount and NULL user ID.  2. The Final Selection Once the all_spend table is ready, the main query aggregates the data:  sum(amount): Calculates the total revenue for each platform per day. count(distinct user_id): Counts how many unique users fall into each category. Note that the placeholder NULL user IDs from Scenario C aren't counted because COUNT(DISTINCT user_id) ignores NULLs. group by spend_date, platform: Ensures you get a breakdown by day and by platform type

Most people create indexes hoping queries get faster. Half the time they make things SLOWER. Here's what they missed: --- 📖 CLUSTERED vs NON-CLUSTERED INDEXING A CLUSTERED index physically reorders the table rows to match the index key. One per table. Think of it like a dictionary — words ARE sorted alphabetically. A NON-CLUSTERED index is a separate structure pointing back to the actual rows. Like a book's index — it lists terms with page numbers, but the book's content stays untouched. You can have up to 999 per table in SQL Server. --- CLUSTERED INDEX — create on your most queried column: CREATE CLUSTERED INDEX idx_order_date ON Orders(OrderDate); Now a range query like: SELECT * FROM Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31'; ...does a contiguous disk read. FAST. --- NON-CLUSTERED INDEX — for selective lookups: CREATE NONCLUSTERED INDEX idx_customer_email ON Customers(Email) INCLUDE (CustomerName, City); The INCLUDE keyword is key — it adds columns to the leaf level of the index, avoiding a KEY LOOKUP back to the base table. Most people skip this and wonder why their query still scans. --- GOTCHA — most people don't know this: If your non-clustered index doesn't COVER the query, SQL Server does a KEY LOOKUP (also called a Bookmark Lookup). For large result sets, this is WORSE than a full table scan. Check it with: SET STATISTICS IO ON; SELECT CustomerName, City FROM Customers WHERE Email = 'rushi@gmail.com'; Look for "logical reads" in the output. If it's high despite the index, your index isn't covering. Add the missing columns to INCLUDE. Also — every index you create SLOWS DOWN INSERT, UPDATE, and DELETE because SQL Server must maintain each index on write. A table with 15 non-clustered indexes on a high-write OLTP system is a performance disaster. --- COMPOSITE INDEX ORDER MATTERS: CREATE INDEX idx_sales ON Sales(Region, ProductID, SaleDate); This index helps: WHERE Region = 'West' AND ProductID = 101 This index DOESN'T help: WHERE SaleDate = '2024-06-01' (skips leading columns) The leftmost prefix rule — always build indexes based on your actual WHERE clause order. --- WHY THIS MATTERS FOR YOUR CAREER: Interviewers don't want definitions. They ask: "Your query returns 10M rows in 45 seconds. How do you fix it?" If your answer doesn't include EXPLAIN / execution plans, covering indexes, and write-cost tradeoffs — you haven't answered the question. Query optimization is where junior analysts become senior engineers. --- #SQL #DataAnalytics #DatabaseOptimization #DataEngineering #SQLPerformance

🚀 SQL Server Execution Plan — The Secret Behind Every Slow Query Most developers try to optimize queries by guessing. But the real answer is always in one place: 👉 Execution Plan Let’s break it down in a practical way 👇 🔍 Estimated vs Actual Execution Plan Estimated Execution Plan Generated before query execution Based on statistics Shows what SQL Server thinks will happen Actual Execution Plan Generated after execution Shows real data (rows processed, actual cost) Helps identify real performance issues 💡 Rule: If your query is slow → always check the Actual Execution Plan ⚙️ Key Operators You MUST Understand 1. Nested Loop (🔁) Best for small datasets Works like: For each row in table A → scan table B ✅ Fast when: One table is small Proper indexes exist ❌ Bad when: Large datasets → becomes very slow 2. Hash Match (🧠) Used for large, unsorted data Creates a hash table in memory ✅ Good for: Large joins No indexes ❌ Downside: High memory usage 3. Merge Join (🔀) Requires sorted data Very efficient for large datasets ✅ Fast when: Both inputs are sorted (via index) ❌ Problem: Sorting itself can be expensive 🤔 Why SQL Chooses One Over Another? SQL Server Query Optimizer decides based on: Table size Available indexes Data distribution (statistics) Estimated cost 👉 It always picks the lowest estimated cost plan But here's the catch: ⚠️ If statistics are outdated → SQL can choose the wrong plan 🐢 Real Example: Fixing a Slow Query Problem: A query was taking 8–10 seconds SELECT o.OrderId, c.CustomerName FROM Orders o JOIN Customers c ON o.CustomerId = c.Id WHERE c.City = 'Lahore' 🔍 Execution Plan Analysis: Operator used: Hash Match Reason: No index on Customers.City SQL scanned entire table 😬 ✅ Fix: Added index: CREATE INDEX IX_Customers_City ON Customers(City); ⚡ Result: Execution plan switched to Nested Loop Query time reduced: 👉 10 sec → 200 ms 💡 Key Takeaways ✔ Don’t guess — read the execution plan ✔ Indexes can completely change performance ✔ Hash Match ≠ bad, but depends on scenario ✔ Always compare Estimated vs Actual 🔥 Pro Tip Next time your API is slow: 👉 Open Execution Plan 👉 Look for: Table Scans High cost operators Missing indexes That’s where the real problem is. 💬 If you found this useful, comment “PLAN” and I’ll share more real-world SQL optimization tricks. #SQLServer #DatabasePerformance #QueryOptimization #ExecutionPlan #SQLTips  #BackendDevelopment #DotNet #SoftwareEngineering #TechCareers #Developers  #Programming #CodeOptimization #APIPerformance #DataEngineering #SQLTuning  #PerformanceTuning #LearnSQL #CodingTips #TechCommunity #LinkedInTech

Fault‑Finding T‑SQL with Simple Relationship Diagrams ➖➖➖➖➖➖➖➖➖➖➖➖➖➖➖ By sketching each table as a box and each JOIN as a line, you instantly expose the structure your query actually represents—not the structure you think it represents. INNER JOINs show you where data must exist. LEFT JOINs show you where data may be missing. A few things happen when you diagram your JOINs: ▪️You spot incorrect join paths that hide or duplicate rows. ▪️You see missing relationships that explain unexpected NULLs. ▪️You understand the data flow from source tables through to the final output. ▪️You can reason about the logic, not just the syntax. Study the values of the keys returned ---------------------------------------------------------- A key part of this is looking closely at primary keys and foreign keys. They show you where rows should link cleanly — and where they don’t. Missing FK matches explain unexpected NULLs, while incorrect or non‑unique keys often point straight to duplicated rows. Once you map the keys visually, the data issues become obvious. Deconstructioning Database Objects ---------------------------------------------------------- To properly fault‑find, take the SQL definition from the view or stored procedure and convert it into a stand‑alone, runnable query in SSMS. That usually means removing the CREATE VIEW or CREATE PROCEDURE wrapper, stripping out parameters, and commenting out any logic that prevents direct execution. Once you’re left with a plain SELECT statement, you can run it raw, add temporary filters, remove filters, isolate JOINs, and test each part independently. This “unwrapped” version gives you full visibility into what the query is actually doing, making it far easier to spot missing data, duplicated rows, or broken join paths. Summation ------------------ If you can see the relationships, you can understand the behaviour — and once you understand the behaviour, fault‑finding becomes far easier.

Sometimes the best way forward is to go back to the basics. ⏪ Today I revisited advanced SQL concepts, specifically views, indexes, subqueries, constraints. 🔷 Virtual Tables & Views A View is a database object created using a SELECT query, it looks like a table but stores NO data. The DBMS dynamically fetches data from the underlying source tables when you query a view. ✅ Views help hide sensitive columns (like SSN, salary), improve security and simplify complex queries. ❌ Downside? Slower than direct table access , can become obsolete if base tables are dropped. 🛠️ Pro tip: The "OR REPLACE" clause When creating a view, using CREATE OR REPLACE VIEW is a best practice. It allows us to update the view's definition without having to drop the existing one first! 🔷 Indexes An index is a data structure that speeds up data retrieval. Key Types: - Unique Index - Enforces uniqueness, no duplicates - Simple Index -Allows duplicates, optimizes searches Syntax - CREATE UNIQUE INDEX idx_name ON table_name (column); 🔷 Subqueries A subquery is a query nested inside another query. The inner query runs first, passes results to the outer query. 🔷 SQL Constraints Constraints are rules applied to table columns to control what data gets stored. Think of them as gatekeepers for your database! 🚦 6 must know constraint types: 1. NOT NULL - Column cannot be left empty. Every record must have a value in that column. 2. UNIQUE - No two rows can have the same value in that column. Duplicates are strictly not allowed. 3. PRIMARY KEY - The combination of NOT NULL + UNIQUE. Uniquely identifies every row in a table. One per table. 4. FOREIGN KEY - Links two tables together for referential integrity. The column must match a primary key in another table. 5. CHECK - Validates data against a condition before inserting. Example: age must be greater than 18, any value below that gets rejected automatically. 6. DEFAULT - If no value is provided during insert, a preset default value is automatically used for that column. 🎯 Quick Takeaways ✅ Views = Security + Reusability (but slower performance) ✅ Indexes = Speed (essential for large tables) ✅ Subqueries = Clean separation of logic Save this post for your next SQL interview or exam prep! Which concept do you find trickiest - views, indexes, subqueries or constraints? Drop it below 👇 #SQL #DatabaseManagement #DBMS #Constraints #Views #Indexes #Subqueries #DataEngineering

A query was taking 4 minutes in production. I spent 2 days rewriting the SQL logic. Tried different joins. Tried different filters. Tried breaking it into CTEs. Nothing helped. Then my senior said: "Did you look at the execution plan?" I didn't even know what that was. I looked. Found the problem in 30 seconds. Fixed it in 5 minutes. Query now runs in 12 seconds. 👇 ──────────────── What is an execution plan? It is the database's internal roadmap. It shows you EXACTLY what happens when your query runs — step by step. Which tables it scans. Which indexes it uses. Where it is spending the most time. Stop guessing. Start reading the plan. ──────────────── How to read it — Snowflake and SQL Server: ----- SNOWFLAKE: use EXPLAIN EXPLAIN SELECT c.customer_name, SUM(o.revenue) AS total_revenue FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= '2024-01-01' GROUP BY c.customer_name; ----- SQL SERVER: use SET STATISTICS SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT c.customer_name, SUM(o.revenue) AS total_revenue FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= '2024-01-01' GROUP BY c.customer_name; ──────────────── 3 things to look for first: → Full table scan — reading ALL rows when you need 100 Fix: add a clustering key or index on filter columns → Wrong row estimates — engine guesses wrong counts Fix: update table statistics so planner works correctly → Nested loops on large tables — wrong join algorithm Fix: ensure join columns are indexed on both sides ──────────────── What I found in my 4 minute query: Full table scan on a 200 million row table. No index on order_date. One clustering key later → 4 minutes became 12 seconds. I had rewritten SQL for 2 days. The fix was one command. Found in 30 seconds. ──────────────── Read the execution plan before you rewrite the query. Always. Without exception. What is the slowest query you have ever fixed? Drop the before and after below 👇 #SQL #DataEngineering #QueryOptimization #DataEngineer #SQLServer #Snowflake #DatabasePerformance #Analytics #DataWarehouse #TechTips #OpenToWork #DataCommunity #HiringDataEngineers #100DaysOfSQL

5 SQL window functions every analyst should know. Here's what each one actually does — in plain English. 👇 --- Window functions are the dividing line between SQL that fetches data and SQL that reasons about it. They let you calculate — across a group of rows — without collapsing those rows into one. That one capability changes everything. --- 𝟭. 𝗥𝗢𝗪_𝗡𝗨𝗠𝗕𝗘𝗥() — 𝗴𝗶𝘃𝗲 𝗲𝘃𝗲𝗿𝘆 𝗿𝗼𝘄 𝗮 𝗽𝗼𝘀𝗶𝘁𝗶𝗼𝗻 Assigns a unique sequential number to each row in a partition. Use it when: → You need to dedupe rows ("keep only the first") → You want the latest record per customer / order / session → You need pagination logic Simple but underrated. The first window function I reached for in real work. --- 𝟮. 𝗥𝗔𝗡𝗞() — 𝗿𝗮𝗻𝗸 𝗿𝗼𝘄𝘀, 𝘁𝗶𝗲𝘀 𝗮𝗹𝗹𝗼𝘄𝗲𝗱 Like ROW_NUMBER but with a twist: tied values get the same rank. Use it when: → You're building leaderboards (top spenders, top products) → Ties matter — two customers spending exactly the same should both rank #1 → You want to find the "top N per group" In my Music Store project, I used RANK() to find the most popular genre in every country in a single query. --- 𝟯. 𝗟𝗔𝗚() — 𝗹𝗼𝗼𝗸 𝗯𝗮𝗰𝗸𝘄𝗮𝗿𝗱𝘀 Fetches the value from a previous row in the same partition. Use it when: → Calculating period-over-period change (this month vs last month) → Detecting customer churn (last purchase date vs current) → Building cohort or retention analysis This is the function that turns "a list of transactions" into "a story of behaviour over time." --- 𝟰. 𝗟𝗘𝗔𝗗() — 𝗹𝗼𝗼𝗸 𝗳𝗼𝗿𝘄𝗮𝗿𝗱𝘀 The opposite of LAG. Fetches the value from a future row. Use it when: → Checking what a customer did next (next purchase, next event) → Calculating time gaps between actions → Predicting churn windows ("if no LEAD within 30 days → likely lost") LAG and LEAD together let you compare any row to its neighbours — without writing a single self-join. --- 𝟱. 𝗦𝗨𝗠() 𝗢𝗩𝗘𝗥 (…) — 𝗿𝘂𝗻𝗻𝗶𝗻𝗴 𝘁𝗼𝘁𝗮𝗹𝘀 A cumulative sum that updates row by row. Use it when: → Calculating running revenue or running counts → Tracking cumulative customer value over time → Building burn-down or burn-up reports With ORDER BY inside the OVER clause, every row gets the running total "as of that point." No looping. No subqueries. One pass. --- 𝗧𝗵𝗲 𝗯𝗶𝗴𝗴𝗲𝗿 𝘁𝗮𝗸𝗲𝗮𝘄𝗮𝘆 GROUP BY collapses rows into a summary. Window functions calculate across rows while keeping every row visible. That single difference is what makes them so powerful for analytics — because most business questions need both the detail and the aggregate, side by side. --- Save this for next time you write a query that feels like it needs a self-join. It probably doesn't. It probably needs a window function. Which one of these do you reach for most often? Curious to see what people use in production. #SQL #DataAnalytics #DataScience #SQLTips #BusinessIntelligence #DatabaseDesign

⚠️ DAY 13/15 — SQL TRAP: PARTITION BY vs GROUP BY GROUP BY collapses your data. PARTITION BY keeps everything. Most people don't know the difference. 👇 🎯 The Situation: You want to show each employee's name, their salary AND their department's total salary — all in the same row. You try GROUP BY. Result → only 2 rows. 😵 All employee names and individual salaries — GONE. Just department totals left. But you needed BOTH individual details AND group totals together! 🧠 Here's the simple difference: GROUP BY → collapses all rows into groups. You lose individual employee details forever. 5 employees become 2 department rows. PARTITION BY → calculates the group total BUT keeps every row intact. 5 employees stay as 5 rows. Each row also shows their department total. Same calculation. Completely different output. ✅ The Result Difference: GROUP BY result → Engineering = 24000 Marketing = 11000 (Only 2 rows. Names gone.) ❌ PARTITION BY result → Alice → 9000 → dept total 24000 ✅ Bob → 7000 → dept total 24000 ✅ Carol → 6000 → dept total 11000 ✅ Dave → 5000 → dept total 11000 ✅ Eve → 8000 → dept total 24000 ✅ All 5 rows. Individual salaries. Department totals. Everything together. 💡 Real Life Example: Imagine a school report. GROUP BY = show only class average. Individual student marks disappear. 😵 PARTITION BY = show every student's mark AND their class average side by side. ✅ One gives you a summary. Other gives you full detail with context. 📌 Save This Rule: → Need only group totals? → GROUP BY → Need individual rows AND group totals together? → PARTITION BY → PARTITION BY always uses OVER() → SUM(salary) OVER(PARTITION BY department) → GROUP BY collapses rows → you lose individual details → PARTITION BY never collapses → all rows always stay 🔑 One Line to Remember: GROUP BY = collapses rows = summary only PARTITION BY = keeps all rows = detail + summary together This is called a Window Function. One of the most powerful and most feared SQL concepts in interviews. 😎 💬 Real Talk: Window functions like PARTITION BY separate beginner SQL from advanced SQL. If you understand this — you're already ahead of most candidates in interviews. 🙋 Quick Quiz: If you use PARTITION BY department — how many rows will you get for a table with 10 employees across 3 departments? Drop your answer below 👇 Follow for Day 14 tomorrow — almost at the finish line! 🚀 #SQL #SQLForBeginners #WindowFunctions #DataAnalytics #LearnSQL #SQLTips #DataEngineering #InterviewPrep