I’m writing a four part series on Joins and Applies in SQL.
Below is the T-SQL to create the two tables and a function used to explain SQL Joins and Applies.
--CREATE A TABLE VALUED FUNCTION --FUNCTION IS ONLY USED FOR CROSS AND OUTER APPLY --CREATE A TABLE VALUED FUNCTION --FUNCTION IS ONLY USED FOR CROSS AND OUTER APPLY CREATE FUNCTION [dbo].[udfGet5Rows](@inputInteger INT) RETURNS @t TABLE ([calculatedValue] INT) AS BEGIN IF @inputInteger IS NOT NULL BEGIN INSERT INTO @t([calculatedValue]) SELECT @inputInteger UNION ALL SELECT @inputInteger + 1 UNION ALL SELECT NULL UNION ALL SELECT @inputInteger + 3 UNION ALL SELECT @inputInteger + 4 END RETURN END; GO --CREATE TWO TABLES CREATE TABLE [dbo].[tblSourceIntegers] ( sysID INT NULL, sourceValue INT NULL ); GO CREATE TABLE [dbo].[tblSourceStrings] ( sysID INT NULL, sourceValue VARCHAR(16) NULL ); GO --INSERT VALUES INTO THE TABLES INSERT INTO tblSourceIntegers(sysID,sourceValue) SELECT 1, 100 UNION ALL SELECT 2, 200 UNION ALL SELECT NULL, NULL UNION ALL SELECT 4, 400 UNION ALL SELECT 5, 500; GO INSERT INTO tblSourceStrings(sysID,sourceValue) SELECT 2, 'Apple' UNION ALL SELECT 3, 'Banana' UNION ALL SELECT NULL, NULL UNION ALL SELECT 5, 'Dog' UNION ALL SELECT 6, 'Egg'; GO |
Test your results with:
SELECT * FROM dbo.udfGet5Rows(5); SELECT * FROM dbo.tblSourceIntegers; SELECT * FROM tblSourceStrings; |
Your results should be:
calculatedValue --------------- 5 6 NULL 8 9 (5 row(s) affected) sysID sourceValue ----------- ----------- 1 100 2 200 NULL NULL 4 400 5 500 (5 row(s) affected) sysID sourceValue ----------- ---------------- 2 Apple 3 Banana NULL NULL 5 Dog 6 Egg (5 row(s) affected)
