MySQL Joins
Joins in MySQL are used to combine rows from two or more tables based on a related column between them. There are different types of joins:
1. INNER JOIN
Returns records that have matching values in both tables.
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;This query retrieves the order ID and customer name for orders placed by customers.
2. LEFT JOIN
Returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;This query retrieves customer names along with their order IDs, including customers who have not placed any orders.
3. RIGHT JOIN
Returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side if there is no match.
SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;This query retrieves customer names along with their order IDs, including orders placed by customers who are not in the customer table.
4. FULL JOIN
Returns all records when there is a match in either the left or right table. The result is NULL if there is no match.
SELECT customers.customer_name, orders.order_id
FROM customers
FULL JOIN orders ON customers.customer_id = orders.customer_id;This query retrieves customer names along with their order IDs, including all customers and orders.
MySQL joins are powerful for combining data from multiple tables based on related columns.