SQL Joins and Relationships: INNER, LEFT, RIGHT, and FULL JOIN

1. INNER JOIN

The INNER JOIN returns rows where there is a match in both tables. If no match is found, the row is excluded.

Syntax

Edit Copy

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

Example

Consider two tables:

• employees (id, name, department_id)
• departments (id, department_name)

Edit Copy

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

This query retrieves only those employees who have a matching department.

2. LEFT JOIN

The LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match is found, NULL values are filled in for columns from the right table.

Syntax

Edit Copy

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

Example

Edit Copy

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

This query retrieves all employees, even those who are not assigned to any department.

3. RIGHT JOIN

The RIGHT JOIN returns all rows from the right table and matching rows from the left table. If no match is found, NULL values are filled in for columns from the left table.

Syntax

Edit Copy

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

Example

Edit Copy

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

This query retrieves all departments, even if they have no employees assigned.

4. FULL JOIN

The FULL JOIN returns all rows from both tables. If there is no match, NULL values are filled in for columns where data is missing. Note: FULL JOIN is not natively supported in MySQL, but you can simulate it using a combination of LEFT JOIN and RIGHT JOIN with UNION.

Example (Simulated FULL JOIN)

Edit Copy

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
UNION
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

This query retrieves all employees and departments, filling in NULL values where there are no matches.

5. Choosing the Right Join Type

Select the join type based on the results you need:

• INNER JOIN: Use when you only need matching rows.
• LEFT JOIN: Use when you want all rows from the left table, regardless of matches.
• RIGHT JOIN: Use when you want all rows from the right table, regardless of matches.
• FULL JOIN: Use when you need all rows from both tables, even if there are no matches (simulated with UNION in MySQL).

6. Conclusion

Understanding and using different types of joins allows you to work efficiently with relational databases, enabling complex queries that combine and filter data across multiple tables.