INNER JOIN:

An INNER JOIN retrieves rows from both tables that satisfy the specified condition.

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

In this example, the query combines rows from the employees and departments tables where the department_id matches.

LEFT JOIN (or LEFT OUTER JOIN):

A LEFT JOIN retrieves all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.:

                                  
                                    SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;         
                                  
                                

This query retrieves all employees and their department names. If an employee does not belong to any department, the department name will be NULL.

RIGHT JOIN (or RIGHT OUTER JOIN):

A RIGHT JOIN retrieves all rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for columns from the left table.

                                
                                    SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
                                
                              

This query retrieves all departments and the names of employees belonging to each department. If a department has no employees, the employee columns will be NULL.

FULL JOIN (or FULL OUTER JOIN):

A FULL JOIN retrieves all rows when there is a match in either the left or right table. If there is no match, NULL values are returned for columns from the table with no match.

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

This query retrieves all employees and their department names, including those without a department and all departments and the names of employees belonging to each department.

Tips:

• Understand the relationships between tables to determine the appropriate JOIN type.
• Use aliases for table names to simplify queries.
• Ensure that the columns used in JOIN conditions have the same data type.