MYSQL JOIN ...
Here's a list of common types of joins used in relational databases, along with explanations and examples for each:
1. Inner Join:
- Use: Retrieves only the matching records from both tables.
- Example:
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
2. Left Join (or Left Outer Join):
- Use: Retrieves all records from the left table and matching records from the right table. If there's no match, null values are returned for the right table's columns.
- Example:
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
3. Right Join (or Right Outer Join):
- Use: Retrieves all records from the right table and matching records from the left table. If there's no match, null values are returned for the left table's columns.
- Example:
SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;
4. Full Outer Join (or Full Join):
- Use: Retrieves all records from both tables, including matching and non-matching records. If there's no match, null values are returned for the missing data.
- Example:
SELECT customers.customer_name, orders.order_id
FROM customers
FULL JOIN orders ON customers.customer_id = orders.customer_id;
5. Self Join:
- Use: Used to join a table with itself, typically to compare rows within the same table.
- Example:
SELECT e1.employee_name, e2.employee_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;
6. Cross Join (or Cartesian Join):
- Use: Generates a Cartesian product of two tables, resulting in all possible combinations of rows from both tables.
- Example:
SELECT products.product_name, categories.category_name
FROM products
CROSS JOIN categories;