Unit II – Software Process & Requirements Modeling

 

                            SQL Joins

When working with relational databases, data is often stored in multiple tables. To combine this data meaningfully, we use SQL Joins.

In this article, we will understand:

• ✅ INNER JOIN

• ✅ LEFT JOIN

• ✅ RIGHT JOIN

• ✅ CROSS JOIN

Using simple and clear examples.

📘 Example Tables

Notice:

• Student Devi (dept_id 40) has no matching department.

• Department MECH (dept_id 50) has no students.

🔹 1. INNER JOIN

📌 Definition

Returns only the matching records from both tables.

 Query:

SELECT s.student_id, s.name, d.dept_name

FROM Student s

INNER JOIN Department d

ON s.dept_id = d.dept_id;

EXACT OUTPUT:

 Explanation

Only students whose dept_id matches a department are shown.

Devi is excluded
MECH is excluded

2. LEFT JOIN

 Definition

Returns all records from the left table and matching records from the right table. If no match, NULL is returned.

Query

SELECT s.student_id, s.name, d.dept_name
FROM Student s
INNER JOIN Department d
ON s.dept_id = d.dept_id;
EXACT OUTPUT:

 Explanation

All students are displayed.
Devi has no department → NULL

3. RIGHT JOIN

Definition

Returns all records from the right table and matching records from the left table.
Query

SELECT s.student_id, s.name, d.dept_name
FROM Student s
RIGHT JOIN Department d
ON s.dept_id = d.dept_id;


EXACT OUTPUT:

Explanation

All departments are displayed.
MECH has no students → NULL

. CROSS JOIN

 Definition

Returns the Cartesian product of both tables (every row from first table combined with every row from second table).

Query

SELECT s.name, d.dept_name
FROM Student s
CROSS JOIN Department d;


Total rows = 4 students × 4 departments = 16 rows


EXACT OUTPUT: