SQL Joins (INNER, LEFT, RIGHT, FULL)

Introduction

Joins in SQL allow us to combine data from multiple tables based on a related column. By using joins, we can efficiently retrieve relevant data from relational databases. SQL supports several types of joins, each serving different use cases.

Types of SQL Joins

1. INNER JOIN

An INNER JOIN returns only the matching records from both tables based on the specified condition. If there is no match, the rows are excluded from the result.

Syntax:

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

Example:

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

This query retrieves employees and their respective departments where a match exists.

2. LEFT JOIN (LEFT OUTER JOIN)

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

Syntax:

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

Example:

SELECT students.name, enrollments.course_id
FROM students
LEFT JOIN enrollments
ON students.student_id = enrollments.student_id;

This query retrieves all students, including those who haven't enrolled in any course.

3. RIGHT JOIN (RIGHT OUTER JOIN)

A RIGHT JOIN is similar to a LEFT JOIN but retrieves all records from the right table and only matching records from the left table. If there is no match, NULL values appear for columns from the left table.

Syntax:

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

Example:

SELECT employees.name, projects.project_name
FROM employees
RIGHT JOIN projects
ON employees.employee_id = projects.employee_id;

This query retrieves all projects, including those that are not assigned to any employee.

4. FULL JOIN (FULL OUTER JOIN)

A FULL JOIN returns all records when there is a match in either the left or the right table. If there is no match, NULL values appear for missing columns from either table.

Syntax:

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

Example:

SELECT students.name, courses.course_name
FROM students
FULL JOIN courses
ON students.course_id = courses.course_id;

This query retrieves all students and courses, including students who haven't enrolled in any course and courses that have no students enrolled.

Choosing the Right Join

Conclusion

Understanding SQL joins is essential for working with relational databases. By using INNER, LEFT, RIGHT, and FULL JOINs, you can efficiently retrieve related data from multiple tables based on specific conditions. Choosing the right type of join depends on the structure of your data and the results you need.