SQL Joins and Relationships

Objective: Learn how to work with multiple tables using SQL joins.

Introduction to Primary and Foreign Keys

Primary keys and foreign keys are fundamental concepts in relational databases that establish and enforce relationships between tables.

1. Primary Key: A primary key is a unique identifier for a record in a table. It ensures that each record can be uniquely identified.

• Syntax Example:
  

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT
);

• Explanation: In this example, StudentID is the primary key, ensuring that each student has a unique identifier.
  
  

2. Foreign Key: A foreign key is a field in one table that uniquely identifies a row of another table. It creates a link between two tables.

• Syntax Example:
  

CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

• Explanation: Here, StudentID in the Enrollments table is a foreign key that references StudentID in the Students table, establishing a relationship between these tables.
  
  
  
  

Types of Joins

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

1. INNER JOIN: Returns only the matching rows from both tables.

• Syntax Example:

SELECT Students.Name, Enrollments.CourseID
FROM Students
INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

• Explanation: This query returns only the students who have enrollments. If a student isn’t enrolled in any course, they won’t appear in the result.
  
  

2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.

• Syntax Example:

SELECT Students.Name, Enrollments.CourseID
FROM Students
LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

• Explanation: This query returns all students, including those who are not enrolled in any course. For students with no enrollment, CourseID will be NULL.
  
  

3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.

• Syntax Example:

SELECT Students.Name, Enrollments.CourseID
FROM Students
RIGHT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

• Explanation: This query returns all enrollments, even if there is no corresponding student in the Students table. If there’s no match, Name will be NULL.
  
  

4. FULL OUTER JOIN: Returns all rows when there is a match in either left or right table. If there is no match, NULL values are returned for columns from the table without a match.

• Syntax Example:

SELECT Students.Name, Enrollments.CourseID
FROM Students
FULL OUTER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

• Explanation: This query returns all students and enrollments, regardless of whether they match. If a student isn’t enrolled or an enrollment has no student, the unmatched columns will be NULL.
  
  
  
  

Self-Joins and Cross Joins

1. Self-Join: A self-join is a regular join but the table is joined with itself.

• Syntax Example:

SELECT A.Name AS Student1, B.Name AS Student2
FROM Students A, Students B
WHERE A.StudentID <> B.StudentID;

• Explanation: This query pairs students with each other except with themselves, listing all possible pairs of students.
  
  

2. Cross Join: A cross join returns the Cartesian product of the two tables. It combines all rows from the first table with all rows from the second table.

• Syntax Example:

SELECT Students.Name, Courses.CourseName
FROM Students
CROSS JOIN Courses;

• Explanation: This query returns every possible combination of students and courses, regardless of whether a student is enrolled in the course.
  
  
  
  

Combining Results with UNION and UNION ALL

1. UNION: Combines the result of two or more SELECT queries but removes duplicate rows.

• Syntax Example:

SELECT Name FROM Students
UNION
SELECT Name FROM Teachers;

• Explanation: This query combines the names of students and teachers into a single list, removing any duplicates.
  
  

2. UNION ALL: Combines the result of two or more SELECT queries, including all duplicates.

• Syntax Example:

SELECT Name FROM Students
UNION ALL
SELECT Name FROM Teachers;

• Explanation: This query returns the combined names of students and teachers, keeping all duplicates.
  
  
  
  

Understanding and Using Subqueries

1. Subquery: A subquery is a query nested within another query. It can be used to filter results or calculate values used in the main query.

• Syntax Example: 

SELECT Name
FROM Students
WHERE StudentID IN (SELECT StudentID FROM Enrollments WHERE CourseID = 101);

• Explanation: This query retrieves the names of students who are enrolled in the course with CourseID = 101.
  
  
  
  

Conclusion

By the end of this lesson, you should be able to work with multiple tables using different types of SQL joins, combine results from multiple queries, and leverage subqueries to refine your data retrieval. These skills are fundamental for managing complex data relationships in a relational database.

In the next lesson, we’ll delve into advanced SQL concepts like nested queries, Common Table Expressions (CTEs), and window functions to further enhance your ability to write powerful SQL queries.