Post #2458

✅SQL Subqueries with Interview Q&A🔍🧠 Subqueries and CTEs help you write cleaner, modular, and more powerful SQL queries. They're often asked in interviews! 1️⃣ Subqueries (Nested Queries) A query inside another query. Example: SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); 📌 Use case: Find employees earning above average. Types: ⦁ In SELECT ⦁ In WHERE ⦁ In FROM (Inline Views) 2️⃣ Correlated Subqueries Inner query depends on outer query. Example: SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id); 📌 Use case: Find employees earning above average in their department. 3️⃣ Common Table Expressions (CTE) Temporary result set using WITH. Improves readability. Example: WITH high_paid AS ( SELECT name, salary FROM employees WHERE salary > 100000 ) SELECT * FROM high_paid; 📌 Use case: Simplify complex queries, recursive queries. 4️⃣ Recursive CTE Used for hierarchical data (e.g. org charts, folders). Example: WITH RECURSIVE emp_tree AS ( SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN emp_tree et ON e.manager_id = et.id ) SELECT * FROM emp_tree; 🧠Interview Questions Q1: When should you use a subquery vs JOIN? A: Use subquery when working with aggregates or filtering logic. JOINs are better for combining related data. Q2: What's the difference between subquery and CTE? A: Subquery is inline; CTE improves readability and can be reused in the query. Q3: What is a correlated subquery? A: It depends on data from the outer query. Runs row by row. Q4: When do you use recursive CTEs? A: For hierarchical/parent-child relationships like org charts, file systems. Q5: Can subqueries be used in the FROM clause? A: Yes, they're called derived tables or inline views. 💬Double Tap ❤️ for more!