Phases of sql query

 

1. FROM & JOIN Phase

   • The DBMS starts by looking at the FROM users u table.

   • It then performs a LEFT JOIN with borrows b:

     • For each row in users, it tries to find matching rows in borrows where u.id = b.user_id.

     • If a user has no borrows, that user still appears, but with NULL in b.*.

   👉 At this stage, we have a wide table with u.id, u.name, b.id, b.issued_date, b.status.

---

2. WHERE Filtering 

   • If you had a WHERE clause, it would be applied here, filtering individual rows before grouping.

   • Since we’re not using WHERE, all rows pass through.

---

3. GROUP BY Phase

   • Rows are grouped by u.id, u.name.

   • Each group corresponds to one user.

   • Example: If user #5 has 3 borrow rows, those rows are grouped into a single "bucket."

---

4. Aggregate Functions (COUNT)

   • For each group (each user), COUNT(b.id) is calculated.

   • Important: COUNT(b.id) counts only non-NULL values.

     • If a user has no borrows, b.id is NULL → count becomes 0.

---

5. HAVING Filtering

   • Now the engine applies HAVING COUNT(b.id) > 0.

   • This removes users with no borrowings (where the count = 0).

   • Difference from WHERE:

     • WHERE filters before grouping,

     • HAVING filters after grouping/aggregation.

---

6. SELECT Phase

   • Only the grouped columns (u.id, u.name) and the aggregated column (total_borrows) are output.

   • You cannot select columns that are not in the GROUP BY or in an aggregate.

---

7. ORDER BY Phase

   • Finally, the result set is sorted by total_borrows in descending order.

   • So the users with the most borrowings appear first.