Given two tables:

1. tblBase - database table (code_no, empid)
2. @type_temp is a temporary user defined table which will come from vb.net frontend (code_no, name)

Firstly I need to get code_no which are in @type_temp and not in tblBase.

Query:

select 
    t.code_no, 'Non-existing' as Remark 
from 
    @type_temp t 
left join 
    tblBase b on t.code_no = b.code_no 
where 
    b.code_no is null

Next I need to get all code_no that have empid = 1.

Query :

select 
    t.code_no, 'Existing' as Remark 
from 
    @type_temp t 
inner join 
    tblBase b on t.code_no = b.code_no 
where 
    b.empid = 1

I need to use both these queries together.

Currently I'm using union to club the two.

select 
    t.code_no, 'Non-existing' as Remark 
from 
    @type_temp t 
left join 
    tblBase b on t.code_no = b.code_no 
where 
    b.code_no is null

union

select 
    t.code_no, 'Existing' as Remark 
from 
    @type_temp t 
inner join 
    tblBase b on t.code_no = b.code_no 
where 
    b.empid = 1

I do not want union here. Any other alternative possible?

CodePudding user response：

Does this work for you?

SELECT type_temp.code_no
     , CASE WHEN tblBase.code_no IS NULL THEN 'Non-existing' ELSE 'Existing' END AS Remark
FROM   @type_temp AS type_temp
 LEFT
  JOIN tblBase
    ON tblBase.code_no = type_temp.code_no
WHERE  tblBase.empid = 1
OR     tblBase.code_no IS NULL
;

CodePudding user response：

An even better option than @gvee's answer, is to put the conditions into the ON clause

SELECT
  type_temp.code_no,
  CASE WHEN tblBase.code_no IS NULL THEN 'Non-existing' ELSE 'Existing' END AS Remark
FROM @type_temp AS type_temp
LEFT JOIN tblBase ON tblBase.code_no = type_temp.code_no
    AND tblBase.empid = 1;

This is likely to be more performant, as the join is pre-filtered.