Notes on Recursive SQL Queries

Introduction to Recursive SQL Queries

• Complexity: Recursive SQL queries are complex concepts but can be very useful.
• Use Cases: Commonly needed in senior roles or interviews.
• Goal of the Video: Explain recursive SQL concepts simply, suitable for beginners.

Sponsor: Brilliant

• Platform: Online learning platform for math, science, and computer science.
• Features: Interactive and visually appealing courses.
• Offer: Free sign-up with a 20% discount for the first 200 users via a specific link.

Understanding Recursive SQL Queries

Syntax for Recursive Queries

1. With Clause: Start with the WITH clause followed by RECURSIVE keyword.
2. Naming: Provide a meaningful name for the clause.
3. Query Structure: Enclose your query in parentheses.
4. Two Parts:
   • Base Query: Returns initial set of data (non-recursive part).
   • Recursive Query: Uses the CTE (Common Table Expression) and must include a termination condition.
5. Execution: SQL executes the base query first, then the recursive query iteratively until the termination condition is met.

Important Considerations

• Union/Union All: Use to combine the base and recursive queries.
• Termination Condition: Essential to prevent infinite loops.

Execution Process of Recursive SQL Queries

• Iteration Process:
  1. Base query executes first (first iteration).
  2. Recursive query uses the result from the previous iteration as input.
  3. Continues until the termination condition fails.

Practical Examples of Recursive SQL Queries

Example 1: Display Numbers from 1 to 10 Without Using Inbuilt Functions

• Strategy: Use recursion to generate numbers without loops.
• Query Breakdown:
  • Base Query: SELECT 1 AS n
  • Recursive Query: SELECT n + 1 FROM numbers WHERE n < 10
  • Final output includes numbers 1 through 10.

Example 2: Find the Hierarchy of Employees Under a Given Manager

• Use Case: Given a manager (e.g., Asha), find all employees under her and their subordinates.
• Data Structure: Employee details table with id, name, manager_id, etc.
• Query Breakdown:
  • Start from the manager's record.
  • Use a recursive join to find employees under each manager.

Example 3: Find the Hierarchy of Managers for a Given Employee

• Use Case: Given an employee (e.g., David), find all their managers.
• Query Breakdown:
  • Base Query: Fetch the employee record (e.g., David).
  • Modify recursion to look for manager_id in employee details.

SQL Syntax Differences Across RDBMS

• PostgreSQL: Supports RECURSIVE, can use UNION.
• MySQL: Use UNION ALL, no RECURSIVE keyword.
• Microsoft SQL Server: Similar to MySQL but requires UNION ALL instead of UNION.
• Oracle: No RECURSIVE keyword; needs explicit column aliases for the query.

Conclusion

• Learning Resources: Dataset used in the video available on Discord.
• Encouragement: Practice solving problems and experimenting with queries for better understanding.