Learn now How to use MINUS operator in SQL

The MINUS operator is used in SQL to retrieve the distinct rows of one query result set that do not appear in another query result set. It is also known as the EXCEPT operator in some SQL implementations. The syntax for using the MINUS operator is as follows:

SELECT column1, column2, ...
FROM table1
MINUS
SELECT column1, column2, ...
FROM table2;

In the above syntax, table1 and table2 are the names of the tables from which we want to retrieve data, and column1, column2, etc. are the column names that we want to retrieve.

Here’s an example of using the MINUS operator in SQL:

Suppose we have two tables, customers and orders, with the following data:

customers
+----+----------+-----------+
| id | name     | email     |
+----+----------+-----------+
|  1 | Alice    | alice@abc |
|  2 | Bob      | bob@abc   |
|  3 | Charlie  | char@abc  |
+----+----------+-----------+

orders
+----+------------+--------+
| id | order_date | amount |
+----+------------+--------+
|  1 | 2022-01-01 |    100 |
|  1 | 2022-02-01 |    200 |
|  2 | 2022-01-15 |    150 |
|  3 | 2022-02-01 |     75 |
+----+------------+--------+

We want to retrieve the names of the customers who have not placed any orders. We can use the MINUS operator to achieve this as follows:

SELECT name
FROM customers
MINUS
SELECT c.name
FROM customers c
JOIN orders o
ON c.id = o.id;

The above query will return the following result:

+----------+
| name     |
+----------+
| Charlie  |
+----------+

In the above query, the first SELECT statement retrieves the names of all customers, and the second SELECT statement retrieves the names of the customers who have placed orders. The MINUS operator is used to subtract the second result set from the first result set, giving us the names of the customers who have not placed any orders.

Note that the MINUS operator only works with columns that have the same data type and are in the same order in both SELECT statements. Also, the result set of the MINUS operator is always a set of distinct rows. If you want to include duplicates, you can use the UNION ALL operator instead.

Also check WHAT IS GIT ? It’s Easy If You Do It Smart

You can also visite the Git website (https://git-scm.com/)

Leave a Reply Cancel reply