UNION and UNION ALL in T-SQL

UNION

The UNION operator is used to combine the result sets of two or more SELECT statements. To use it successfully, you must follow three key rules:

• Column Consistency: Each SELECT statement must have the same number of columns.

• Data Compatibility: The corresponding columns in each statement must have similar or compatible data types.

• Distinct Results: By default, UNION removes duplicate records and returns only distinct values.

UNION ALL

The UNION ALL operator works similarly to UNION, but with one major difference: it retrieves all rows from the selected tables without filtering for duplicates. Because it doesn't spend resources searching for and removing identical records, UNION ALL is significantly faster and more efficient when you know your data is unique or when you specifically want to include every result.  

Use UNION ALL by default if your data is already unique or if you don't mind including duplicate records. Because UNION must perform extra work to ensure only distinct values are returned, it is slower than UNION ALL. Only reach for the standard UNION when you specifically need to filter out overlapping data between your query results.

Common Table Expression (CTE) in Sql Server

Common Table Expressions, or CTEs, can be thought of as temporary views that are created in memory for a single execution scope. They allow you to simplify complex joins and subqueries by defining a named result set that exists only until the next query runs. Whether you are using Recursive or Non-Recursive CTEs, understanding their limitations—such as being unindexable and requiring immediate execution—is key to writing efficient SQL code.

• We can take CTE as tempory view (can be thought as tempory result set) that create in memory

• Its scope valid until next query run

• CTE need to use just after it declaration  

• There are two type Recursive and Nonrecursive CTE

• Can define multiple CTE

• CTE are unindexable ( it cau use exist index in referenced object)

• CTE can't have constrain

• ORDER BY clause can't be used in the CTE_Query_Definition , execpt when use TOP keyword

Simple Example.

ex 1

WITH CTE_CUSTOMER_EX2
(         [CustomerID]
          ,[CompanyName]
  ,[ContactName]
  ,[ContactTitle]
  ,[Address]
  ,[City]
)
AS
(
SELECT [CustomerID]
  ,[CompanyName]
  ,[ContactName]
  ,[ContactTitle]
  ,[Address]
  ,[City]

  FROM .[dbo].[Customers]
)


SELECT * FROM CTE_CUSTOMER_EX2

ex 2

;WITH CTE_CUSTOMER_EX1
AS
(
SELECT [CustomerID]
 ,[CompanyName]
 ,[ContactName]
 FROM .[dbo].[Customers]

)
SELECT * FROM CTE_CUSTOMER_EX1

Note
If there are query before CTE define , need to use Semicolon teminator before declare CTE


Multiple CTE

  With CTE_M1 as 
(
-select statement
) 
     , CTE_M2 as 
(
-select statemet

)

you can find some more detail and examples using this Link and Link2