? MySQL JOINS

? Quick Overview

Joins let you combine rows from two or more tables based on related columns. They control which rows match and which are included when tables are combined.

? Key Concepts

INNER JOIN — only matching rows from both tables.
LEFT JOIN — all rows from left table + matched rows from right; unmatched right columns = NULL.
RIGHT JOIN — all rows from right table + matched rows from left; unmatched left columns = NULL.
FULL OUTER JOIN — all rows from both tables; unmatched side columns = NULL (not supported in all MySQL versions).

? Syntax / Theory

? View Code Example

-- Syntax examples for common JOIN types
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

? View Code Example

-- LEFT JOIN returns all rows from table1 and matched rows from table2
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

? View Code Example

-- RIGHT JOIN returns all rows 
from table2 and matched rows from table1
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

? View Code Example

-- FULL OUTER JOIN returns all rows from both sides
(MySQL may not support directly)
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

? Examples

? View Code Example

-- INNER JOIN example: students enrolled in courses (only matches)
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses
ON students.course_id = courses.course_id;

? View Code Example

-- LEFT JOIN example: employees with their departments,
include employees without departments
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

? View Code Example

-- RIGHT JOIN example: include all customers even if they have no orders (orders may be NULL)
SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.customer_id;

? View Code Example

-- FULL JOIN example (if supported): show all orders and customers, matched where possible
SELECT orders.order_id, customers.customer_name
FROM orders
FULL OUTER JOIN customers
ON orders.customer_id = customers.customer_id;

? Explanation

INNER JOIN: Returns records with matches in both tables.
LEFT JOIN: All rows from left table; matched rows from right or NULL.
RIGHT JOIN: All rows from right table; matched rows from left or NULL.
FULL JOIN: All rows from both tables; unmatched side columns NULL (useful for union-style comparisons).

? Live Output / Explanation

Example: INNER JOIN (conceptual output)

students table

-- id | name | course_id
1 | Alice | 10
2 | Bob   | 20
3 | Carol | 10

courses table

-- course_id | course_name
10 | Math
20 | Physics
30 | History

INNER JOIN result (students + courses where course_id matches):

-- name  | course_name
Alice | Math
Bob   | Physics
Carol | Math

✅ Tips & Best Practices

Use INNER JOIN when you require matched rows only.
Prefer LEFT JOIN to preserve primary table rows while adding related data.
Index the columns used in JOIN conditions for better performance.
Be explicit with table aliases to keep queries readable in multi-join scenarios.
When FULL JOIN is unavailable, simulate with UNION of LEFT and RIGHT joins where needed.

? Try It Yourself / Practice Tasks

Write a query to find all orders along with the customer names, including orders without customers (use a suitable join).
List courses and enrolled students using INNER JOIN.
Display all products with their order quantities, including products that have never been ordered (use LEFT JOIN).
Simulate a FULL OUTER JOIN in MySQL using UNION of LEFT JOIN and RIGHT JOIN.