MySQL Joins

---

In MySQL, joins are used to combine rows from two or more tables based on related columns.

Joins allow you to retrieve data from multiple tables in a single query, enabling you to work with data that is distributed across different tables.

Types of joins in MySQL:

1. INNER JOIN (or JOIN):

   • The INNER JOIN returns only the rows where there is a match between the specified columns in both tables.
   • Syntax:

   SELECT columns FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

   Example:

   SELECT employees.first_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;

2. LEFT JOIN (or LEFT OUTER JOIN):

   • The LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, it returns NULL values for columns from the right table.
   • Syntax:

   SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

   Example:

   SELECT customers.customer_name, orders.order_date FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id;

3. RIGHT JOIN (or RIGHT OUTER JOIN):

   • The RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If there is no match, it returns NULL values for columns from the left table.
   • Syntax:

   SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

   Example:

   SELECT orders.order_date, customers.customer_name FROM orders RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

4. FULL OUTER JOIN (or FULL JOIN):

   • The FULL OUTER JOIN returns all rows when there is a match in either the left or right table. If there is no match, it returns NULL values for columns from the non-matching table.
   • Syntax:

   SELECT columns FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;

   Example:

   SELECT employees.first_name, departments.department_name FROM employees FULL JOIN departments ON employees.department_id = departments.department_id;

5. SELF JOIN:

   • A self-join is a join where a table is joined with itself. It is useful when you have hierarchical or self-referencing data in a single table.
   • Syntax:

   SELECT columns FROM table1 AS t1 JOIN table1 AS t2 ON t1.column_name = t2.column_name;

   Example (for an employee hierarchy):

   SELECT e1.employee_name, e2.manager_name FROM employees AS e1 JOIN employees AS e2 ON e1.manager_id = e2.employee_id;

Joins are powerful tools for combining data from multiple tables in MySQL.

Depending on your specific use case, you can choose the appropriate type of join to retrieve the desired data.

Enroll Now

• SQL
• DBMS