Q.8)

SQL Joins Kya Hain?

SQL joins relational databases mein alag-alag tables se data ko combine (jodne) karne ke fundamental (sabse zaroori) tools hain.

• Example: Maan lijiye hamare paas do tables hain. Ek 'Student' table jisme student ki information aur uski ID hai, aur dusri 'Marks' table jisme har student ID ke marks hain. Ab agar humein har student ka naam uske marks ke sath dekhna hai, toh humein in dono tables ko join karna padega.
• Note: Yaad rakhiye, hum database normalization ke rules ke hisaab se anomalies (gadbadi) aur redundancies (duplicacy) ko avoid karne ke liye data ko multiple tables mein store karte hain.

Sample Tables (Examples samajhne ke liye)

Joins ko ache se samajhne ke liye hum in do sample tables ka use karenge:

Table: Student

Roll_No	Name
1	Harsh
2	Pratik
3	Riya

Table: Course

Roll_No	Course_ID
1	C1
2	C2
4	C3

---

1. SQL INNER JOIN

INNER JOIN keyword dono tables se sirf un rows (records) ko select karta hai jo condition ko satisfy karte hain. Yani yeh unhi rows ka result banayega jahan dono tables mein common field ki value same hogi (match karegi).

Note: Hum INNER JOIN ki jagah sirf JOIN bhi likh sakte hain, dono ka matlab same hota hai.

Syntax:

SELECT table1.column1, table1.column2, table2.column1... 
FROM table1 
INNER JOIN table2 
ON table1.matching_column = table2.matching_column;

Example Query:

SELECT Student.Name, Course.Course_ID 
FROM Student 
INNER JOIN Course 
ON Student.Roll_No = Course.Roll_No;

Output:
Harsh - C1
Pratik - C2
(Sirf Roll No 1 aur 2 match hue)

2. SQL LEFT JOIN

LEFT JOIN left (pehli) table ki saari rows return karta hai, aur right (dusri) table se sirf matching rows laata hai. Agar right table mein koi match nahi milta, toh un columns ke aage NULL likha aa jata hai. Ise LEFT OUTER JOIN bhi kehte hain.

Syntax:

SELECT table1.column1, table2.column1... 
FROM table1 
LEFT JOIN table2 
ON table1.matching_column = table2.matching_column;

Example Query:

SELECT Student.Name, Course.Course_ID 
FROM Student 
LEFT JOIN Course 
ON Student.Roll_No = Course.Roll_No;

Output:
Harsh - C1
Pratik - C2
Riya - NULL
(Riya left table mein hai par usne koi course nahi liya, isliye NULL aaya)

3. SQL RIGHT JOIN

RIGHT JOIN bilkul Left Join ka ulta hai. Yeh right (dusri) table ki saari rows return karta hai aur left (pehli) table se sirf matching rows laata hai. Agar left table mein koi match nahi milta, toh wahan NULL aa jata hai. Ise RIGHT OUTER JOIN bhi kehte hain.

Syntax:

SELECT table1.column1, table2.column1... 
FROM table1 
RIGHT JOIN table2 
ON table1.matching_column = table2.matching_column;

Example Query:

SELECT Student.Name, Course.Course_ID 
FROM Student 
RIGHT JOIN Course 
ON Student.Roll_No = Course.Roll_No;

Output:
Harsh - C1
Pratik - C2
NULL - C3
(Course C3 right table mein hai par kisi student ne nahi liya, isliye Name ki jagah NULL aaya)

4. SQL FULL JOIN

FULL JOIN asal mein LEFT JOIN aur RIGHT JOIN dono ka combination hai. Yeh dono tables ki saari rows ko return karta hai. Jahan koi match nahi milta, wahan apne aap NULL values aa jati hain.

Syntax:

SELECT table1.column1, table2.column1... 
FROM table1 
FULL JOIN table2 
ON table1.matching_column = table2.matching_column;

Example Query:

SELECT Student.Name, Course.Course_ID 
FROM Student 
FULL JOIN Course 
ON Student.Roll_No = Course.Roll_No;

Output:
Harsh - C1
Pratik - C2
Riya - NULL
NULL - C3
(Dono tables ka saara data aa gaya, bina match walon ke aage NULL lag gaya)

5. SQL Natural Join

Natural Join ek tarah ka INNER JOIN hi hota hai jo automatically do tables ko un columns ke base par join kar deta hai jinka naam aur data type ek jaisa (same) hota hai.

• Yeh sirf wahi rows return karta hai jahan in common columns ki values match karti hain.
• Common columns result mein sirf ek hi baar dikhte hain, bhale hi wo dono tables mein maujud hon.
• CROSS JOIN (jo saare combinations banata hai) ke opposite, Natural Join mein sirf matching values aati hain.

Example Query:

SELECT * 
FROM Student 
NATURAL JOIN Course;

Output:

Roll_No	Name	Course_ID
1	Harsh	C1
2	Pratik	C2

(Yahan automatically 'Roll_No' par join lag gaya aur Roll_No column result mein sirf ek baar aaya)