In MySQL, joins are used to combine rows from two or more tables based on related columns. Joins are essential for querying data from multiple tables simultaneously. There are several types of joins in MySQL:
The INNER JOIN keyword selects records that have matching values 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;
This query retrieves the first names of employees along with their corresponding department names.
The LEFT JOIN keyword 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.
Syntax:
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
Example:
SELECT employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
This query retrieves the first names of all employees, along with their corresponding department names if they belong to any department. If an employee does not belong to any department, the department name will be NULL.
The RIGHT JOIN keyword 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.
Syntax:
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Example:
SELECT employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
This query retrieves the first names of employees who belong to any department, along with their corresponding department names. If a department has no employees, the employee's first name will be NULL.
The FULL JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records.
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;
This query retrieves the first names of all employees, along with their corresponding department names. If an employee does not belong to any department or a department has no employees, the respective columns will contain NULL values.
Joins are fundamental in SQL for fetching data from multiple related tables and combining them as needed for analysis or reporting purposes.