set operators provided by SQL Server:

• UNION
• INTERSEC
• EXCEPT

Union operator (merge)

Results from two different query statements can be combined into a single result set using the UNION operator. Query statements must have a compatible column type and an equal number of columns. The column names may be different in each statement, but the data types must be compatible. By compatible data types, it means that the content of one of the columns can be converted into another column. For example, if one query statement has the data type of int and another has the data type of money , they are compatible with each other and union can take place between them because int data can be converted to money . data

Syntax:

Query_Statement1 UNION [ALL] Query Statment_2

For example:

SELECT Product.ProductId FROM Production.Product UNION SELECT ProductId FROM Sales.SalesOrderDetail

The above statement will retrieve the entire ProductId column data of both tables that match. If you use the ALL clause, all the tables will appear in the result set including duplicate records (In orer detail there are many Orders selling the same Product).

UNION ALL example:

SELECT Product.ProductId FROM Production.Product UNION ALL SELECT ProductId FROM Sales.SalesOrderDetail

By default, the UNION operator deletes redundant records in the result set. However, if you add the ALL clause to the UNION operator, all records will be returned.

Difference between UNION and JOIN:

The JOIN and UNION operations can be used to combine data from one or more tables. The difference lies in the way the data is combined.

In a word, join combines data into new columns. If two tables are joined, the data from the first table is displayed in a set of columns along with the column of the second table in the same row.

Unions combine data into new rows. If two tables are “merged” together, the data from the first table is in one set of rows and the data from the second table in another set. The rows have the same result.

Reference: https://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union#:~:text=The%20difference%20lies%20in%20how,combine%20data% 20into%20new%20rows.

INTERSECT operator (intersection)

Let’s say the situation has 2 tables Product and SalesOrderDetail and want to display all the common rows in both tables. To do this, you can use the INTERSECT operator. The INTERSECT operator is used with two query statements that return a separate result set with rows common to both queries.

Syntax:

Query_statement1 INTERSECT Query_statement2

For example:

SELECT Product.ProductId FROM Production.Product INTERSECT SELECT ProductId FROM Sales.SalesOrderDetail

The result of the intersection between the Production.Product and Sales.SalesOrderDetail tables will only have productids that match records in the product table. In a large enterprise, there will be a large number of data records stored in the database. Instead of storing all the data in a single table, it can be divided into several different tables. When data is stored in separate tables, SQL Server has many ways to combine data from tables such as JOIN,UNION,INTERSECT .

Rules for using INTERSECT :

• The number of columns and the order in which they are given should be the same in both queries
• The data types of the columns being used must be compatible

EXCEPT operator (except)

The EXCEPT operator returns all distinct rows from the query to the left of this operator and removes all records from the result set if they match the condition to the right of the EXCEPT operator .

Syntax:

Query_statement1 EXCEPT Query_statement2

The two rules that apply to the INTERSECT operator also apply to the EXCEPT operator:

• The number of columns and the order in which they are given should be the same in both queries
• The data types of the columns being used must be compatible

For example:

SELECT Product.ProductId FROM Production.Product EXCEPT SELECT ProductId FROM Sales.SalesOrderDetail

With the example above, only the records from the Production.Product table that do not appear in the Sales.SalesOrderDetail table are returned, we can see that this clause can be used in the product search and analysis business. not yet sold.

We see that the EXCEPT operator retrieves all records from the first table excluding the matches in table 2, so when using EXCEPT, the order of the 2 tables in the query is important, and with INTERSECT it doesn’t matter which table is predefined.