How to output what is not in list

[rd13]

Select * from employee where empnum in ('1','2','3','4')

I don't have emp 3 and 4 in the table and so i want to output that.
Basically, instead of outputing what is in the table i want to output what is in the list but not in the table . Is it possible ?

[TheCPUWizard]

Create a temporary table (or table variable depending on the flavor of SQL), then do a left join, looing for RHS = null.

[rd13]

hmm..interesting. Not a sql expert. But, can this all be done in query analyzer statements? I am using toad.

create a temp table like #temp
Fill in temp with list items
left or right join with the table i am querying

[Shuja Ali]

Yes it can be done in T-SQL too. After all a stored procedure or a function is also T-SQL. You will need to execute the whole block at once, and not line by line.

[rd13]

Something like this??
CREATE TABLE #temp (empnum int )

INSERT INTO #temp(empnum) values ('1','2','3','4')

select * from #temp left outer join employee e on
e.empnum=#temp.empnum

drop table #temp

But for this don't need to have table create permissions. If i have read only permissions, is there a way to do it?

[KrisSimonis]

Don't use a temp table, just declare a temporary table variable.



DECLARE @Temp TABLE { EmpNumber int }

INSERT INTO @Temp VALUES (1)
INSERT INTO @Temp VALUES (2)
INSERT INTO @Temp VALUES (3)
INSERT INTO @Temp VALUES (4)

SELECT T.EmpNumber
FROM @Temp
LEFT JOIN Employee E ON E.EmpNum = T.EmpNumber
WHERE E.EmpNum IS NULL

[cjard]

What database are you using?