SQL show records that don't exist in my table variable

Question

I have a table variable that holds orderID, UnitID and OrderServiceId (it is already populated via a query with insert statement).

I then have a query under this that returns 15 columns which also include the OrderId, UnitId, OrderServiceId

I need to only return the rows from this query where the same combination of OrderId, UnitId, and OrderServiceId are not in the table variable.

Answer 1

select q.*
from (
    MyQuery
) q
left outer join MyTableVariable t on q.ORDERID  = t.ORDERID
    and q.UNITID= t.UNITID
    and q.ORDERSERVICESID = t.ORDERSERVICESID 
where t.ORDERID is null

Answer 2

You can use NOT EXISTS. e.g.

FROM YourQuery q
WHERE NOT EXISTS 
(
SELECT * FROM @TableVar t
WHERE t.OrderId = q.OrderId
  and t.UnitId = q.UnitId 
  and t.OrderServiceId=q.OrderServiceId
)

Answer 3

You can use EXCEPT | INTERSECT operators for this (link).

Example:

(select 3,4,1
union all
select 2,4,1)

intersect

(select 1,2,9
union all
select 3,4,1)