SQL Joins

In Module 2, we learned about the Relational Model and how data is often split across multiple tables to avoid repetition (data redundancy) and ensure accuracy (data integrity) using Primary Key-Foreign Key relationships. For example, student details are in a Students table, and department details are in a Departments table, linked by MajorDeptID (FK) and DeptID (PK).

When you need to get information that combines data from these different, but related, tables, you use Joins. A JOIN operation combines rows from two or more tables based on a related column between them. It's how you bring scattered pieces of related information together into one meaningful result.

INNER JOIN (or just JOIN)

● What it does: This is the most common and default type of join. An INNER JOIN creates a new result table by combining rows from two tables ONLY when there is a match in the specified common column(s) in both tables. If a row in one table doesn't have a matching row in the other table, it is not included in the result.

● Analogy: Imagine you have a list of students and a list of departments. An INNER JOIN is like finding all the students who actually have a matching department listed in the department table. Students without a major (NULL) or with a MajorDeptID that doesn't exist in the Departments table will be left out.

Syntax:

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

Example: Get the FirstName, LastName of students and the DeptName of their major department.

SELECT S.FirstName, S.LastName, D.DeptName
FROM Students AS S
INNER JOIN Departments AS D
ON S.MajorDeptID = D.DeptID;

LEFT JOIN (or LEFT OUTER JOIN)

● What it does: A LEFT JOIN returns all rows from the left table (the first table mentioned in your FROM clause) and the matching rows from the right table. If there's no match for a row in the left table in the right table, the columns from the right table will show NULL values for that row.

● Analogy: "Show me all the students, and if they have a major, show me its name. If they don't have a major, still show the student, but put NULL for the department name."

Syntax:

SELECT columns_you_want
FROM table1 -- This is the LEFT table
LEFT JOIN table2 -- This is the RIGHT table
ON table1.common_column = table2.common_column;

Example: Get all student names and their major department names (if any).

SELECT S.FirstName, S.LastName, D.DeptName
FROM Students AS S -- Students is the LEFT table
LEFT JOIN Departments AS D -- Departments is the RIGHT table
ON S.MajorDeptID = D.DeptID;

RIGHT JOIN (or RIGHT OUTER JOIN)

● What it does: A RIGHT JOIN is the opposite of a LEFT JOIN. It returns all rows from the right table (the second table mentioned in your FROM clause) and the matching rows from the left table. If there's no match for a row in the right table in the left table, the columns from the left table will show NULL values.

● Analogy: "Show me all the departments, and if they have students, show me their names. If a department has no students, still show the department, but put NULL for the student name."

Syntax:

SELECT columns_you_want
FROM table1 -- This is the LEFT table
RIGHT JOIN table2 -- This is the RIGHT table
ON table1.common_column = table2.common_column;

Example: Get all department names and any students who major in them.

SELECT S.FirstName, S.LastName, D.DeptName
FROM Students AS S -- Students is the LEFT table
RIGHT JOIN Departments AS D -- Departments is the RIGHT table
ON S.MajorDeptID = D.DeptID;

FULL JOIN (or FULL OUTER JOIN)

● What it does: A FULL JOIN returns all rows when there is a match in either the left table or the right table. It's essentially a combination of a LEFT JOIN and a RIGHT JOIN. If a row from one table has no match in the other, the corresponding columns from the non-matching table will show NULL values.

● Analogy: "Show me all students and all departments. Match them up where possible. If a student has no major, show them with a NULL department. If a department has no students, show it with NULL student info."

Syntax:

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

Example: Get all students and all departments, regardless of whether they have a match.

SELECT S.FirstName, S.LastName, D.DeptName
FROM Students AS S
FULL JOIN Departments AS D
ON S.MajorDeptID = D.DeptID;

SELF-JOIN

● What it does: Sometimes, the relationship you want to explore exists within a single table. A SELF-JOIN is when you join a table with itself. This might seem strange, but it's very useful for finding relationships between different rows of the same table. A common example is an Employees table where an EmployeeID is linked to a ManagerID (which is also an EmployeeID).

Key Technique: To perform a SELF-JOIN, you must use table aliases. You treat the same table as if it were two separate, distinct tables during the query.

Example: Assume an Employees table:

We want to list each employee and their manager's name.

SELECT E.EmpName AS EmployeeName, M.EmpName AS ManagerName
FROM Employees AS E -- Treat this as the "employee" instance of the table
INNER JOIN Employees AS M -- Treat this as the "manager" instance of the table
ON E.ManagerID = M.EmpID; -- Join where the employee's ManagerID matches the manager's EmpID

CROSS JOIN

● What it does: A CROSS JOIN creates the Cartesian Product of two tables. This means it combines every single row from the first table with every single row from the second table. No join condition (ON clause) is specified for a CROSS JOIN.

● Analogy: If you have 3 shirts and 4 pairs of pants, a CROSS JOIN would give you all 12 possible shirt-and-pants combinations.

Syntax:

SELECT columns_you_want
FROM table1
CROSS JOIN table2;

Caution: CROSS JOIN can produce extremely large result sets (number_of_rows_in_table1 * number_of_rows_in_table2). Use it with extreme caution and only when you truly need every possible combination.

Example: (Using our Students and Departments tables)

SELECT S.FirstName, D.DeptName
FROM Students AS S
CROSS JOIN Departments AS D;