SQL Joins in SQL Server

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

SQL Server Notes by AB | Note #12 | Histogram In Action | Original Draft Date: 19 Jan, 2022 | Re-posted on 27 Apr, 2026 | #SQLServerWithAmitBansal   In one of my previous notes, I had talked about the histogram, which is one of the most critical things in the stats object. Histogram, as the name suggests, is a bucketing technique of how the column data is distributed - the highest value of a bucket, which defines a boundary, how many rows are between two boundary values, how many rows are equal to a specific boundary value, how many unique values are there between a range and, how many rows are there on average per distinct value. All of this helps the optimizer make the right estimates. Here is a sample histogram on TotalDue column of SalesOrderHeader table (not putting down all the columns for brevity): RANGE_HI_KEY  RANGE_ROWS   EQ_ROWS =============  ============   ========= 26.2769         40              142 30.1444         17              202 . . (more rows) Now, let's we write a query: SELECT * FROM Sales.SalesOrderHeader WHERE TotalDue = 30.1444 The above query will return 202 rows. If check the cardinality estimation from the execution plan, you will observe the Estimated Number of Rows = 202 and the Actual Number of Rows=202. This is perfect and the best-case scenario for the optimizer where the estimate and the actual matched 100%. How did this happen? Well, this is called Histogram Step Hit. The predicate value mentioned in the query (WHERE condition) has a step representation in the histogram and the optimizer does a perfect estimation with EQ_ROWS (the number of rows equal to the step value), which is 202. The above is just a quick explanation of the ways how the optimizer leverages the histogram.   Demo URL: https://lnkd.in/g5-ef5gq. Want to read more SQL notes like this one? Here: https://bit.ly/ABSQLNotes. Looking for deep-dive content on SQL Server Performance Tuning? Get lifetime access to master class recordings. Check this: https://lnkd.in/d-JrAG78

SQL Server Notes by AB | Note #12 | Histogram In Action | Original Draft Date: 19 Jan, 2022 | Re-posted on 27 Apr, 2026 | #SQLServerWithAmitBansal   In one of my previous notes, I had talked about the histogram, which is one of the most critical things in the stats object. Histogram, as the name suggests, is a bucketing technique of how the column data is distributed - the highest value of a bucket, which defines a boundary, how many rows are between two boundary values, how many rows are equal to a specific boundary value, how many unique values are there between a range and, how many rows are there on average per distinct value. All of this helps the optimizer make the right estimates. Here is a sample histogram on TotalDue column of SalesOrderHeader table (not putting down all the columns for brevity): RANGE_HI_KEY  RANGE_ROWS   EQ_ROWS =============  ============   ========= 26.2769         40              142 30.1444         17              202 . . (more rows) Now, let's we write a query: SELECT * FROM Sales.SalesOrderHeader WHERE TotalDue = 30.1444 The above query will return 202 rows. If check the cardinality estimation from the execution plan, you will observe the Estimated Number of Rows = 202 and the Actual Number of Rows=202. This is perfect and the best-case scenario for the optimizer where the estimate and the actual matched 100%. How did this happen? Well, this is called Histogram Step Hit. The predicate value mentioned in the query (WHERE condition) has a step representation in the histogram and the optimizer does a perfect estimation with EQ_ROWS (the number of rows equal to the step value), which is 202. The above is just a quick explanation of the ways how the optimizer leverages the histogram.   Demo URL: https://lnkd.in/g5-ef5gq. Want to read more SQL notes like this one? Here: https://bit.ly/ABSQLNotes. Looking for deep-dive content on SQL Server Performance Tuning? Get lifetime access to master class recordings. Check this: https://lnkd.in/d-JrAG78

🚀 I used to write complex SQL queries… until Window Functions changed everything. There was a time I built a sales dashboard where comparing daily performance meant multiple joins, subqueries, and unnecessary complexity. It worked—but it wasn’t elegant. Then I discovered SQL Window Functions—and everything became simpler, faster, and more intuitive. 💡 What are SQL Window Functions? Window Functions allow you to perform calculations across a set of rows related to the current row—without collapsing the data like GROUP BY. 👉 In simple terms: You get aggregated insights + row-level detail in the same result. That’s what makes them incredibly powerful for analytics. 🔧 Practical Tips & Use Cases Here are some window functions I use almost daily: 🔹 ROW_NUMBER() Assigns a unique number to each row 👉 Use case: Deduplicate records or pick latest transactions 🔹 RANK() vs DENSE_RANK() RANK() skips numbers on ties DENSE_RANK() doesn’t 👉 Use case: Ranking top customers or products 🔹 LAG() & LEAD() Access previous or next row values 👉 Use case: Compare today’s sales with yesterday’s 🔹 PARTITION BY Breaks data into groups (like GROUP BY, but without aggregation) 👉 Use case: Analyze performance per region, customer, or product 🔹 Running Totals with SUM() OVER() 👉 Use case: Track cumulative revenue over time 📊 Real-World Example Let’s say you want to calculate daily sales + cumulative revenue + previous day comparison: SELECT order_date, daily_sales, SUM(daily_sales) OVER (ORDER BY order_date) AS cumulative_sales, LAG(daily_sales) OVER (ORDER BY order_date) AS prev_day_sales FROM sales; 👉 In one query, you get: Daily performance Running total Day-over-day comparison No joins. No subqueries. Just clean logic. 🧠 Personal Insight One of my biggest “aha” moments as an analyst was realizing: 👉 Good SQL isn’t just about getting results—it’s about writing queries that are easy to understand and scale. Window functions helped me: Reduce query complexity Improve performance Make dashboards more dynamic 🎯 Final Thought If you’re still overusing subqueries or complex joins for analytical problems… it’s time to start thinking in windows, not blocks. 💬 Which SQL window function do you use the most in your daily work? #SQL #DataAnalytics #BusinessIntelligence #PowerBI #DataTips

🧠 Ever wondered how SQL Server decides HOW to run your query? You write: SELECT * FROM Orders WHERE CustomerId = 10 But SQL Server has multiple ways to execute it: 👉 Index Seek 👉 Table Scan 👉 Different join strategies So how does it choose? Welcome to the world of the SQL Server Query Optimizer 👇 ⚙️ What is the Query Optimizer? 👉 It’s the brain of SQL Server Its job: Find the most efficient way to execute your query 💡 Not the fastest always… 👉 The lowest estimated cost plan 🔍 What Happens Under the Hood? When you run a query, SQL Server goes through steps: 1. Parsing 👉 Checks syntax & converts query into a logical tree 2. Optimization (Magic happens here ✨) 👉 SQL generates multiple execution plans It evaluates: Table size Indexes Statistics Join methods 👉 Then assigns a cost to each plan 3. Plan Selection 👉 Picks the plan with lowest estimated cost 4. Execution 👉 Runs the chosen plan 💡 This is what you see in the Execution Plan 🤔 Why Sometimes SQL Makes Bad Decisions? Because it depends on: 👉 Statistics If stats are: Outdated Inaccurate 👉 SQL may choose: ❌ Table Scan instead of Index Seek ❌ Wrong join type ⚡ Real Example Query: SELECT * FROM Orders WHERE CustomerId = 10 🔍 Scenario 1: Index exists Few matching rows 👉 Plan: ✔ Index Seek (fast) 🔍 Scenario 2: No index Large table 👉 Plan: ❌ Table Scan (slow) 🔥 Scenario 3 (Interesting): Index exists But SQL thinks many rows will match 👉 Plan: ❌ Table Scan (even though index exists 😬) 💡 Key Concepts You Should Know 👉 Cost-based optimization 👉 Cardinality estimation (row prediction) 👉 Plan caching (reuse execution plan) 💡 These directly affect performance 🐢 Real-World Insight Ever seen this? 👉 Query fast sometimes 👉 Slow other times 💡 Reason: Cached execution plan Different parameters Wrong estimation 🔥 Pro Tips ✔ Keep statistics updated ✔ Create proper indexes ✔ Always check execution plan ✔ Don’t blindly trust SQL—it can be wrong 💡 Final Thought SQL Server is powerful… But it’s not magic. 👉 It makes decisions based on data it knows If that data is wrong… 👉 Your performance will be too 💬 Comment “OPTIMIZER” and I’ll break down execution plan operators next. #SQLServer #QueryOptimizer #DatabasePerformance #ExecutionPlan #SQLTips  #BackendDevelopment #DotNet #SoftwareEngineering #PerformanceTuning  #DataEngineering #Developers #Programming #SystemDesign #CodingTips  #TechCareers #TechCommunity #LearnSQL

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

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 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

This is a terrific technical piece on optimizing SQL Server when using PowerBI. But even more importantly, it touches on an important Requirements point: 'The honest conversation with stakeholders usually starts with the question: "What's your actual data freshness requirement?" Most teams discover that "real-time" means "within the hour" once they think it through. If that's the case, Import with scheduled refresh covers it, and you skip the entire DirectQuery tuning exercise.' This is true (and often it's less frequent than that) But I'll add two other things: 1] whenever possible you should model your data. Even if you aren't using a dedicated data warehouse, you can create a SQL Server reporting database or at least a reporting schema. Shifting your logic "left" will simplify your PBI queries and help standardize data model logic. 2] don't just stop with the honest conversation about data freshness. Make sure that you really understand how they are using the dashboard. The minute the phrase "export to excel" comes up, consider whether you even need a dashboard, or if different delivery approach makes more sense. (PowerBI should not be another ETL tool.) https://lnkd.in/ee8_rPY2

⚠️ DAY 14/15 — SQL TRAP: No Index vs Index Same query. Same table. Same result. One takes 120 seconds. Other takes 0.1 seconds. The difference? One line of code. 👇 🎯 The Situation: Your table has 1 million employees. You search for one employee by name. No error. Query runs. Result comes. But it takes 2 whole minutes. 😵 Your dashboard is freezing. Users are complaining. Your manager is asking questions. All because of one missing setup. 🧠 Here's what's happening: Without an index — SQL has no idea where your data lives. So it checks every single row. One by one. All 1 million of them. This is called a FULL TABLE SCAN. Every. Single. Row. Checked. Even when only 1 row matches. With an index — SQL knows exactly where Alice is. Jumps directly to her row. 1 row scanned. Done. ✅ The Numbers: No Index → Rows scanned = 1,000,000 Time = ~120 seconds ❌ With Index → Rows scanned = 1 Time = ~0.1 seconds ✅ 1200x faster. Just from one line — CREATE INDEX idx_name ON employees(name) 💡 Real Life Example: Without index = finding a word in a book by reading every single page from start to finish. 😵 With index = opening the book's index at the back, finding the word, jumping straight to that page. ✅ Same book. Same word. One way is just exhausting. 📌 Save This Rule: → No index = full table scan = checks every row = very slow → Index = direct lookup = jumps to result = very fast → Always index columns used in WHERE, JOIN, ORDER BY → Too many indexes slow down INSERT and UPDATE — balance is key → Use EXPLAIN command to check if your query is using an index 🔑 One Line to Remember: No Index = reads entire table Index = jumps directly to answer On small tables you won't notice. On production data with millions of rows — this is the difference between a fast app and a broken one. 💬 Real Talk: Most slow dashboards and freezing reports in real companies are not caused by bad queries. They are caused by missing indexes. This is one of the first things senior engineers check when a query is running slow. 😎 🙋 Have you ever had a query that worked fine in testing but became super slow in production? Missing index was probably the reason! Comment below 👇 Follow for Day 15 tomorrow — the FINAL day of the series! 🚀 #SQL #SQLForBeginners #DataAnalytics #LearnSQL #SQLTips #DataEngineering #InterviewPrep