**INNER JOIN:**
INNER JOIN is a type of join that returns only matched records from both tables. In other words, it only returns rows where there is a match between the common columns of both tables. The syntax for INNER JOIN is as follows:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;For example, suppose we have two tables ‘employees‘ and ‘departments‘. In this case, we may want to join the two tables to find the department for each employee. We can use INNER JOIN to accomplish this as follows:
SELECT employees.name, departments.department
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;The result of this query will be a table with two columns: ‘name‘ and ‘department‘. This table will only include records where there is a match between ‘department_id‘ in the ‘employees‘ table and ‘department_id‘ in the ‘departments‘ table.
**LEFT JOIN:**
LEFT JOIN is a type of join that returns all records from the left table and only the matching records from the right table. In other words, it returns all records from the left table and only the records from the right table that have a match with the left table. The syntax for LEFT JOIN is as follows:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;For example, suppose we have two tables ‘employees‘ and ‘salaries‘. In this case, we may want to join the two tables to find the salary for each employee. However, not all employees may have a salary record yet. We can use LEFT JOIN to accomplish this as follows:
SELECT employees.name, salaries.salary
FROM employees
LEFT JOIN salaries
ON employees.employee_id = salaries.employee_id;The result of this query will be a table with two columns: ‘name‘ and ‘salary‘. This table will include all records from the ‘employees‘ table regardless of whether there is a matching record in the ‘salaries‘ table. If there is a matching record, the ‘salary‘ column will be populated. If there is no matching record, the ‘salary‘ column will be ‘NULL‘.
**RIGHT JOIN:**
RIGHT JOIN is a type of join that returns all records from the right table and only the matching records from the left table. In other words, it returns all records from the right table and only the records from the left table that have a match with the right table. The syntax for RIGHT JOIN is as follows:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;For example, suppose we have two tables ‘salaries‘ and ‘employees‘. In this case, we may want to join the two tables to find the employee name for each salary record. However, not all salary records may have an associated employee. We can use RIGHT JOIN to accomplish this as follows:
SELECT employees.name, salaries.salary
FROM employees
RIGHT JOIN salaries
ON employees.employee_id = salaries.employee_id;The result of this query will be a table with two columns: ‘name‘ and ‘salary‘. This table will include all records from the ‘salaries‘ table regardless of whether there is a matching record in the ‘employees‘ table. If there is a matching record, the ‘name‘ column will be populated. If there is no matching record, the ‘name‘ column will be ‘NULL‘.
In summary, INNER JOIN returns only matched records, LEFT JOIN returns all records from the left table and matching records from the right table, and RIGHT JOIN returns all records from the right table and matching records from the left table.