Common Table Expressions (CTEs) provide a mechanism for you to define a subquery that may then be used elsewhere in a query. Unlike a derived table, a CTE is defined at the beginning of a query and may be referenced multiple times in the outer query
WITH CTE_year
AS
(
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM CTE_year
GROUP BY orderyear;
INTERSECT and EXCEPT compare two result sets against each other, and return rows in common, or rows that appear in one but not in the other.
CREATE TABLE #Colours1 ([Name] VARCHAR(50))
CREATE TABLE #Colours2 ([Name] VARCHAR(50))
INSERT INTO #Colours1 VALUES ('Blue'),('Red'),('Yellow'),('Pink')
INSERT INTO #Colours2 VALUES ('Blue'),('Red'),('Gray'),('Blue1'),('Black')
SELECT [Name] from #Colours1
INTERSECT
SELECT [Name] from #Colours2 --> Blue,Red
SELECT [Name] from #Colours1
EXCEPT
SELECT [Name] from #Colours2 --> Pink,Yellow
SELECT [Name] from #Colours2
EXCEPT
SELECT [Name] from #Colours1 --> Black,Blue1,Gray
CREATE TABLE #Employee ([Name] VARCHAR(50), [DepartmentId] INT)
CREATE TABLE #Dept ([Id] INT, [Name] VARCHAR(50))
INSERT INTO #Employee VALUES ('Blue',1),( 'Red',1), ('Yellow',2), ('Pink',4), ('Green',9)
INSERT INTO #Dept VALUES (1, 'IT'),(2,'CS'),(3,'Finance'),(4,'HR')
SELECT ee.Name, dd.Name [DeptName]
FROM #Employee ee
CROSS APPLY( SELECT Name FROM #Dept WHERE Id = ee.DepartmentId) dd
Name DeptName
Blue IT
Red IT
Yellow CS
Pink HR
SELECT ee.Name, dd.Name [DeptName]
FROM #Employee ee
OUTER APPLY( SELECT Name FROM #Dept WHERE Id = ee.DepartmentId ) dd
Name DeptName
Blue IT
Red IT
Yellow CS
Pink HR
Green NULL
