JOINs

Estimated reading: 3 minutes 20 views

SQL joins are a fundamental concept in relational database management, used to combine data from two or more tables based on related columns. Understanding the different types of SQL joins is crucial for database querying, data analysis, and software testing. In this article, we will explore all types of joins in SQL, explain their use cases, and compare them with diagrams and examples.

Types of Joins in SQL

Join Type	Description
`INNER JOIN`	Combines rows from both tables where the join condition is met.
`LEFT JOIN`	Returns all rows from the left table and matching rows from the right table, with `NULL` for unmatched rows.
`RIGHT JOIN`	Returns all rows from the right table and matching rows from the left table, with `NULL` for unmatched rows.
`FULL OUTER JOIN`	Combines rows from both tables, returning all rows with `NULL` in unmatched rows from either table.
`CROSS JOIN`	Produces the Cartesian product of both tables, returning all possible combinations of rows.
`SELF JOIN`	Joins a table with itself, often used to find relationships within the same table.

Detailed Examples

1. INNER JOIN

An INNER JOIN returns rows with matching values in both tables.

Employees Table

emp_id	name	department_id
1	John	101
2	Alice	102
3	Bob	103

Departments Table

department_id	department_name
101	HR
102	IT
104	Marketing

Query:

				
					SELECT Employees.name, Departments.department_name
FROM Employees
INNER JOIN Departments
ON Employees.department_id = Departments.department_id;

Result:

name	department_name
John	HR
Alice	IT

2. LEFT JOIN

A LEFT JOIN returns all rows from the left table and matching rows from the right table, with NULL for unmatched rows.

Query:

				
					SELECT Employees.name, Departments.department_name
FROM Employees
LEFT JOIN Departments
ON Employees.department_id = Departments.department_id;

Result:

name	department_name
John	HR
Alice	IT
Bob	NULL

3. RIGHT JOIN

A RIGHT JOIN returns all rows from the right table and matching rows from the left table, with NULL for unmatched rows.

Query:

				
					SELECT Employees.name, Departments.department_name
FROM Employees
RIGHT JOIN Departments
ON Employees.department_id = Departments.department_id;

Result:

name	department_name
John	HR
Alice	IT
NULL	Marketing

4. FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables, with NULL in columns where there is no match.

Query:

				
					SELECT Employees.name, Departments.department_name
FROM Employees
FULL OUTER JOIN Departments
ON Employees.department_id = Departments.department_id;

Result:

name	department_name
John	HR
Alice	IT
Bob	NULL
NULL	Marketing

5. CROSS JOIN

A CROSS JOIN returns the Cartesian product of both tables, generating all possible combinations of rows.

Query:

				
					SELECT Employees.name, Departments.department_name
FROM Employees
CROSS JOIN Departments;

Result:

name	department_name
John	HR
John	IT
John	Marketing
Alice	HR
Alice	IT
Alice	Marketing
Bob	HR
Bob	IT
Bob	Marketing

6. SELF JOIN

A SELF JOIN is used to join a table with itself. It is often used to find relationships within the same table, such as employees and their managers.

Employees Table:

emp_id	name	manager_id
1	John	NULL
2	Alice	1
3	Bob	1

Query:

				
					SELECT E1.name AS Employee, E2.name AS Manager
FROM Employees E1
LEFT JOIN Employees E2
ON E1.manager_id = E2.emp_id;

Result:

Employee	Manager
John	NULL
Alice	John
Bob	John

Comparison of Joins

Join Type	Matches Only	Includes Unmatched Rows	Example Use Case
`INNER JOIN`	Yes	No	Find rows present in both tables.
`LEFT JOIN`	No	Left table only	Include all rows from one table.
`RIGHT JOIN`	No	Right table only	Include all rows from the other table.
`FULL OUTER JOIN`	No	Both tables	Combine all rows from both tables.
`CROSS JOIN`	No	All combinations	Generate Cartesian product.
`SELF JOIN`	N/A	N/A	Relate rows within the same table.

Conclusion

SQL joins are powerful tools for combining data across tables. By understanding their differences and use cases, you can write more efficient and accurate queries. Use this guide as a reference to determine which join best suits your requirements, and practice with real-world examples to solidify your understanding.

JOINs

1. INNER JOIN

2. LEFT JOIN

3. RIGHT JOIN

4. FULL OUTER JOIN

5. CROSS JOIN

6. SELF JOIN

Leave a Comment Cancel reply

CONTENTS