2

I have a table A which needs to join table B.

table B is a constant which 2 columns

year, flag
----------
2010, A1
2011, A2
2012, A3
2013, A4
2014, A5

is it possible to do something like below (* invalid syntax in sql server, just use it to show what I mean):

SELECT *
FROM A RIGHT JOIN (
   2010, 'A1';
   2011, 'A2';
   2012, 'A3';
   2013, 'A4';
   2014, 'A5';
)
B ON A.year = B.year

I do not want to use table variable or temp table for B. Is there other choices?

Thanks

urlreader
  • 6,319
  • 7
  • 57
  • 91
  • 1
    Why not create a real table? And why would you not want to use a table variable or temp table? – HLGEM Dec 18 '13 at 18:43
  • It is because this is part of a Stored Procedure. we need to join this query again to some other queries. If there is something like this exists in sql server, then we can write 1 query, let sql optimize it. If use table variable, seems slow down it. – urlreader Dec 18 '13 at 18:48
  • CTE (Comman Table Expression) is your friend :) – M.Ali Dec 18 '13 at 18:57

1 Answers1

6

Yes you can use VALUES to create a table of literal values in the plan

SELECT *
FROM   A
       RIGHT JOIN ( VALUES(2010, 'A1'),
                          (2011, 'A2'),
                          (2012, 'A3'),
                          (2013, 'A4'),
                          (2014, 'A5') ) B(year, flag)
         ON A.year = B.year

This table is not indexed however and is limited to a max 1000 rows.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • +1 for the new information didnt know this.but is there any particular situation where you have to do it ??? – M.Ali Dec 18 '13 at 18:55
  • This will work but you are better off having this in a real table in the database, so that in 2015 you can add a value and all the places this is used will automatically include it. That way you don't have to look for all the refences in your code. That is one of teh purposes of storing information in tables. – HLGEM Dec 18 '13 at 18:55
  • 2
    @MuhammedAli - It is just syntactic sugar for a derived table with `UNION ALL`. These virtual tables are [useful for generating a numbers table from scratch](http://dba.stackexchange.com/q/7233/3690) without querying system objects for example. Cross joining real tables takes locks which [may have undesirable results](http://stackoverflow.com/q/20155337/73226) – Martin Smith Dec 18 '13 at 18:59
  • lol `syntactic sugar` and cheers pal for the interesting read :) – M.Ali Dec 18 '13 at 19:05
  • A nice answer! Maybe you can create it as a view and reuse it as wish. – jean Dec 18 '13 at 19:22