Create an inline SQL table on the fly (for an excluding left join)

You can do this from SQL Server 2008 onwards using a table value constructor.

SELECT * FROM (
   VALUES(1, 'red'),
         (2, 'orange'),
         (5, 'yellow'),
         (10, 'green'),
         (11, 'blue'),
         (12, 'indigo'),
         (20, 'violet'))
   AS Colors(Id, Value)

More information here: Table Value Constructor

You can create an "inline table" with a UNION subquery:

(
            SELECT 10 AS id
  UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
  -- etc.
) AS inline_table

Create an inline SQL table on the fly (for an excluding left join)

Related

Recent Posts