Understanding SQL Joins

What's a JOIN? A JOIN in SQL is a way to combine rows from two or more tables based on a related column. Imagine you have two puzzle pieces, and you're trying to fit them together where they connect.

Types of JOINs:

Visualize tables as Venn diagrams (those circles that overlap). The area where they overlap is where they have something in common.

INNER JOIN (or just JOIN)

• Venn Diagram: Only the overlapping part of the circles.
• What It Does: Returns records that have matching values in both tables.
• Memory Tip: Think "IN"-side the overlap.

LEFT (or LEFT OUTER) JOIN:

• Venn Diagram: Everything in the left circle + the overlapping part.
• What It Does: Returns all records from the left table, and the matched records from the right table. If no match, result is NULL on the right side.
• Memory Tip: Everything to the "LEFT", plus any common bits.

RIGHT (or RIGHT OUTER) JOIN:

• Venn Diagram: Everything in the right circle + the overlapping part.
• What It Does: Returns all records from the right table, and the matched records from the left table. If no match, result is NULL on the left side.
• Memory Tip: Everything to the "RIGHT", plus any common bits. (Used less frequently than LEFT JOIN.)

FULL (or FULL OUTER) JOIN:

• Venn Diagram: Both circles entirely.
• What It Does: Returns all records when there's a match in one of the tables. So, it returns all the rows when there's a match in one of the tables.
• Memory Tip: "FULL" circle, both sides, everything.

How to Use Them?

Imagine two tables: Employees and Departments.

• Employees has: EmployeeID, EmployeeName, DepartmentID
• Departments has: DepartmentID, DepartmentName

To get each employee's name with their respective department:

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