How to write a Sql statement without using union?

Question

Hi,

I have a sql statement like below. How can I add a single row(code = 0, desc = 1) to result of this sql statement without using union keyword? thanks.

select code, desc
from material 
where material.ExpireDate ='2010/07/23'

Answer 1

You can always create a view for your table which itself uses UNION keyword

CREATE VIEW material_view AS SELECT code, desc, ExpireDate FROM material UNION SELECT '0', '1', NULL;
SELECT code, desc FROM material_view WHERE ExpireDate = '2010/07/23' OR code = '0';

Answer 2

Since you don't want to use either a union or a view, I'd suggest adding a dummy row to the material table (with code = 0, desc = 1, and ExpireDate something that would never normally be selected - eg. 01 January 1900) - then use a query like the following:

select code, desc
from material 
where material.ExpireDate ='2010/07/23' or 
    material.ExpireDate ='1900/01/01' 

Normally, a Union would be my preferred option.

Answer 3

 WITH material   AS
 (
 SELECT * 
 FROM 
      (VALUES (2, 'x', '2010/07/23'), 
              (3, 'y', '2009/01/01'), 
              (4, 'z', '2010/07/23')) vals (code, [desc], ExpireDate)
 )

SELECT 
     COALESCE(m.code,x.code) AS code, 
     COALESCE(m.[desc],x.[desc]) AS [desc]
FROM material m
FULL OUTER JOIN (SELECT 0 AS code, '1' AS [desc] ) x ON 1=0
WHERE m.code IS NULL OR m.ExpireDate ='2010/07/23'

Gives

code        desc
----------- ----
2           x
4           z
0           1