JOINS

        

                                                                 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  examples: 

📘 Example Tables

Notice:

•     Student Devi (dept_id 40) has no matching department.
• Department MECH (dept_id 50) has no students.

🔹 1. INNER JOIN

📌 Definitio

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

 

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;

 

EXACT OUTPUT:

 

 

Explanation:

If table 1 has 5 rows and table 2 has 4 rows → Result = 20 rows