Joins in SQL for Beginners (with sql query)

 SQL joins are powerful tools that allow you to combine data from multiple tables, providing a more comprehensive view of the data. Let's explore different types of joins using two sample tables and see how each join works.

1. Introduction to Joins

• Joins in SQL are used to combine rows from two or more tables based on a related column.

• Joins augment data by providing additional information from another table.

2. Sample Tables

Let's consider two tables: Employees and Departments.

Employees Table:

EmployeeID	-EmployeeName	-DepartmentID
1	Alice	101
2	Bob	102
3	Charlie	103
4	David	NULL

Departments Table:

DepartmentID	DepartmentName
101	HR
102	IT
104	Finance

3. Types of Joins

3.1. Inner Join

• An Inner Join returns only the rows where there is a match in both tables.

Query:

sql

SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Output:

EmployeeID	-EmployeeName	-DepartmentName
1	Alice	HR
2	Bob	IT

Diagram:

+--------------+     +------------------+
| Employees    |     | Departments      |
|--------------|     |------------------|
| EmployeeID   |     | DepartmentID     |
| EmployeeName |     | DepartmentName   |
| DepartmentID |---->| DepartmentID     |
+--------------+     +------------------+

3.2. Left Join

• A Left Join returns all rows from the left table, and the matched rows from the right table. Unmatched rows will show NULL.

Query:

sql

SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Output:

EmployeeID	-EmployeeName	-DepartmentName
1	Alice	HR
2	Bob	IT
3	Charlie	NULL
4	David	NULL

Diagram:

+--------------+     +------------------+
| Employees    |     | Departments      |
|--------------|     |------------------|
| EmployeeID   |     | DepartmentID     |
| EmployeeName |     | DepartmentName   |
| DepartmentID |---->| DepartmentID     |
| EmployeeID   |     | NULL (if no match)|
+--------------+     +------------------+

3.3. Right Join

• A Right Join returns all rows from the right table, and the matched rows from the left table. Unmatched rows will show NULL.

Query:

sql

SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Output:

EmployeeID	-EmployeeName	-DepartmentName
1	Alice	HR
2	Bob	IT
NULL	NULL	Finance

Diagram:

+--------------+     +------------------+
| Employees    |     | Departments      |
|--------------|     |------------------|
| EmployeeID   |     | DepartmentID     |
| EmployeeName |     | DepartmentName   |
| DepartmentID |---->| DepartmentID     |
| NULL (if no match) | DepartmentName   |
+--------------+     +------------------+

3.4. Full Outer Join

• A Full Outer Join returns all rows when there is a match in either table. If there is no match, the result is NULL on the side where there is no match.

Query:

sql

SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Output:

EmployeeID	-EmployeeName	-DepartmentName
1	Alice	HR
2	Bob	IT
3	Charlie	NULL
4	David	NULL
NULL	NULL	Finance

Diagram:

+--------------+     +------------------+
| Employees    |     | Departments      |
|--------------|     |------------------|
| EmployeeID   |     | DepartmentID     |
| EmployeeName |     | DepartmentName   |
| DepartmentID |---->| DepartmentID     |
| NULL (if no match) | DepartmentName   |
+--------------+     | NULL (if no match)|
                     +------------------+

4. How SQL Joins are Simple Lookups

• SQL joins can be thought of as simple lookups to merge related data from different tables.

• They allow you to augment your main dataset with additional information from other tables based on a common key.

5. Summary of Differences

• Inner Join: Only matched rows from both tables.

• Left Join: All rows from the left table and matched rows from the right table.

• Right Join: All rows from the right table and matched rows from the left table.

• Full Outer Join: All rows from both tables, with NULLs for unmatched rows.

Support Our Efforts and Earn Together 🚀

Visit https://parucodes.github.io/ today and start your journey to becoming a fast, accurate, and confident touch typist.

If you find our website useful and want to support us, consider joining the exciting world of Bitcoin mining on your mobile phone. Follow this link: Mine PI Bitcoin and use my username prarthanadp as your invitation code. With the referral code prarthanadp, you'll receive a special referral bonus.

Thank you for your support! Let's grow and earn together! 🌟