MYSQL Tutorial

MySQL JOIN Operations

JOINs let you combine rows from two or more tables based on related columns. They’re essential for querying normalized databases where data is split across tables.

Common JOIN Types

• INNER JOIN: Returns rows with matching keys in both tables.
• LEFT JOIN (or LEFT OUTER): All rows from the left table, plus matched rows from the right.
• RIGHT JOIN (or RIGHT OUTER): All rows from the right table, plus matched rows from the left.
• FULL JOIN (MySQL 8.0+ via FULL OUTER JOIN): All rows when there is a match in either table.

Basic Syntax

SELECT cols
FROM table1
  [INNER | LEFT | RIGHT | FULL] JOIN table2
    ON table1.key = table2.key;
  

Example: INNER JOIN

SELECT e.first_name, e.last_name, d.department_name
FROM employees AS e
INNER JOIN departments AS d
  ON e.department_id = d.department_id;
  

Example: LEFT JOIN

SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id;
  

Example: RIGHT JOIN

SELECT p.product_name, s.stock_quantity
FROM products AS p
RIGHT JOIN stock AS s
  ON p.product_id = s.product_id;
  

Example: FULL OUTER JOIN

SELECT a.id, a.val, b.val
FROM tableA AS a
FULL OUTER JOIN tableB AS b
  ON a.id = b.id;
  

Key Tips

• Always qualify columns with table aliases to avoid ambiguity.
• Use EXPLAIN to check join performance and optimize with indexes.
• FULL OUTER JOIN may require workarounds (UNION of LEFT and RIGHT) in older MySQL versions.