CROSS APPLY In Microsoft SQL Server

Penggunaan CROSS APPLY sangat membantu untuk menjoin tabel dengan relasi one to many dengan kriteria tertentu. Secara performa juga terbilang bagus, execute time cepat dan minim menggunakan resource hardware. Berikut contohnya:

SELECT UserId,
	ShiftDate,
	ShiftId,
	IsActive,
	CAST(CASE WHEN HasAttendance > 0 OR
	  HasActivityLog > 0 OR
	  HasFinding > 0 OR
	  HasFollowUp > 0 THEN 1 ELSE 0 END AS bit
	) AS HasRelation
FROM
	[GBK.OSETRACK.UM].dbo.Users AS a
CROSS APPLY
	(SELECT TOP 1 
		ShiftDate,
		ShiftId, 
		CAST(CASE WHEN ShiftDate >= DATEADD(dd, - 7, CAST(GETDATE() AS date)) THEN 1 ELSE 0 END AS bit) AS IsActive,
		CAST(CASE WHEN ShiftId IS NOT NULL THEN 1 ELSE 0 END AS bit) AS HasAttendance
	FROM
		dbo.Attendance
	WHERE
		UserId = a.UserId
	ORDER BY 
		CheckIn DESC
	) AS b
CROSS APPLY
	(SELECT TOP 1
		CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS bit) AS HasActivityLog
	FROM
		dbo.ActivityLog
	WHERE
		UserId = a.UserId
	) AS d
CROSS APPLY
	(SELECT TOP 1
		CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS bit) AS HasFinding
	FROM
		dbo.Finding
	WHERE
		FindingBy = a.UserId
	) AS e
CROSS APPLY
	(SELECT TOP 1
		CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS bit) AS HasFollowUp
	FROM
		dbo.FollowUp
	WHERE
		FollowUpBy = a.UserId
	) AS f

Untuk lebih lanjut dapat dilihat pada link berikut ini