20
SQL Concept Differences
Every Data Professional
Must Know
Ekta Joshi
1. WHERE vs HAVING
WHERE → Filters rows before aggregation
HAVING → Filters aggregated data
SELECT dept, COUNT(*)FROM employees
WHERE salary > 50000 GROUP BY dept
HAVING COUNT(*) > 5;
📌 Interview Tip:
👉 Use WHERE first, HAVING only with aggregates
2. DELETE vs TRUNCATE
DELETE → Row-level, rollback
TRUNCATE → Fast, table-level
DELETE FROM logs WHERE log_date < '2023-01-01';
📌 Key Question: Which one is safer? → DELETE
3. INNER JOIN vs LEFT JOIN
INNER JOIN → Only matching records
LEFT JOIN → Keeps unmatched rows
SELECT * FROM orders o
LEFT JOIN customers c
ON o.customer_id = c.customer_id;
📌 Asked when: missing customer / null analysis
4. UNION vs UNION ALL
UNION → Removes duplicates
UNION ALL → Faster, keeps duplicates
SELECT user_id FROM jan_users
UNION ALL
SELECT user_id FROM feb_users;
📌 Commonly asked to evaluate efficiency on large
datasets
5. COUNT(*) vs COUNT(column)
COUNT(*) → Includes NULL
COUNT(column) → Excludes NULL
SELECT COUNT(*) FROM employees;
SELECT COUNT(email) FROM employees;
📌 Frequently Asked
6. PRIMARY KEY vs UNIQUE KEY
PRIMARY KEY → Unique + NOT NULL
UNIQUE KEY → Allows NULL
user_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
📌 Follow-up: One PK, multiple UNIQUE
7. SUBQUERY vs CTE
Subquery → Complex and harder to read
CTE → Clear, structured, and reusable
WITH avg_sal AS (
SELECT AVG(salary) avg_salary FROM employees
)
SELECT * FROM employees
WHERE salary > (SELECT avg_salary FROM avg_sal);
📌 Modern SQL preference → CTE
8. RANK vs DENSE_RANK
RANK → Skips numbers
DENSE_RANK → No gaps
SELECT name,
DENSE_RANK() OVER(ORDER BY salary DESC)
FROM employees;
📌 Often Tested in Top-N Questions
9. ROW_NUMBER vs RANK
ROW_NUMBER → Unique sequence
RANK → Same rank for ties
ROW_NUMBER() OVER(PARTITION BY dept
ORDER BY salary DESC)
📌 Used to find highest salary per department
10. EXISTS vs IN
EXISTS → Faster for large data
IN → Simple but slower
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
);
📌 Performance-based question
11. INDEX vs PRIMARY KEY
PRIMARY KEY → Uniquely identifies each row in a table
INDEX → Improves query speed
📌 Simple way to remember:
Primary Key = Identity
Index = Speed booster 🚀
12. VIEW vs TABLE
TABLE → Stores actual data
VIEW → Stores only a SQL query
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'Active';
📌 Security & abstraction use case
13. CHAR vs VARCHAR
CHAR → Fixed
VARCHAR → Dynamic
status CHAR(1),
email VARCHAR(100)
📌 Storage optimization question
14. NORMALIZATION vs DENORMALIZATION
Normalization → Reduce redundancy
Denormalization → Faster analytics
📌 OLTP → Normalized
📌 OLAP → Denormalized
15. OLTP vs OLAP
OLTP → High volume of inserts & updates
OLAP → Optimized for reads, reporting & aggregations
📌 Commonly Asked in Data Interviews
16. CROSS JOIN vs INNER JOIN
CROSS JOIN → All combinations
INNER JOIN → Matching rows
SELECT * FROM products CROSS JOIN regions;
📌 Used in matrix/report generation
17. COALESCE vs ISNULL
COALESCE → ANSI standard SQL (cross-database)
ISNULL → SQL Server specific
SELECT COALESCE(phone, 'NA') FROM customers;
📌 Commonly Asked in Data Interviews
18. FUNCTION vs STORED PROCEDURE
Function → Returns a value
Stored Procedure → Executes business logic
📌 Functions can be used inside SELECT statements
19. WINDOW FUNCTION vs GROUP BY
WINDOW FUNCTION → Keeps row-level detail while adding
calculations
GROUP BY → Aggregates data and reduces rows
SELECT name, salary,
AVG(salary) OVER() AS avg_salary
FROM employees;
20. ON vs WHERE in JOIN
ON → Defines how tables are joined
WHERE → Filters rows after the join
SELECT *
FROM orders o
LEFT JOIN customers c
ON o.customer_id = c.customer_id
WHERE [Link] = 'India';
🔖 Save this for your next SQL interview
🔄 Share with someone preparing for data roles
🚀 Follow to master SQL concepts
Ekta Joshi