Joining Tables

Purpose: Joins are used to combine rows from two or more tables based on a related column between them.Types of Joins:

• INNER JOIN: Returns records with matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table, returning NULL for non-matching rows.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table.
• FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table records.

Example (INNER JOIN):

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

Explanation: This query retrieves the names of employees along with their corresponding department names, joining the employees and departments tables on the department_id.