SQL NULLIF() Function for Avoiding Division by Zero and Data Cleanup

Why Your SQL Query Returns Correct Results… But Wrong Totals This is one of the most confusing issues I’ve faced in SQL. 👉 Row-level data looks correct 👉 But total numbers don’t match expectations Example scenario: You calculate revenue per product. Each row looks fine. But when you sum everything — the total is higher than expected. What’s actually happening: Most of the time, it’s because of a JOIN issue 👉 Joining a fact table with a one-to-many table Orders table (1 row per order) Order items table (multiple rows per order) 👉 Result: data gets duplicated Now when you calculate: Revenue gets counted multiple times Totals become inflated 💡 Why this is tricky: Query runs without errors Individual rows look correct Problem only appears in aggregation How I fixed it: 👉 1. Checked join cardinality Is it 1:1 or 1:N? 👉 2. Aggregated before join Reduced duplication 👉 3. Validated totals separately Always cross-check with base table 💡 Key insight: SQL doesn’t just combine tables — it can multiply data Takeaway: If your totals are wrong: 👉 Don’t just check the calculation 👉 Check the JOIN That’s where most hidden issues are.

⚠️ BIG SQL SIN 💡 Using NOT IN or IN operator when NULL is involved. In SQL the IN operator is a shorthand for multiple OR conditions, and value = NULL is always UNKNOWN. This will usually return empty results. If you want to include NULLs, handle them explicitly. Use IS NULL condition in addition to the IN condition, or use a different approach that accounts for NULLs. 🔑 Always test your queries with NULLs to ensure they behave as expected. 📙 SQL Essentials for Data Analysis is now available https://lnkd.in/erNbWQJi

‼️ SQL Order of Execution - Extended Version We all learn this at some point: > SQL doesn't execute in the order we write it. We write: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY But SQL actually runs: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY But this is just the basic version. In real queries, there's more happening under the hood : ✏️ A more complete execution flow looks like this: ▪️ FROM / JOIN ▪️ WHERE ▪️ GROUP BY ▪️ HAVING ▪️ WINDOW FUNCTIONS ▪️ SELECT ▪️ DISTINCT ▪️ ORDER BY ▪️ LIMIT / OFFSET Now this explains a lot of "weird" SQL behavior : 📌JOIN happens first This is where duplicates often start 📌WHERE runs before aggregation You can't use SUM/COUNT here 📌GROUP BY creates aggregated data You're no longer working with raw rows 📌WINDOW FUNCTIONS run after grouping But before final selection. That's why functions like ROW_NUMBER(), RANK() behave differently 📌SELECT happens later than you think Aliases don't exist in WHERE 📌DISTINCT runs after SELECT Removes duplicates from final output 📌ORDER BY runs near the end Can use aliases 📌LIMIT is the final step Just trims the result Why this matters: • Explains unexpected duplicates • Helps debug query errors faster • Makes window functions easier to understand • Prevents misuse of DISTINCT as a "quick fix" 💡 The real shift: SQL is not: ▪️ "Write - Execute" It's: ▪️ Build - Filter - Transform - Analyze - Show #linkedinforcreators #linkedincreators

Small SQL changes that made a noticeable difference Over time, I’ve noticed that performance issues are not always about complex tuning. Sometimes, small changes in how we write SQL make a big impact. Here are a few simple ones I’ve come across 👇 🔹 1️⃣ Avoid functions on indexed columns WHERE TO_CHAR(order_date,'YYYY-MM-DD') = '2024-01-01' 👉 Prevents index usage ✔ Better: WHERE order_date = DATE '2024-01-01' 🔹 2️⃣ NVL can affect performance WHERE NVL(status,'X') = 'A' 👉 Index may not be used ✔ Better: WHERE status = 'A' OR status IS NULL 🔹 3️⃣ Avoid SELECT * SELECT * FROM orders WHERE status = 'COMPLETE'; 👉 Fetches unnecessary data ✔ Better: SELECT order_id, order_date, amount FROM orders WHERE status = 'COMPLETE'; 🔹 4️⃣ NOT IN vs NOT EXISTS WHERE emp_id NOT IN (SELECT emp_id FROM terminated_employees) 👉 Fails if NULL exists ✔ Better: WHERE NOT EXISTS ( SELECT 1 FROM terminated_employees t WHERE t.emp_id = e.emp_id ) 💡 What I’ve learned Many performance improvements come from writing SQL in a way the optimizer can understand better — not just adding hints or indexes. Have you seen similar small changes make a difference? #OracleSQL #SQLTuning #Performance #DatabaseDevelopment #PLSQL

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

Sometimes the simplest SQL concepts are the ones that confuse you the longest. For me it was WHERE vs HAVING. I used to treat them like they were doing the same thing… after all, it’s all filtering. Until it clicked that they don’t just solve the problem in different ways… they operate at different stages of the query. WHERE is like filtering before anything happens. You’re saying: “I don’t even want this data in my view.” HAVING is after everything is grouped. You’re saying: “Now that I’ve summarized this, filter the result.” Same idea… different stage. Because WHERE reduces the data before grouping even happens, while HAVING works after the data has already been grouped and processed. Here’s how I put it simply: WHERE filters before aggregation happens. HAVING filters after aggregation happens. And honestly, once you see it like that, it stops feeling like SQL theory… and starts feeling like logic. Have you ever mixed both up before it finally clicked?

📌 This SQL query looks 100% correct… but returns ZERO rows No error. No warning. Just empty results. At first glance, nothing looked wrong 👇 SELECT CustomerID FROM Customers WHERE CustomerID NOT IN ( SELECT CustomerID FROM Orders ); Everything checked out: ✔ Data ✔ Logic ✔ Joins Still… EMPTY result. 🔍 The hidden culprit => NULL Just one NULL in the subquery can break everything. What SQL actually does internally Subquery returns: (2, 3, NULL) SQL interprets this as: CustomerID <> 2 AND CustomerID <> 3 AND CustomerID <> NULL Now here’s the catch 👇 1 <> NULL → UNKNOWN So the condition becomes: TRUE AND TRUE AND UNKNOWN = UNKNOWN And SQL behaves like this: TRUE → keep FALSE → remove UNKNOWN → also remove 💥 Result → EMPTY DATASET Even when valid rows exist. And that’s how one NULL can silently invalidate your entire dataset. ✅ The safer approach → NOT EXISTS SELECT c.CustomerID FROM Customers c WHERE NOT EXISTS ( SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID ); ✔ Works row-by-row ✔ Stop early (better performance) ✔ NULL doesn’t break logic 🔥 Learning NOT IN doesn’t fail loudly… it fails silently. 💡 Rule to follow: - Default → NOT EXISTS - Use NOT IN → only when you're 100% sure NO NULLs exist #SQL #SQLServer #DataAnalytics

Working with data? Then you can’t ignore SQL Date Functions Dates are everywhere, from transactions to user activity, and knowing how to manipulate them is a must-have skill. Here are a few essential date functions every SQL professional should know: • GETDATE() / CURRENT_TIMESTAMP → Get the current date and time • DATEADD() → Add or subtract time (days, months, years) • DATEDIFF() → Find the difference between two dates • DATEPART() → Extract specific parts of a date (year, month, day, hour) • DATENAME() → Get the name of a date part such as Monday or January Example using DATENAME(): ```sql SELECT datetime_req, DATENAME(MONTH, datetime_req) AS MonthName, DATENAME(WEEKDAY, datetime_req) AS DayName FROM your_table; ``` Why does this matter? Because real-world problems often sound like: • “Show transactions from the last 7 days” • “Get users active this month” • “Break down activity by day, month, or year” If you can handle dates well, you move from writing queries to solving business problems. Keep learning. Keep building. #SQL #DataAnalytics #TechSkills #Learning #Database

I used to ignore indexes in my queries and suffered slow results. Now I add them strategically for faster insights. Slow SQL queries waste hours of analyst time every week and kill productivity. We run ad-hoc queries daily and need results fast to keep business decisions moving. • 🔍 Column order matters Multi-column indexes process columns left to right so order impacts speed significantly. Place your most selective column first to reduce scanned rows immediately. • ⚠️ Over-indexing slows writes Extra indexes make inserts and updates slower because the database maintains each one. Only create indexes for columns frequently filtered in critical read-heavy queries. • 🔄 Covering indexes save time Include all needed columns in the index to avoid table lookups entirely. Queries run faster by reading only the index structure, not the full table. • 🚫 Avoid low-cardinality columns Indexing columns like gender or status rarely helps due to few unique values. Skip these unless your table is massive and queries target specific patterns. Before: SELECT * FROM logs WHERE user_id = 123 After: CREATE INDEX idx_user ON logs (user_id); SELECT * FROM logs WHERE user_id = 123 Apply this: Always check the execution plan after adding an index to confirm the database actually uses it. What's the most common indexing mistake you see analysts make when writing SQL queries?

🚀 SQL Series – Day 2: String Functions Handling text data in SQL? String functions make it easy! 👉 These functions help to modify and format text data 🔹 Common String Functions: • UPPER() → Convert text to uppercase • LOWER() → Convert text to lowercase • INITCAP() → First letter capital • LENGTH() → Count characters • SUBSTR() → Extract part of text • CONCAT() → Combine two strings 📊 Example Insight: • "john" → JOHN (UPPER) • "SQL" → sql (LOWER) • "kalai" → Kalai (INITCAP) • "Database" → 8 characters (LENGTH) • "BANKING" → "BANK" (SUBSTR) • "Data" + "Base" → Database (CONCAT) 💡 Simple understanding: 👉 “Text data → Format & transform easily” 💡 Where we use this? • Data cleaning • Reports • User data formatting #SQL #StringFunctions #DataAnalytics #Database #TechSimplified #ITIndustry #ProfessionalGrowth Follow JACOB JEYAKUMAR S For more updates 🔥