Difference between MINUS and INTERSECT

In SQL Server, MINUS and INTERSECT are both set operators that are used to combine or compare the results of two or more queries.

MINUS, also known as EXCEPT, is used to combine the results of two queries and return only the rows that are unique to the first query. In other words, it returns all rows from the first query that are not present in the second query.

INTERSECT is used to compare the results of two queries and return only the rows that are common to both queries. In other words, it returns only the rows that are present in both queries.

Here’s an example to illustrate the difference between MINUS and INTERSECT:

-- Sample data
CREATE TABLE table1 (col1 INT);
CREATE TABLE table2 (col1 INT);

INSERT INTO table1 VALUES (1), (2), (3), (4);
INSERT INTO table2 VALUES (3), (4), (5), (6);

-- MINUS/EXCEPT example
SELECT col1 FROM table1
EXCEPT
SELECT col1 FROM table2;

-- Returns: 1, 2

-- INTERSECT example
SELECT col1 FROM table1
INTERSECT
SELECT col1 FROM table2;

-- Returns: 3, 4

In this example, we have two tables (table1 and table2) with some sample data. The first query uses MINUS/EXCEPT to return all rows from table1 that are not present in table2, which are rows 1 and 2. The second query uses INTERSECT to return only the rows that are present in both tables, which are rows 3 and 4.