SQL Execution Order

This follows the standard logical processing order (predefined in SQL standards like ANSI SQL):
1. FROM/JOINs (builds initial dataset from tables).
2. WHERE (filters rows; absent here).
3. GROUP BY (groups rows, computes aggregates like SUM).
4. HAVING (filters groups using aggregates).
5. SELECT (chooses/evaluates columns, applies aliases).
6. ORDER BY (sorts final result).
7. LIMIT/OFFSET (limits rows; absent here)

What is Join?

A JOIN is a single optimized query handled by the DBMS, reducing network roundtrips and leveraging indexes.
It matches rows from different tables using a common column (like CustomerID) that shares related data, as seen in Orders and Customers tables.
Advantages:
  1. Reduces the need for multiple, separate queries to different tables, improving performance and lowering server load.

How join is internally implemented

How join is different from I doing query to table-1 storing data and then doing query to table-2?
JOINs are implemented by the database engine (e.g., via nested loops, hash joins, or merge joins). It scans indexes or tables, matches rows on the join condition, and returns combined results.
No separate handles are opened per table; the optimizer plans a single execution path, often using memory or temp storage for efficiency.

Types of Joins

Lets consider we have 2 tables(employee and department)

Employee-Table          Departments-Table
id      Name            id         dept
1       Alice           1           HR
2       Bob             2           IT
3       Charlie         4           Sale
Type Examples
INNER JOIN
Returns matching rows from both tables 		Inner join (on empid)

ID      Name               dept
1       Alice              HR
2       Bob                IT
LEFT JOIN (or LEFT OUTER JOIN)
Returns all rows from left table, matching from right; NULLs for non-matches. 		LEFT JOIN on ID=EmpID

ID      Name               dept
1       Alice              HR
2       Bob                IT
3       Charlie            null
RIGHT JOIN (or RIGHT OUTER JOIN)
Returns all rows from right table, matching from left; NULLs for non-matches. 		Right JOIN on ID=EmpID

ID      Name               dept
1       Alice              HR
2       Bob                IT
4       null               Sales
FULL OUTER JOIN
Returns all rows from both tables; NULLs for non-matches. 		FULL OUTER JOIN on ID=EmpID

ID      Name               dept
1       Alice              HR
2       Bob                IT
3       Charlie            null
4       null               Sales
CROSS JOIN
Cartesian product; all combinations without condition 		Returns 9 rows (3x3 combinations), e.g., Alice-HR, Alice-IT, Alice-Sales, etc.

Complex Joins

Inner Join

Tables Complex SQL Query 

// Create 3 tables
CREATE TABLE IF NOT EXISTS customers(custid INTEGER NOT NULL, 
name VARCHAR(50) NOT NULL UNIQUE);
INSERT INTO customers (custid, name) VALUES (1, 'B');
INSERT INTO customers (custid, name) VALUES (2, 'A');
INSERT INTO customers (custid, name) VALUES (3, 'C');

CREATE TABLE IF NOT EXISTS orders(custid INTEGER NOT NULL, 
amount INTEGER NOT NULL);
INSERT INTO orders (custid, amount) VALUES (1, 100);
INSERT INTO orders (custid, amount) VALUES (2, 200);
INSERT INTO orders (custid, amount) VALUES (3, 300);

CREATE TABLE IF NOT EXISTS lineitems(custid INTEGER NOT NULL, 
data VARCHAR(50) NOT NULL UNIQUE);
INSERT INTO lineitems (custid, data) VALUES (1, 'X');
INSERT INTO lineitems (custid, data) VALUES (2, 'Y');
INSERT INTO lineitems (custid, data) VALUES (3, 'Z');

customers            Ooders               lineItems
custid  name      custid  amount        custid  data
1       B           1       100           1     X
2       A           2       200           2     Y
3       C           3       300           3     Z
Order of execution 

SELECT c.CustID, c.Name, SUM(o.Amount) 
FROM Orders o INNER JOIN LineItems l ON (o.CustID = l.CustID)     //1
              INNER JOIN Customers c ON (o.CustID = c.CustID) 
GROUP BY c.CustID, c.Name 
HAVING SUM(o.Amount) > 100 
ORDER BY SUM(o.Amount) DESC

Shortnames: Customers=c, Orders=o, LineItems=l

This is order of execution:
1. FROM Orders o INNER JOIN LineItems l ON (o.CustID = l.CustID) 
                  INNER JOIN Customers c ON (o.CustID = c.CustID) 
This will create join of all tables
Name  custid  amount  data
B       1       100       X
A       2       200       Y
C       3       300       Z

2. GROUP BY c.CustID, c.Name. collapses identical groups into single rows

3. HAVING SUM(o.Amount) > 100.
eliminates the B
Name  custid  amount  data
A       2       200   Y
C       3       300   Z

4. SELECT c.CustID, c.Name, SUM(o.Amount) 
Name  custid  amount
A       2     200	
C       3     300	

5. ORDER BY SUM(o.Amount) DESC 
sorts the two rows: C first (300), then A (200).
Name  custid  amount
C       3     300	
A       2     200