BE - Database Management Systems

A join is used to combine rows from two or more tables based on a related column between them. Joins are categorized into several types:

1. INNER JOIN

An inner join returns only the rows that have matching values in both tables.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example:

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

This retrieves only students who are enrolled in at least one course.

2. LEFT (OUTER) JOIN

A left join returns all rows from the left table and the matched rows from the right table. If no match exists, NULL is returned for columns of the right table.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Example:

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

This retrieves all students, including those who are not enrolled in any course.

3. RIGHT (OUTER) JOIN

A right join returns all rows from the right table and the matched rows from the left table. If no match exists, NULL is returned for columns of the left table.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example:

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

This retrieves all enrollments, including those with no matching student (if any).

4. FULL (OUTER) JOIN

A full join returns all rows when there is a match in one of the tables. If there is no match, the result includes NULL for the missing side.

Syntax:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Example:

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

This retrieves all students and all enrollments, including unmatched rows from both tables.

5. CROSS JOIN

A cross join returns the Cartesian product of two tables. Every row from the first table is combined with every row from the second table.

Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

Example:

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

This pairs every student with every course, regardless of enrollment.

 

Join Type	Description
INNER JOIN	Returns only matching rows
LEFT JOIN	Returns all rows from left table + matched from right
RIGHT JOIN	Returns all rows from right table + matched from left
FULL OUTER JOIN	Returns all rows from both tables
CROSS JOIN	Returns Cartesian product of both tables