Understanding SQL Joins — With Examples

Joins are used in SQL to combine rows from two or more tables based on a related column between them.

We’ll use two sample tables throughout:

📘 Table: students

📘 Table: courses

✅ 1. INNER JOIN (Only Matching Records)

Returns only the rows where there is a match in both tables.

SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON s.student_id = c.student_id;

🧾 Result:

student_id = 5 in courses doesn’t match any student, so it’s excluded.

✅ 2. LEFT JOIN (All from Left + Matching from Right)

Returns all rows from the left table, and matched rows from the right. Non-matches return NULL.

SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON s.student_id = c.student_id;

🧾 Result:

Students with no course still appear.

---

✅ 3. RIGHT JOIN (All from Right + Matching from Left)

Returns all rows from the right table, and matched rows from the left. Non-matches return NULL.

SELECT s.name, c.course_name
FROM students s
RIGHT JOIN courses c ON s.student_id = c.student_id;

🧾 Result:

The course with student_id = 5 has no matching student.

---

✅ 4. FULL OUTER JOIN (All from Both Tables)

Returns all records when there is a match in either left or right table. Non-matching rows return NULL.

SELECT s.name, c.course_name
FROM students s
FULL OUTER JOIN courses c ON s.student_id = c.student_id;

🧾 Result:

All rows from both tables, including non-matches.

---

✅ 5. CROSS JOIN (Cartesian Product)

Returns all combinations of rows — each row from the first table is paired with all rows from the second.

SELECT s.name, c.course_name
FROM students s
CROSS JOIN courses c;

🧾 If students has 4 rows and courses has 4 rows → Output: 16 rows

⚠️ Use with caution — grows quickly with data size!

---

✅ 6. SELF JOIN (Join a table to itself)

Used when rows in a table are related to other rows in the same table.

Example: Employee hierarchy

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

🧾 Result:

---

🧠 Quick Summary Table

What is a NATURAL JOIN in SQL?

A NATURAL JOIN is a type of join that automatically joins two tables based on all columns with the same name in both tables — without explicitly specifying the join condition.

✅ Syntax:

SELECT * 
FROM table1
NATURAL JOIN table2;

⚠️ Important Points:

• It finds columns with the same name in both tables and joins based on those.

• You don’t need to specify ON or USING.

• It’s often equivalent to:

SELECT * 
FROM table1
JOIN table2 USING (common_column_name);

SQL Aggregation and GROUP BY

📌 What is GROUP BY?

The GROUP BY clause is used to group rows that have the same values in specified columns and perform aggregate functions like SUM, COUNT, AVG, MAX, and MIN on each group.

🔢 Common Aggregate Functions in SQL

🧪 Sample Table: orders

✅ 1. Using GROUP BY with SUM()

-- Total amount spent by each customer
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

🧾 Output:

---

✅ 2. Using GROUP BY with COUNT()

-- Number of orders per customer
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

🧾 Output:

---

✅ 3. Using GROUP BY with AVG()

-- Average order amount per customer
SELECT customer_id, AVG(amount) AS avg_order
FROM orders
GROUP BY customer_id;

🧾 Output:

---

✅ 4. Using GROUP BY with MIN() and MAX()

-- Min and max order amount per customer
SELECT customer_id, MIN(amount) AS min_order, MAX(amount) AS max_order
FROM orders
GROUP BY customer_id;

🧾 Output:

---

✅ 5. Grouping by Multiple Columns

-- Total amount per customer per product
SELECT customer_id, product, SUM(amount) AS total
FROM orders
GROUP BY customer_id, product;

🧾 Output:

---

✅ 6. Using HAVING with GROUP BY

HAVING is like WHERE, but used after grouping to filter group-level results.

-- Customers who spent more than 1000 total
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

🧾 Output:

---

🔄 Difference Between WHERE and HAVING

---

🧠 Tips for Using Aggregations

• Every column in SELECT (that is not aggregated) must be in GROUP BY.

• You can use ORDER BY after GROUP BY to sort results.

• You can nest aggregate functions (e.g., ROUND(AVG(...))).