How can I use Except all in SQL Server?

Question

I'm trying to use this query statement in order to except daysoff and a duration, let's called holiday from a calendar of a specific month . This what I have tried :

select Weekday from Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST('2021-01-01' 
AS datetime))+2, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST('2021-01-01' AS datetime))+2, -1)) 
except (select dayId as dayId from days) 
--select Date from holiday,Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST('2021-01-01' AS datetime))+2, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST('2021-01-01' AS datetime))+2, -1)) where Date between startDate and endDate 

and this is the function of Get_Calendar_Date

ALTER FUNCTION [dbo].[Get_Calendar_Date]
(
    @StartDate DATETIME
,   @EndDate DATETIME
)
RETURNS TABLE
AS
 
RETURN
(
    SELECT  Tbl_Obj.RNo
        ,   DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate) AS [Date]
        ,   DATEPART(quarter,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Quarter]
        ,   DATEPART(dayofyear,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [DayofYear]
        ,   DATEPART(WEEK,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [WeekofYear]
        ,   DATEPART(YEAR,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Year]
        ,   DATEPART(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Month]
        ,   DATEPART(DAY,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Day]
        ,   DATEPART(weekday,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Weekday]
        ,   DATENAME(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [MonthName]
        ,   DATENAME(weekday,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [WeekdayName]
        ,   (RIGHT( REPLICATE('0',(4)) +
                CONVERT([VARCHAR],DATEPART(YEAR,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)),0)
                ,(4)
             )+
             RIGHT( REPLICATE('0',(2)) +
                CONVERT([VARCHAR],DATEPART(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)),0)
                ,(2)
             )
            ) AS [Vintage]
 
    FROM    ( SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS [RNo]
              FROM sys.all_objects WITH (NOLOCK)
            ) Tbl_Obj
          
    WHERE   DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate) <= @EndDate
)

• To except the daysoff ( weekends ) , I have used except , but what I got is something like this:

And the result expected should be like this because I have four Friday in the month ( dayId=6), I need to get all dayId with 6 ( All friday of months that represent the daysoff ) :

Excepted result:

Weekday
   6
   6
   6
   6

The result of executing without Except

    select Weekday from Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST('2021-01-01' 
AS datetime))+2, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST('2021-01-01' AS datetime))+2, -1))

Answer 1

Please aim for a minimal, reproducable example in the future.

Sample data

(Could be even more minimal.)

create table set1
(
  day int
);
insert into set1 (day) values (1),(2),(3),(1),(2),(3),(4); -- contains 2x 3

create table set2
(
  day int
);
insert into set2 (day) values (1),(2),(1),(2),(4); -- removed 3's

Issue reproduction

select set1.day
from set1
  except
select set2.day
from set2;

Result

day
---
3

Only unique values remain where you want all occurences.

Solutions

With not exists

select set1.day
from set1
where not exists ( select 'x'
                   from set2
                   where set2.day = set1.day );

With left join

select set1.day
from set1
left join set2
  on set2.day = set1.day
where set2.day is null;

Result

day
---
3
3

Fiddle to see things in action.

Answer 2

You can use not exists

select Weekday from Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST('2021-01-01' 
AS datetime))+2, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST('2021-01-01' AS datetime))+2, -1))
where  not exists (select 1 from days where dayid=weekday) 

Answer 3

First, @Sander's answer is very good and tells you what you need to know IMO. I want to dig into this a bit deeper. EXCEPT is a SET Operator just like INTERSECT and UNION (UNION ALL is a multiset operator - different topic). There is almost no documentation on the topic of set operators except for Itzik Ben-Gan's T-SQL Fundamentals books. Note these published examples: TSQLFundamentals20160601

Set Operators return a Set, which, by definition is unique (distinct). This is why, in Sander's examples EXCEPT returns a distinct value whereas the LEFT JOIN and NOT EXISTS do not return a distinct set. JOINs (INNER, OUTER and CROSS) are table operators which return a multiset, AKA "bag" - a non-distinct set. Let's compare the execution plans for EXCEPT, NOT EXISTS and THE LEFT JOIN.

EXCEPT and NOT EXISTS leverage Anti Semi Join table operators in the execution plan. An anti join is where you say "give me everything here that does not exists there." A Semi-Join is returns a distinct set from the left table (the table before the NOT EXISTS statement.) The LEFT JOIN uses a traditional JOIN operator.

What makes the anti semi join plans better is that they are able to get the job done while retrieving fewer rows from the set2 table. The EXCEPT solution, ignoring the "estimated cost" - the EXCEPT Plan is the most efficient as it gets the job done by retrieving 20 rows vs 28 rows for NOT EXIST and 42 rows for the LEFT JOIN.

Next the STATISTICS IO (number of reads):

--==== NOT EXISTS
Table 'set2'. Scan count 1, logical reads 7, physical reads 0...
Table 'set1'. Scan count 1, logical reads 1, physical reads 0...

--==== LEFT JOIN
Table 'set2'. Scan count 1, logical reads 7, physical reads 0...
Table 'set1'. Scan count 1, logical reads 1, physical reads 0...

--==== EXCEPT
Table 'set2'. Scan count 1, logical reads 4, physical reads 0...
Table 'set1'. Scan count 1, logical reads 1, physical reads 0...

The EXCEPT solution, under the hood, is generating 1/2 the IO. Set operators can be powerful.