In my previous article "CROSS APPLY on SQL Server 2005", I already mentioned the definition of cross apply with proper example.
Reference link related to article "CROSS APPLY on SQL Server 2005"
In this article I am trying to discuss related to different formation of APPLY or we can say it CROSS APPLY Vs OUTER APPLY.
APPLY has two forms
1. CROSS APPLY
2. OUTER APPLY
CROSS APPLY
Think it as an INNER JOIN.
The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table-valued functions or table expression. In other words, the right table expression returns rows for left table expression match only.
OUTER APPLY
Think it as a LEFTOUTER JOIN.
The OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table-valued functions or table expression. For those rows for which there are no corresponding matches in right table-valued functions or table expression, it contains NULL values in columns of right table expression.
To understand it properly we just take an example of both CROSS APPLY and OUTER APPLY
Step-1 [ Create the Environment ]
CREATE TABLE tbl_Dep
(DepID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
DepName VARCHAR(50) NOT NULL)
GO
INSERT INTO tbl_Dep
(DepName)
VALUES ('Developemnt')
GO
INSERT INTO tbl_Dep
(DepName)
VALUES ('Management')
GO
INSERT INTO tbl_Dep
(DepName)
VALUES ('Logistic')
CREATE TABLE tbl_Emp
(EmpID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EmpName VARCHAR(50) NOT NULL,
DepID INT)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Joydeep Das',1)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Tuhin Shina',1)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Sangram Jit',1)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Bhola',1)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Sudip Das',2)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Mithun deshi',3)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Rajesh Keshri',NULL)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Sukamal Jana',NULL)
GO
-- Table Valued Function
CREATE FUNCTION dbo.fnGetDept
(@p_DepID AS INT)
RETURNS TABLE
AS
RETURN
(
SELECT DepID,
DepName
FROM tbl_Dep
WHERE DepID = @p_DepID
)
GO
SELECT * FROM tbl_Emp
GO
EmpID EmpName DepID
1 Joydeep Das 1
2 Tuhin Shina 1
3 Sangram Jit 1
4 Bhola 1
5 Sudip Das 2
6 Mithun deshi 3
7 Rajesh Keshri NULL
8 Sukamal Jana NULL
Step-2 [ CROSS APPLY ]
SELECT a.EmpID,
a.EmpName,
b.DepID,
b.DepName
FROM tbl_Emp a
CROSS APPLY dbo.fnGetDept(a.DepID)b
Output
EmpID EmpName DepID DepName
1 Joydeep Das 1 Developemnt
2 Tuhin Shina 1 Developemnt
3 Sangram Jit 1 Developemnt
4 Bhola 1 Developemnt
5 Sudip Das 2 Management
6 Mithun deshi 3 Logistic
Please look at the output as the Employee named "Rajesh Keshri" with Emp ID: 7 and "Sukamal Jana" with Emp ID 8, not appears in the list as the CROSS APPLY works like INNER JOIN.
Step-3 [ OUTER APPLY ]
SELECT a.EmpID,
a.EmpName,
b.DepID,
b.DepName
FROM tbl_Emp a
OUTER APPLY dbo.fnGetDept(a.DepID)b
GO
Output
EmpID EmpName DepID DepName
1 Joydeep Das 1 Developemnt
2 Tuhin Shina 1 Developemnt
3 Sangram Jit 1 Developemnt
4 Bhola 1 Developemnt
5 Sudip Das 2 Management
6 Mithun deshi 3 Logistic
7 Rajesh Keshri NULL NULL
8 Sukamal Jana NULL NULL
Here the employee named "Rajesh Keshri" with Emp ID: 7 and "Sukamal Jana" with Emp ID 8 appears but there Dept ID and Dep Name Contains NULL values. So it looks like LEFT OUTER JOIN.
Hope you like it.
Posted by: MR. JOYDEEP DAS
No comments:
Post a Comment