SQL Joins
SQL Basics Course
Topic 12: SQL Joins
Learn how to combine data from multiple tables using SQL joins.
Learning Objectives
- Understand why joins are needed
- Learn INNER JOIN
- Learn LEFT JOIN
- Learn RIGHT JOIN
- Learn FULL JOIN
- Understand SELF JOIN and CROSS JOIN
Why Do We Need Joins?
Data is often stored in multiple tables.
| Students | Courses |
|---|---|
| Student_ID | Course_ID |
| Name | Course_Name |
Joins allow us to combine related data from these tables.
INNER JOIN
Returns matching records from both tables.
SELECT students.name,
courses.course_name
FROM students
INNER JOIN courses
ON students.course_id = courses.course_id;
Only matching rows are returned.
LEFT JOIN
Returns all rows from the left table and matching rows from the right table.
SELECT *
FROM students
LEFT JOIN courses
ON students.course_id = courses.course_id;
RIGHT JOIN
Returns all rows from the right table and matching rows from the left table.
SELECT *
FROM students
RIGHT JOIN courses
ON students.course_id = courses.course_id;
FULL JOIN
Returns all records from both tables whether matched or not.
SELECT *
FROM students
FULL OUTER JOIN courses
ON students.course_id = courses.course_id;
Join Comparison
| Join Type | Returns |
|---|---|
| INNER JOIN | Matching rows only |
| LEFT JOIN | All left rows + matches |
| RIGHT JOIN | All right rows + matches |
| FULL JOIN | All rows from both tables |
SELF JOIN
A table can join with itself.
SELECT A.employee_name,
B.employee_name AS manager
FROM employees A
JOIN employees B
ON A.manager_id = B.employee_id;
Useful for hierarchical data.
CROSS JOIN
SELECT *
FROM products
CROSS JOIN colors;
Returns every possible combination of rows.
Real World Example
SELECT orders.order_id,
customers.name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;
Combines order information with customer information.
Business Use Cases
- E-commerce reports
- Student enrollment systems
- Hospital management systems
- Inventory tracking
- Customer relationship management
Best Practices
- Use meaningful aliases.
- Join only required tables.
- Use indexed columns.
- Avoid unnecessary joins.
- Understand relationship structures.
Practice Exercises
- Create an INNER JOIN query.
- Create a LEFT JOIN query.
- Create a RIGHT JOIN query.
- Create a SELF JOIN example.
- Create a CROSS JOIN example.
Quiz
1. Which join returns matching rows only?
2. Which join returns all rows from the left table?
3. Which join returns all rows from both tables?
4. What is a SELF JOIN?
5. What does CROSS JOIN return?
Summary
- Joins combine data from multiple tables.
- INNER JOIN returns matching rows.
- LEFT and RIGHT JOIN preserve one table completely.
- FULL JOIN returns all records.
- SELF JOIN and CROSS JOIN solve special use cases.