CROSS APPLY vs OUTER APPLY in SQL Server (with Live Examples)

SQL Server में CROSS APPLY और OUTER APPLY powerful operators हैं जो हमें Master-Detail records के साथ flexible तरीके से काम करने की सुविधा देते हैं।

मुख्य फर्क:
CROSS APPLY ➝ INNER JOIN जैसा (सिर्फ matching rows)
OUTER APPLY ➝ LEFT JOIN जैसा (non-matching rows भी दिखते हैं)

---

🧪 Example 1: OUTER APPLY – Student Exam Summary

यह example हर student को दिखाता है, चाहे उसने exam दिया हो या नहीं।

-- Step 1: Students Table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName NVARCHAR(100)
);

INSERT INTO Students VALUES
(1, 'Amit Sharma'),
(2, 'Priya Verma'),
(3, 'Rahul Meena'),
(4, 'Suman Joshi'); -- इसने कोई exam नहीं दिया

-- Step 2: ExamResults Table
CREATE TABLE ExamResults (
    ResultID INT PRIMARY KEY,
    StudentID INT,
    Subject NVARCHAR(50),
    Marks INT
);

INSERT INTO ExamResults VALUES
(1, 1, 'Math', 45),
(2, 1, 'English', 38),
(3, 2, 'Math', 72),
(4, 2, 'Science', 65),
(5, 2, 'English', 33),
(6, 3, 'Math', 20),
(7, 3, 'Science', 50);

-- Step 3: OUTER APPLY Query
SELECT 
    S.StudentName,
    ISNULL(R.TotalExams, 0) AS Total_Exams,
    ISNULL(R.Passed, 0) AS Passed_Exams,
    ISNULL(R.Failed, 0) AS Failed_Exams
FROM Students S
OUTER APPLY (
    SELECT 
        COUNT(*) AS TotalExams,
        SUM(CASE WHEN Marks >= 40 THEN 1 ELSE 0 END) AS Passed,
        SUM(CASE WHEN Marks < 40 THEN 1 ELSE 0 END) AS Failed
    FROM ExamResults E
    WHERE E.StudentID = S.StudentID
) R;

📌 Output:

StudentName	Total_Exams	Passed_Exams	Failed_Exams
Amit Sharma	2	1	1
Priya Verma	3	2	1
Rahul Meena	2	1	1
Suman Joshi	0	0	0

---

🧪 Example 2: CROSS APPLY – Latest Order Per Customer

यह example सिर्फ उन customers को दिखाता है जिन्होंने कम से कम 1 order दिया है।

-- Step 1: Customers Table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100)
);

INSERT INTO Customers VALUES
(1, 'Amit Kumar'),
(2, 'Priya Mehta'),
(3, 'Rahul Jain');

-- Step 2: Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    Amount DECIMAL(10, 2)
);

INSERT INTO Orders VALUES
(101, 1, '2025-07-01', 1500.00),
(102, 1, '2025-07-10', 1800.00),
(103, 2, '2025-07-05', 2000.00);

-- Step 3: CROSS APPLY Query
SELECT 
    C.CustomerName,
    O.OrderID,
    O.OrderDate,
    O.Amount
FROM Customers C
CROSS APPLY (
    SELECT TOP 1 OrderID, OrderDate, Amount
    FROM Orders O
    WHERE O.CustomerID = C.CustomerID
    ORDER BY OrderDate DESC
) O;

📌 Output:

CustomerName	OrderID	OrderDate	Amount
Amit Kumar	102	2025-07-10	1800.00
Priya Mehta	103	2025-07-05	2000.00

---

🧪 Example 3: CROSS APPLY – Last 3 Orders Per Product

-- Step 1: Products Table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100)
);

INSERT INTO Products VALUES
(1, 'Mobile'),
(2, 'Laptop'),
(3, 'Headphones');

-- Step 2: OrderDetails Table
CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    ProductID INT,
    OrderID INT,
    OrderDate DATE,
    Quantity INT
);

INSERT INTO OrderDetails VALUES
(1, 1, 101, '2025-07-01', 2),
(2, 1, 102, '2025-07-03', 1),
(3, 1, 103, '2025-07-08', 3),
(4, 2, 104, '2025-07-02', 1);

-- Step 3: CROSS APPLY Query
SELECT 
    P.ProductName,
    O.OrderID,
    O.OrderDate,
    O.Quantity
FROM Products P
CROSS APPLY (
    SELECT TOP 3 OrderID, OrderDate, Quantity
    FROM OrderDetails O
    WHERE O.ProductID = P.ProductID
    ORDER BY OrderDate DESC
) O;

📌 Output:

ProductName	OrderID	OrderDate	Quantity
Mobile	103	2025-07-08	3
Mobile	102	2025-07-03	1
Mobile	101	2025-07-01	2
Laptop	104	2025-07-02	1

---

📚 Summary – कब क्या Use करें?

Condition	Use
सिर्फ matching records चाहिए	CROSS APPLY
All master rows चाहिए (चाहे detail हो या नहीं)	OUTER APPLY

---

आशा है आपको CROSS APPLY और OUTER APPLY के बीच का फर्क और उनका उपयोग समझ में आया होगा।