SQL Trap: IN vs EXISTS with NULLs

I used to write SQL like I was fighting the database. Nested subqueries. Temp tables everywhere. Self-joins that made my future self want to cry. Then I learned ROW_NUMBER(). Let me show you what I mean. Say you have a table with millions of transaction records and you need the most recent transaction per customer. Before (the ugly way): SELECT * FROM transactions t WHERE t.date = ( SELECT MAX(date) FROM transactions WHERE customer_id = t.customer_id ) This works. It also runs like it's powered by a hamster on a wheel. On 10M+ records, I once waited 14 minutes for this to finish. Went and made coffee. Came back. Still running. After (window function): SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY date DESC ) AS rn FROM transactions ) ranked WHERE rn = 1 Same result. Ran in under 40 seconds. That's not a minor improvement. That's the difference between "the report is almost ready" and actually having an answer before the meeting starts. The thing nobody tells you about SQL: the jump from intermediate to advanced isn't about learning more functions. It's about stopping the brute force approach and letting the database do what it was designed to do. One function. That was the turning point for me. What's the SQL trick that changed your workflow? Drop it below — I'm always collecting these. — #SQL #DataAnalyst #DataAnalytics #PowerBI #OpenToWork

🚀 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

This is where advanced SQL starts. I recently asked a SQL question in one of my posts. - QS. Finding customers whose spending dropped compared to their last order. You can find the answer here -https://lnkd.in/ghVarZ6d This is an easy-to-moderate question for an interview, BUT if we add a follow-up question to it, then it becomes an advanced SQL question. Follow-up Questions - Get me the same but only for the latest order and the previous one for each user? The catch is you don't know the date; an order can be placed any time, and the previous one also can be any date. You can try LAG() + QUALIFY(). SELECT user_id, order_date, amount, LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date ) AS previous_amount, amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date ) AS difference FROM orders QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC ) = 1; It's simple but powerful. If not QUALIFY(), then what would be your approach?

🧠 SQL Functions — The Complete Cheat Sheet Every Analyst Needs! SQL isn't just SELECT and WHERE. The real power lies in its built-in functions. 👇 1️⃣ Aggregate Functions Summarize your data in one line → COUNT() · SUM() · AVG() · MAX() · MIN() 2️⃣ String Functions Clean, format & transform text → UPPER() · LOWER() · CONCAT() · SUBSTRING() · LENGTH() 3️⃣ Date Functions (MySQL Syntax) Work with time like a pro → NOW() · DATE_ADD() · DATEDIFF() · YEAR/MONTH/DAY 4️⃣ Mathematical Functions Precise calculations, zero effort → ROUND() · CEIL() · FLOOR() · ABS() 5️⃣ Conditional Functions Add logic directly into your queries → COALESCE() · CASE WHEN 🎯 Use these functions to: ✅ Summarize data ✅ Format & clean strings ✅ Handle NULLs gracefully ✅ Calculate time differences ✅ Add if/else logic in queries 💡 Master these 20 functions and you can handle 80% of real-world SQL problems. Save this post 🔖 — your future self will thank you! ♻️ Repost to help someone learning SQL today! #SQL #SQLFunctions #DataAnalytics #DataAnalyst #LearnSQL #SQLTips #DatabaseManagement #SQLInterview #DataEngineering #Analytics #TechLearning #SQLForBeginners #DataScience #CaseWhen #StringFunctions #DateFunctions #ShankarMaheshwari #UpskillDaily #DataCommunity #CareerGrowth

🚀 SQL Basics – Day 5: Subqueries Made Simple Now let’s move one step ahead and understand how to use a query inside another query 🤯 Sounds complex? Don’t worry—it’s actually simple! 👇 🔍 What is a Subquery? 👉 A query written inside another query 🧠 “Query ke andar query” --- 📌 Subquery in WHERE 👉 Used to filter data based on another query 💡 "SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);" 🧠 Employees earning more than average salary --- 📊 Subquery in FROM 👉 Used as a temporary table 💡 "SELECT dept, AVG(salary) FROM (SELECT * FROM employees) AS temp GROUP BY dept;" --- 🎯 Subquery in SELECT 👉 Used to show extra calculated data 💡 "SELECT name, (SELECT AVG(salary) FROM employees) AS avg_salary FROM employees;" --- 😄 Easy way to remember: Subquery = Query inside query WHERE = Filter with subquery FROM = Use as table SELECT = Add extra info --- ✨ Conclusion: Subqueries help you solve complex problems step by step 💪 Once you understand this, your SQL level becomes advanced 🚀 📌 Practice different types of subqueries to gain confidence! #SQL #DataAnalytics #SQLBasics #Subquery #LearningSQL #Day5 #DataAnalystii

Day 11/30 of SQL Challenge Today I learned something simple but very powerful for writing clean SQL. -> Aliases At first, my queries worked… but they didn’t look clean or readable. That’s where aliases come in. What are aliases? Aliases are temporary names given to columns or tables to make queries easier to read. Example: SELECT name AS customer_name, salary AS employee_salary FROM employees; What’s happening here? - "name" is renamed as "customer_name" - "salary" is renamed as "employee_salary" This makes the output much more understandable. Small insight: We can also use aliases for tables: SELECT e.name, e.salary FROM employees AS e; This helps especially when writing JOIN queries. Why this matters: As queries get bigger, readability becomes very important. Aliases make your SQL look cleaner and more professional. My reflection: Today I realized writing SQL is not just about getting results, it’s also about writing queries that others can easily understand. #SQL #LearningInPublic #Data #BackendDevelopment

Do you use GROUP BY to find duplicates in SQL? That’s usually the first thing most of us learn and it works well to detect duplicates. But here’s something we get stuck: 👉 What if you actually need to remove duplicates? 👉 How do you identify which rows are the exact duplicates? GROUP BY won’t help much there as it only gives counts, not row-level detail. To handle this properly, you need a way to work at the row level and that’s where ROW_NUMBER() with PARTITION BY becomes useful. I’ve written a short 2-minute tech blog explaining this with a simple example.If you're learning SQL or working with real datasets, this might be useful 👇 #SQL #ROW_NUMBER() #Tech_blog

⚡ Performance Impact of SQL JOINs – What Every Developer Should Know SQL JOINs are powerful—but if used incorrectly, they can seriously impact your query performance. Let’s break it down in a simple way 👇 ------------------------------------------------------ 🔍 Why JOIN Performance Matters When you use JOINs, the database engine has to: • Scan multiple tables • Match rows based on conditions • Return combined results 👉 The larger the data, the heavier the operation. 🔹 INNER JOIN (Faster in Most Cases) Why? Only returns matching records → less data to process ✅ Efficient when: • Both tables are properly indexed • You only need matched data 💡 Tip: Always index the JOIN columns 🔹 LEFT JOIN (Heavier than INNER JOIN) Why? Returns ALL rows from left table + matching rows ⚠️ Can slow down when: • Left table is large • Many unmatched rows exist 💡 Use only when you truly need all records from the main table 🔹 RIGHT JOIN (Similar to LEFT JOIN) Same performance behavior as LEFT JOIN, just reversed. ⚠️ Often avoided in practice 👉 Developers prefer rewriting it as LEFT JOIN for clarity 🚨 Common Performance Mistakes 🔸 Joining without indexes 🔸 Joining large tables unnecessarily 🔸 Using SELECT * instead of specific columns 🔸 Missing proper WHERE conditions 🟢 Best Practices for Better Performance 🔸 Index your JOIN columns 🔸Filter data early using WHERE 🔸Avoid unnecessary JOINs 🔸Use INNER JOIN when possible 🔸Limit returned columns 📌 Real Impact Poorly optimized JOINs can: • Slow down your application • Increase server load • Cause timeouts in large systems 💡 Tip: Always check your query using EXPLAIN to understand how the database executes your JOIN. 📣 Question for You: Have you ever faced slow queries because of JOINs? How did you optimize them? #SQL #DatabaseOptimization #Performance #WebDevelopment #DataEngineering #LearningSQL

🗃️ SQL Joins — visualized. If you've ever confused a LEFT JOIN with a FULL JOIN, this one's for you. Here's a quick breakdown of all 7 essential SQL joins: ✅ INNER JOIN — only matching rows from both tables ✅ FULL JOIN — all rows from both tables ✅ FULL JOIN + WHERE NULL — only rows that don't match in either table ✅ LEFT JOIN — all rows from A, matching rows from B ✅ LEFT JOIN + WHERE NULL — only rows in A with no match in B ✅ RIGHT JOIN — all rows from B, matching rows from A ✅ RIGHT JOIN + WHERE NULL — only rows in B with no match in A Mastering JOINs is one of the highest-leverage skills in SQL. Whether you're doing data analysis, building reports, or debugging queries — knowing which JOIN to reach for saves hours.

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