Cross Join

Recently met with a problem, where in I need to lookup a table using LINQ query where the JOIN clause is not supporting non-equi joins.  The Cross Join came as handy and helped.  Here is the sample SQL query which unit tested the same.

 

DROP TABLE BucketList
GO
CREATE TABLE BucketList
(
    StartNum INT,
    EndNum INT,
    Bucket VARCHAR(20)
)

INSERT INTO BucketList values
( 1,10,'[01-10]')
,( 11,20,'[11-20]')
,( 21,30,'[21-30]')
,( 31,40,'[31-40]')

drop TABLE #T1
go
CREATE TABLE #T1
(
    IP INT,
    Label VARCHAR(40)
)

INSERT INTO #T1 VALUES
(4,'Four')
,(9,'Nine')
,(34,'Thirty four')
,(44,'FortyFour')
GO

SELECT StartNum, EndNum, Label, Bucket
FROM #T1
CROSS JOIN BucketList
WHERE IP >= StartNum AND IP <= EndNum