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
Rushikesh Tawale’s Post
More Relevant Posts
-
⚠️ 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
To view or add a comment, sign in
-
-
📊 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
To view or add a comment, sign in
-
-
🔥 𝗗𝗮𝘆 34 – 𝗦𝗤𝗟 𝗦𝗲𝗿𝘃𝗲𝗿 𝗝𝗼𝗶𝗻𝘀 (𝗣𝗮𝗿𝘁 2) SQL Joins are one of the most important concepts in SQL Server. They are used to combine data from multiple tables using related columns. In real-world projects, data is usually stored in separate tables such as Customers, Products, Transactions, Accounts, Employees, and Sales. ━━━━━━━━━━━━━━━━━━ 📌 𝗧𝘆𝗽𝗲𝘀 𝗼𝗳 𝗦𝗤𝗟 𝗝𝗼𝗶𝗻𝘀 1️⃣ 𝗜𝗡𝗡𝗘𝗥 𝗝𝗢𝗜𝗡 Returns only matching records from both tables. SELECT * FROM AccountMaster A INNER JOIN TransactionMaster T ON A.AccountID = T.AccountID; 2️⃣ 𝗟𝗘𝗙𝗧 𝗝𝗢𝗜𝗡 Returns all rows from the left table and matching rows from the right table. SELECT * FROM AccountMaster A LEFT JOIN TransactionMaster T ON A.AccountID = T.AccountID; 3️⃣ 𝗥𝗜𝗚𝗛𝗧 𝗝𝗢𝗜𝗡 Returns all rows from the right table and matching rows from the left table. SELECT * FROM AccountMaster A RIGHT JOIN TransactionMaster T ON A.AccountID = T.AccountID; 4️⃣ 𝗙𝗨𝗟𝗟 𝗝𝗢𝗜𝗡 Returns matching and unmatched rows from both tables. SELECT * FROM AccountMaster A FULL JOIN TransactionMaster T ON A.AccountID = T.AccountID; 5️⃣ 𝗖𝗥𝗢𝗦𝗦 𝗝𝗢𝗜𝗡 Returns every possible combination of rows. Formula: Rows in Table A × Rows in Table B Example: 6 rows × 6 rows = 36 rows SELECT * FROM AccountMaster A CROSS JOIN TransactionMaster T; ━━━━━━━━━━━━━━━━━━ 💡 𝗜𝗺𝗽𝗼𝗿𝘁𝗮𝗻𝘁 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗣𝗼𝗶𝗻𝘁𝘀 ✔ NULL does not match NULL ✔ JOIN means INNER JOIN by default ✔ CROSS JOIN does not need a common column ✔ If joining N tables, we need N - 1 join conditions ✔ Use aliases for clean queries ✔ Use table names when same column exists in multiple tables ━━━━━━━━━━━━━━━━━━ ⚠️ 𝗔𝗺𝗯𝗶𝗴𝘂𝗼𝘂𝘀 𝗖𝗼𝗹𝘂𝗺𝗻 𝗘𝗿𝗿𝗼𝗿 ❌ Wrong: SELECT AccountID FROM AccountMaster A JOIN TransactionMaster T ON A.AccountID = T.AccountID; ✅ Correct: SELECT A.AccountID FROM AccountMaster A JOIN TransactionMaster T ON A.AccountID = T.AccountID; ━━━━━━━━━━━━━━━━━━ 🚀 𝗥𝗲𝗮𝗹-𝗧𝗶𝗺𝗲 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 Customer-wise Transaction Summary SELECT A.Name, T.TransactionType, COUNT(*) AS NumberOfTransactions, SUM(T.TransactionAmount) AS TotalAmount FROM AccountMaster A JOIN TransactionMaster T ON A.AccountID = T.AccountID GROUP BY A.Name, T.TransactionType; ━━━━━━━━━━━━━━━━━━ 🎯 𝗙𝗶𝗻𝗮𝗹 𝗧𝗵𝗼𝘂𝗴𝗵𝘁 SQL Joins are the backbone of: ✔ Reporting ✔ Analytics ✔ ETL ✔ Dashboards ✔ Data Engineering 👉 Master joins and complex SQL becomes much easier. #SQL #SQLServer #SQLQueries #SQLDeveloper #SQLLearning #LearnSQL #SQLInterview #SQLTips #SQLPractice #TSQL #MicrosoftSQLServer #Database #DatabaseDeveloper #DatabaseManagement #DataAnalytics #DataAnalysis #DataAnalyst #BusinessIntelligence #BI #PowerBI #PowerBIDeveloper #Dashboard #Reporting #ETL #DataEngineering #DataEngineer #DataWarehouse #DataModeling #Joins #InnerJoin #LeftJoin #RightJoin #FullJoin #CrossJoin #TechJobs #Analytics #Coding #Programming #InterviewPreparation #CareerGrowth #LinkedInLearning Bhaskar Jogi Go Online Trainings
To view or add a comment, sign in
-
-
🧠 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
To view or add a comment, sign in
-
-
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
To view or add a comment, sign in
-
-
🚨 Most SQL developers get this wrong — and it silently breaks their data insights. COUNT(*) and COUNT(column) look almost identical. But they behave very differently. Here's what you NEED to know 👇 🔍 What's the difference? 📌 COUNT(*) → Counts ALL rows — NULLs included, no exceptions 📌 COUNT(column) → Counts only non-NULL values** in that column — NULLs are skipped Same table. Same query. Different numbers. 😱 ------------------------------------------- 🛒 Example 1 — Orders Table You have 5 orders. 2 customers had no discount code (NULL). SELECT COUNT(*) -- ✅ Returns 5 → Total orders placed SELECT COUNT(discount_code) -- ✅ Returns 3 → Orders WITH a coupon 💡 Ask yourself: do you want how many orders exist OR how many used a coupon? Two different questions → Two different answers. Don't mix them up! ---------------------------------------- 👔 Example 2 — Employees Table Your company has 4 employees. The CEO has no manager (NULL). SELECT COUNT(*) -- ✅ Returns 4 → Total headcount SELECT COUNT(manager_id) -- ✅ Returns 3 → Employees WITH a manager 💡 Using COUNT(*)here would make your org chart math look broken — because you'd be counting someone who reports to no one as if they report to someone. ----------------------------------- ✅ The golden rule: Counts NULLs? | Use when… COUNT(*) ✅ Yes --> You want total row count COUNT(col) ❌ No --> You want non-empty values only 🧠 Neither is wrong — they answer different questions. Knowing which one to reach for is what separates good SQL from great SQL. ------------------------------------ 💬 Comment— have you ever hit a bug from this? Let's swap war stories 👇 👍 Like to help this reach more data folks! #SQL #DataEngineering #Analytics #DataScience #TechTips #LearnSQL #DatabaseDevelopment
To view or add a comment, sign in
-
-
To handle 80% of real-world SQL querying, you don’t need everything—you need to master a strong core. Here’s the high-impact SQL skill stack that will cover most use cases: -------------------------------------------------------------------------------- 🔑 1. SELECT + FROM (FOUNDATION) * Pull data from tables * Choose specific columns SELECT name, age FROM users; -------------------------------------------------------------------------------- 🔍 2. WHERE (FILTERING DATA) * Filter rows using conditions * Use operators: =, !=, >, <, LIKE, IN, BETWEEN SELECT * FROM orders WHERE amount > 100; -------------------------------------------------------------------------------- 🔗 3. JOINS (MOST IMPORTANT 🔥) * Combine data from multiple tables Must know: * INNER JOIN * LEFT JOIN SELECT u.name, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id; -------------------------------------------------------------------------------- 📊 4. GROUP BY + AGGREGATIONS * Summarize data Functions to know: * COUNT() * SUM() * AVG() * MIN() * MAX() SELECT user_id, SUM(amount) AS total_spent FROM orders GROUP BY user_id; -------------------------------------------------------------------------------- 🚦 5. HAVING (FILTER AGGREGATES) * Filter grouped results SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id HAVING total > 500; -------------------------------------------------------------------------------- 🔃 6. ORDER BY (SORTING) * Sort results SELECT * FROM orders ORDER BY amount DESC; -------------------------------------------------------------------------------- 🔢 7. LIMIT (TOP RESULTS) * Get top N rows SELECT * FROM orders ORDER BY amount DESC LIMIT 10; -------------------------------------------------------------------------------- 🧠 8. CASE (CONDITIONAL LOGIC) * Add logic inside queries SELECT name, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS status FROM users; -------------------------------------------------------------------------------- 🔁 9. SUBQUERIES (BASIC) * Query inside a query SELECT name FROM users WHERE id IN ( SELECT user_id FROM orders ); -------------------------------------------------------------------------------- ⚡ 10. WINDOW FUNCTIONS (HIGH VALUE) * Advanced but extremely useful Must know: * ROW_NUMBER() * RANK() * PARTITION BY SELECT name, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank FROM orders; -------------------------------------------------------------------------------- 🎯 IF YOU LEARN ONLY THIS… You’ll handle: * Dashboards * Data analysis * ETL pipelines * Interview questions * 80% of business queries -------------------------------------------------------------------------------- 🧠 SIMPLE RULE TO REMEMBER * SELECT + WHERE → filter * JOIN → combine * GROUP BY → summarize * WINDOW → analyze deeper
To view or add a comment, sign in
-
-
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
To view or add a comment, sign in
-
-
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
To view or add a comment, sign in
-
-
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
To view or add a comment, sign in
-
Explore content categories
- Career
- Productivity
- Finance
- Soft Skills & Emotional Intelligence
- Project Management
- Education
- Technology
- Leadership
- Ecommerce
- User Experience
- Recruitment & HR
- Customer Experience
- Real Estate
- Marketing
- Sales
- Retail & Merchandising
- Science
- Supply Chain Management
- Future Of Work
- Consulting
- Writing
- Economics
- Artificial Intelligence
- Employee Experience
- Workplace Trends
- Fundraising
- Networking
- Corporate Social Responsibility
- Negotiation
- Communication
- Engineering
- Hospitality & Tourism
- Business Strategy
- Change Management
- Organizational Culture
- Design
- Innovation
- Event Planning
- Training & Development