Joins in mysql

In MySQL, joins are used to combine rows from two or more tables based on a related column between them. Joins help you fetch related data in a single query instead of multiple queries. There are several types of joins in MySQL:

---

1. INNER JOIN

• Returns records that have matching values in both tables.
• Syntax:

SELECT t1.column1, t2.column2
FROM table1 t1
INNER JOIN table2 t2
ON t1.common_column = t2.common_column;

• Example: Get orders with customer info:

SELECT customers.name, orders.order_id
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

---

2. LEFT JOIN (or LEFT OUTER JOIN)

• Returns all records from the left table, and the matched records from the right table. If no match, NULL is returned for the right table.
• Syntax:

SELECT t1.column1, t2.column2
FROM table1 t1
LEFT JOIN table2 t2
ON t1.common_column = t2.common_column;

• Example: Get all customers and their orders (if any):

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

---

3. RIGHT JOIN (or RIGHT OUTER JOIN)

• Returns all records from the right table, and the matched records from the left table. If no match, NULL is returned for the left table.
• Syntax:

SELECT t1.column1, t2.column2
FROM table1 t1
RIGHT JOIN table2 t2
ON t1.common_column = t2.common_column;

• Example: Get all orders and their customers (if any):

SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

---

4. FULL OUTER JOIN (Not directly supported in MySQL)

• Returns all records when there is a match in either table.
• MySQL doesn’t support it directly; you can simulate using UNION of LEFT JOIN and RIGHT JOIN:

SELECT t1.column1, t2.column2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.common_column = t2.common_column
UNION
SELECT t1.column1, t2.column2
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.common_column = t2.common_column;

---

5. CROSS JOIN

• Returns the Cartesian product of both tables (every row of table1 combined with every row of table2).
• Syntax:

SELECT t1.column1, t2.column2
FROM table1 t1
CROSS JOIN table2 t2;

• Usually used rarely because it can produce a large number of rows.

---

6. SELF JOIN

• Joining a table with itself. Useful for hierarchical data.
• Syntax:

SELECT a.column1, b.column2
FROM table a
JOIN table b ON a.common_column = b.common_column;

• Example: Employees and their managers:

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

---

💡 Tips:

• Always use aliases (t1, t2) for readability.
• Use ON to define the join condition; using WHERE instead of ON can lead to different results.