Join

 SQL JOIN ka use do tables ke records ko join karne ke liye hota hai common column ke basis par.

Aapke options hain:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

---

1️⃣ INNER JOIN (Common Records)

👉 Ye sirf matching records ko return karta hai jo dono tables me available hote hain.
🔹 Non-matching records include nahi hote.

Example

sql
SELECT PO.PurchaseOrderID, GR.GoodsReceiptID
FROM PurchaseOrder_TDI PO
INNER JOIN GoodsReceipt_TDI GR 
ON PO.PurchaseOrderID = GR.PurchaseOrderID;

Diagram

yaml
PurchaseOrder_TDI       GoodsReceipt_TDI  
+------------+         +------------+  
| PO_ID      |         | GR_ID      |  
| 1001       |---✓---->| 1001       |  
| 1002       |---✓---->| 1002       |  
| 1003       |         | 1004       |  
+------------+         +------------+  

✅ Result:

PO_ID	GR_ID
1001	1001
1002	1002

---

2️⃣ LEFT JOIN (All Left + Matching Right)

👉 Left table ke saare records + Right table ke sirf matching records.
🔹 Jo records match nahi hote unke liye NULL aayega.

Example

sql
SELECT PO.PurchaseOrderID, GR.GoodsReceiptID
FROM PurchaseOrder_TDI PO
LEFT JOIN GoodsReceipt_TDI GR 
ON PO.PurchaseOrderID = GR.PurchaseOrderID;

Diagram

yaml
PurchaseOrder_TDI       GoodsReceipt_TDI  
+------------+         +------------+  
| PO_ID      |         | GR_ID      |  
| 1001       |---✓---->| 1001       |  
| 1002       |---✓---->| 1002       |  
| 1003       |         | NULL       |  
+------------+         +------------+  

✅ Result:

PO_ID	GR_ID
1001	1001
1002	1002
1003	NULL

---

3️⃣ RIGHT JOIN (All Right + Matching Left)

👉 Right table ke saare records + Left table ke sirf matching records.
🔹 Jo match nahi hote unke liye NULL aayega.

Example

sql
SELECT PO.PurchaseOrderID, GR.GoodsReceiptID
FROM PurchaseOrder_TDI PO
RIGHT JOIN GoodsReceipt_TDI GR 
ON PO.PurchaseOrderID = GR.PurchaseOrderID;

Diagram

yaml
PurchaseOrder_TDI       GoodsReceipt_TDI  
+------------+         +------------+  
| PO_ID      |         | GR_ID      |  
| 1001       |---✓---->| 1001       |  
| 1002       |---✓---->| 1002       |  
| NULL       |         | 1004       |  
+------------+         +------------+  

✅ Result:

PO_ID	GR_ID
1001	1001
1002	1002
NULL	1004

---

4️⃣ FULL JOIN (All Left + All Right)

👉 Dono tables ke saare records milenge.
🔹 Jo match nahi hote unke liye NULL aayega.

Example

sql
SELECT PO.PurchaseOrderID, GR.GoodsReceiptID
FROM PurchaseOrder_TDI PO
FULL JOIN GoodsReceipt_TDI GR 
ON PO.PurchaseOrderID = GR.PurchaseOrderID;

Diagram

yaml
PurchaseOrder_TDI       GoodsReceipt_TDI  
+------------+         +------------+  
| PO_ID      |         | GR_ID      |  
| 1001       |---✓---->| 1001       |  
| 1002       |---✓---->| 1002       |  
| 1003       |         | NULL       |  
| NULL       |         | 1004       |  
+------------+         +------------+  

✅ Result:

PO_ID	GR_ID
1001	1001
1002	1002
1003	NULL
NULL	1004

---

Summary Table 📌

Join Type	Left Table	Right Table	Unmatched Data
INNER JOIN	✅ Matching	✅ Matching	❌ Excluded
LEFT JOIN	✅ All	✅ Matching	❌ Right Side NULL
RIGHT JOIN	✅ Matching	✅ All	❌ Left Side NULL
FULL JOIN	✅ All	✅ All	✅ NULLs for unmatched

Agar aapko sirf matching records chahiye toh INNER JOIN use karein.
Agar left table ke saare records + right ke matching chahiye toh LEFT JOIN.
Agar right table ke saare records + left ke matching chahiye toh RIGHT JOIN.
Agar dono tables ke saare records chahiye (matching + non-matching) toh FULL JOIN. 🚀

You said:

aur cross

ChatGPT said:

CROSS JOIN (Cartesian Product) Kya Hota Hai?

👉 CROSS JOIN dono tables ke har record ka combination banata hai.
🔹 Isme koi condition nahi hoti, aur sabhi records ek doosre se multiply hote hain.
🔹 Number of records = (Table 1 ke rows) × (Table 2 ke rows)

---

Example

Maan lo PurchaseOrder_TDI table me 3 rows hain aur GoodsReceipt_TDI table me 2 rows hain.
Agar hum CROSS JOIN use karein to 3 × 2 = 6 rows milengi.

sql
SELECT PO.PurchaseOrderID, GR.GoodsReceiptID
FROM PurchaseOrder_TDI PO
CROSS JOIN GoodsReceipt_TDI GR;

Diagram

yaml
PurchaseOrder_TDI       GoodsReceipt_TDI  
+------------+         +------------+  
| PO_ID      |         | GR_ID      |  
| 1001       |         | 2001       |  
| 1002       |         | 2002       |  
| 1003       |         |            |  
+------------+         +------------+  

✅ Result:

PO_ID	GR_ID
1001	2001
1001	2002
1002	2001
1002	2002
1003	2001
1003	2002

---

CROSS JOIN Kab Use Karein?

✅ Jab sabhi possible combinations chahiye ho.
✅ Jab data pairing ya permutations ka use ho.
✅ Jab default settings ya mapping data generate karna ho.

🚨 Warning: Agar tables bade hain to CROSS JOIN ka result bohot bada ho sakta hai, jo performance slow kar sakta hai.