Mastering Database JOINs: Connecting Tables with Shared IDs

Most people learn SQL… But get confused the moment JOIN comes into the picture. Because JOIN is not just syntax. It’s about understanding relationships between tables. Let’s simplify it ➥ INNER JOIN : Returns only matching records from both tables. Think: “Show me what exists in both.” ➥ LEFT JOIN : Returns all records from the left table + matched records from the right table. Think: “Show me everything from left, even if right is missing.” ➥ RIGHT JOIN : Returns all records from the right table + matched records from the left table. Think: “Show me everything from right, even if left is missing.” ➥ FULL JOIN (FULL OUTER JOIN) : Returns all records from both tables. Think: “Show me everything, matched or not.” #SQL #Database #DataEngineering #BackendDevelopment #TechLearning #SoftwareEngineering

❓ Ever tried WHERE COUNT(*) > 10 and got an error? That’s because WHERE filters rows before grouping. Aggregates like COUNT(*) don’t exist yet. Enter HAVING, the clause that filters after grouping. In Lesson 29 of “From Zero to Database Hero,” you’ll learn: ✅ The difference: WHERE = filter rows, HAVING = filter groups ✅ The execution order: WHERE ➡️ GROUP BY ➡️ HAVING ➡️ ORDER BY ✅ How to find categories with more than 10 books (HAVING COUNT(*) > 10) ✅ Using HAVING with AVG, SUM, MIN, MAX, and combining it with WHERE This is a classic interview question and a must‑know for any analytics dashboard. 👉 https://lnkd.in/ekn6jAZq #FromZeroToDatabaseHero #MySQL #HAVING #SQLForBeginners #LearnSQL #DataFiltering #Aggregation #TechCareer #BackendTeam #PracticalLearning

🚀 Day 18: Mastering SQL Window Functions! ⚡ Today I moved beyond basic aggregations and unlocked one of the most powerful features in SQL: Window Functions (OVER, PARTITION BY). Unlike standard GROUP BY clauses that collapse your rows, Window Functions allow you to calculate values across a group while keeping the detail of every single record. What I achieved today: ✅ Competitive Ranking: Used DENSE_RANK() to rank products by price within their specific categories (e.g., finding the #1 most expensive electronic). ✅ Financial Tracking: Created a Running Total using SUM() OVER. This is exactly how bank statements or inventory logs are calculated! Understanding how to "partition" data is a total game-changer for building dashboards and reports. 📊 #SQL #DataAnalytics #100DaysOfCode #WindowFunctions #DataScience #PostgreSQL #TechSkills

✅ Solved a SQL problem on StrataScratch — Day 51 of my SQL Journey 💪 After 50 days on LeetCode, I’ve started focusing more on real-world, business-driven SQL problems 📊 Today’s problem was about analysing how rankings change over time — not just activity, but improvement in position. I worked on: • Aggregating total comments per country by month • Ranking countries within each month using DENSE_RANK() • Comparing rankings between December and January • Identifying countries whose rank improved What I practised: • Window functions for ranking • Time-based aggregation using DATE functions • Comparing metrics across time periods • Turning raw activity into performance insights What stood out — Growth isn’t always about higher numbers. It’s about moving ahead. A country can increase activity… Yet still fall behind others. That’s why relative performance matters more than absolute values. SQL helps uncover not just change… but meaningful change. Consistent learning, one query at a time 🚀 #SQL #StrataScratch #DataAnalytics #LearningInPublic #SQLPractice

Day 33/90 — SQL Series | Phase 2 Subquery in FROM = building a custom table mid-query. "Show only cities with more than 10 orders — sorted by order count." You cannot do this with WHERE alone because WHERE runs before GROUP BY. Solution: group first inside a subquery → then filter the grouped result. SELECT city, order_count FROM ( SELECT city, COUNT(*) AS order_count FROM orders GROUP BY city ) AS city_summary WHERE order_count > 10; The inner query creates a virtual table called city_summary. The outer query reads and filters it like a normal table. Rule: always give the inner query an alias — SQL requires it. CTEs do the exact same thing but cleaner — coming next week. #SQL #Subquery #DataAnalytics #LearnSQL #DataAnalyst

Day 26/90 — SQL Series | Week 4 "My WHERE clause is not filtering dates correctly." "My SUM is returning NULL instead of a number." 9 times out of 10 — it's a data type mismatch. CAST and CONVERT fix it. CAST('250' AS INT) → turns text into number so you can do math CAST('2024-01-15' AS DATE) → turns text into date so filters work CAST(order_id AS VARCHAR) → turns number into text for concatenation Rule: use CAST (works everywhere). Use CONVERT only when you need date formatting in SQL Server. #SQL #CastConvert #DataAnalytics #LearnSQL #DataAnalyst

🚀 Day 28 of SQL Journey – Subqueries (Part 3) Today, I explored an important classification of subqueries based on dependency: Correlated and Non-Correlated Subqueries 🔍 🔹 Correlated Subqueries These subqueries depend on the outer query and execute once for every row processed. While powerful for row-wise comparisons, they can be slower due to repeated execution. 🔹 Non-Correlated Subqueries These are independent of the outer query and execute only once. They are more efficient and ideal when a single aggregated result is sufficient. 📊 The key difference lies in execution behavior and performance impact. Choosing the right type of subquery can significantly optimize your queries. 💡 Key Insight: Use correlated subqueries for dynamic, row-level comparisons, and non-correlated subqueries for static, overall comparisons. #SQL #Learning #DataAnalytics #Database #SQLJourney #40DaysOfCode

Why NOT EXISTS is faster than LEFT JOIN (in many cases) Both queries solve the same problem: Find records that don’t have a match But internally, they behave very differently. LEFT JOIN: • Joins entire tables • Creates intermediate result • Then filters NULLs NOT EXISTS: • Checks row by row • Stops at first match • Avoids unnecessary scans LEFT JOIN = “process everything, then filter” NOT EXISTS = “check and skip early” That’s why NOT EXISTS often performs better on large datasets. But remember: Modern SQL optimizers can rewrite both — always check execution plans. Small query change. Big performance difference. #SQL #SQLTips #AdvancedSQL #QueryOptimization #Database #DataEngineering #Analytics #SQLServer #PostgreSQL #CodingTips

Day 28/90 — Phase 1 complete. 4 weeks. 28 posts. Every SQL foundation covered. Here is the full cheat sheet — save it now. Week 1 — Core queries: SELECT, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT Week 2 — JOINs: INNER, LEFT, RIGHT, FULL OUTER, SELF Week 3 — Functions: COUNT/SUM/AVG/MAX/MIN, DISTINCT, LIKE, IN, BETWEEN, String functions Week 4 — Date & NULL: GETDATE, DATEPART, DATEDIFF, IS NULL, ISNULL, COALESCE, CAST If you can write this query without help — you are interview-ready for Phase 1: SELECT city, SUM(amount) AS revenue, COUNT(*) AS orders FROM orders WHERE amount > 500 AND DATEPART(YEAR, order_date) = 2024 GROUP BY city HAVING SUM(amount) > 10000 ORDER BY revenue DESC; Phase 2 starts Monday — Subqueries, CTEs, Window Functions. The intermediate level. Follow so you don't miss it. Tag someone who needs this cheat sheet. #SQL #DataAnalytics #LearnSQL #Phase1 #DataAnalyst #SQLCheatSheet

Day 19/90 — SQL Series | Week 3: Functions "Find all customers whose name starts with R." "Find all emails from Gmail." "Find all products with 'shirt' somewhere in the name." All 3 solved with LIKE + wildcards. % → any number of characters (zero or more) _ → exactly one character WHERE name LIKE 'R%' → starts with R (Rahul, Rohan, Riya) WHERE email LIKE '%@gmail.com' → ends with @gmail.com WHERE product LIKE '%shirt%' → 'shirt' anywhere in the name WHERE code LIKE 'A_01' → A then any 1 char then 01 (e.g. AB01, AC01) LIKE is case-insensitive in SQL Server by default. Combine with NOT LIKE to exclude patterns. #SQL #LIKEOperator #DataAnalytics #LearnSQL #DataAnalyst