Joins in DBMS
Subject: Computer Science Fundamentals · Language: DBMS · Level: beginner · 10 min read
Understand joins in DBMS with numbered join types, beginner-friendly table examples, result outputs, and clear explanation of how related tables are combined.
A join is used to combine data from two or more tables based on a related column. In relational databases, data is often divided into separate tables to reduce duplication. Joins help bring related data back together when we need a complete result. For example, one table may store student details and another table may store department details. Instead of repeating the department name in every student record, the student table can store a department ID. A join can then connect both tables and show student names with department names.
Joins are very important in DBMS because real databases are rarely stored as one large table. Good database design separates data, and joins make separated data useful.
Why Joins Are Needed
Suppose a Students table stores roll number, name, and department ID. A Departments table stores department ID and department name. If we want to show the student's name with the department name, we need data from both tables. A join matches rows from both tables where the related columns have the same value. In this example, the related column is department ID.
Without joins, we would either repeat data unnecessarily or manually search multiple tables. Joins allow the DBMS to combine related records correctly.
Tables Used in Examples
We will use these two small tables.
```text
Students
+---------+--------+---------+
| roll_no | name | dept_id |
+---------+--------+---------+
| 101 | Rahul | 1 |
| 102 | Priya | 2 |
| 103 | Aman | 1 |
| 104 | Neha | 4 |
+---------+--------+---------+
```
```text
Departments
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | CSE |
| 2 | IT |
| 3 | ECE |
+---------+-----------+
```
In these tables, `dept_id` is the common column. Students with department ID 1 belong to CSE, and students with department ID 2 belong to IT. Notice that Neha has department ID 4, but department ID 4 is not present in the Departments table.
Simple Join Diagram
This diagram shows the basic matching idea behind joins.
```text
Students.dept_id ---- matches with ---- Departments.dept_id
If values match, related columns from both tables can appear in one result.
``` The DBMS compares the join condition and returns rows based on the join type. Different join types decide whether unmatched rows should appear or be ignored.
Types of Joins
The main types of joins are listed below.
1. Inner join: Returns only matching rows from both tables.
2. Left join: Returns all rows from the left table and matching rows from the right table.
3. Right join: Returns all rows from the right table and matching rows from the left table.
4. Full outer join: Returns all rows from both tables, whether matched or unmatched.
5. Cross join: Returns every possible combination of rows from both tables.
6. Self join: Joins a table with itself.
A beginner should first master inner join and left join. These two appear very often in SQL practice and interviews.
Join Types Summary
This table gives a quick comparison of common join types.
| Join Type | What It Returns | Unmatched Rows Included? |
| --- | --- | --- |
| Inner Join | Only matching rows from both tables | No |
| Left Join | All left table rows plus matching right table rows | Yes, from left table |
| Right Join | All right table rows plus matching left table rows | Yes, from right table |
| Full Outer Join | All rows from both tables | Yes, from both tables |
| Cross Join | Every combination of rows | Not based on matching |
| Self Join | Rows from the same table matched with itself | Depends on condition |
This comparison is useful for revision, but the best way to understand joins is to study the output of each query.
Inner Join
An inner join returns only rows where the join condition matches in both tables.
```sql
SELECT Students.name, Departments.dept_name
FROM Students
INNER JOIN Departments
ON Students.dept_id = Departments.dept_id;
```
This query matches students with departments using `dept_id`.
```text
+--------+-----------+
| name | dept_name |
+--------+-----------+
| Rahul | CSE |
| Priya | IT |
| Aman | CSE |
+--------+-----------+
```
Neha is not shown because her `dept_id` is 4, and there is no matching department ID 4 in the Departments table. ECE is also not shown because no student has department ID 3 in the Students table. Use inner join when you only want records that exist in both related tables.
Left Join
A left join returns all rows from the left table and matching rows from the right table. If a row from the left table has no match in the right table, the right table columns show null.
```sql
SELECT Students.name, Departments.dept_name
FROM Students
LEFT JOIN Departments
ON Students.dept_id = Departments.dept_id;
```
Here, Students is the left table because it appears before `LEFT JOIN`.
```text
+--------+-----------+
| name | dept_name |
+--------+-----------+
| Rahul | CSE |
| Priya | IT |
| Aman | CSE |
| Neha | NULL |
+--------+-----------+
```
Neha is included because left join keeps all rows from the Students table. Her department name is shown as null because department ID 4 has no matching row in the Departments table. Use left join when every record from the main table should appear, even if related data is missing.
Right Join
A right join returns all rows from the right table and matching rows from the left table. If a row from the right table has no match in the left table, the left table columns show null.
```sql
SELECT Students.name, Departments.dept_name
FROM Students
RIGHT JOIN Departments
ON Students.dept_id = Departments.dept_id;
```
Here, Departments is the right table because it appears after `RIGHT JOIN`.
```text
+--------+-----------+
| name | dept_name |
+--------+-----------+
| Rahul | CSE |
| Aman | CSE |
| Priya | IT |
| NULL | ECE |
+--------+-----------+
```
ECE is included because right join keeps all rows from the Departments table. The student name is null because no student belongs to department ID 3 in the sample data. Many developers use left join more often than right join because the same result can usually be achieved by changing table order.
Full Outer Join
A full outer join returns all rows from both tables. Matching rows are combined, and unmatched rows from either side are still included with null values for missing columns.
```sql
SELECT Students.name, Departments.dept_name
FROM Students
FULL OUTER JOIN Departments
ON Students.dept_id = Departments.dept_id;
```
The result includes matched students, Neha with a missing department, and ECE with no student.
```text
+--------+-----------+
| name | dept_name |
+--------+-----------+
| Rahul | CSE |
| Aman | CSE |
| Priya | IT |
| Neha | NULL |
| NULL | ECE |
+--------+-----------+
```
Full outer join is useful when we want to find all matched and unmatched records from both sides. Not every database supports the exact `FULL OUTER JOIN` syntax, so some systems use a combination of left join and right join.
Cross Join
A cross join returns every possible combination of rows from two tables. It does not need a matching condition.
```sql
SELECT Students.name, Departments.dept_name
FROM Students
CROSS JOIN Departments;
```
If Students has 4 rows and Departments has 3 rows, the cross join returns 12 rows. This is because every student is paired with every department. Cross joins should be used carefully. On large tables, they can create a very large result because the number of rows is multiplied.
Self Join
A self join means joining a table with itself. It is used when rows in the same table are related to other rows in that table. For example, an Employees table may have `employee_id`, `employee_name`, and `manager_id`. The `manager_id` points to another employee in the same table.
```sql
SELECT e.employee_name AS employee,
m.employee_name AS manager
FROM Employees e
LEFT JOIN Employees m
ON e.manager_id = m.employee_id;
```
Here, the same Employees table is used twice with different aliases: `e` for employee and `m` for manager. Aliases make self joins readable.
ON Clause
The `ON` clause defines the condition used to match rows from both tables. In most joins, this condition compares a foreign key with a primary key.
```sql
ON Students.dept_id = Departments.dept_id
```
This condition says that a student row and a department row should be connected when their department IDs are equal. A wrong join condition can produce wrong results. For example, joining unrelated columns may create incorrect combinations that look valid but do not represent real data.
Table Aliases
Aliases give short names to tables. They make join queries shorter and easier to read.
```sql
SELECT s.name, d.dept_name
FROM Students s
INNER JOIN Departments d
ON s.dept_id = d.dept_id;
```
Here, `s` is an alias for Students and `d` is an alias for Departments. The query works the same as before, but it is cleaner. Aliases are especially useful when table names are long or when the same table is joined more than once.
Common Beginner Mistakes
A common mistake is forgetting the join condition. Without a correct condition, the result may become a cross join-like output with too many rows. Another mistake is confusing inner join and left join. Inner join removes unmatched rows, while left join keeps all rows from the left table.
Students also sometimes forget which table is left and which table is right. The left table is the table written before the join keyword, and the right table is the table written after it.
Important Points to Remember
Joins combine related data from multiple tables. The join condition usually compares a foreign key with a primary key. Inner join returns only matching rows. Left join keeps all rows from the left table. Right join keeps all rows from the right table. Full outer join keeps unmatched rows from both sides.
For interviews and real SQL practice, focus on understanding output. If you can predict which rows will appear, the join concept is clear.