How to use minus in sql

The MINUS operator in SQL is used to subtract rows from one table in another table and return the difference between the two sets of rows: Syntax: SELECT FROM table1 MINUS SELECT FROM table2 Difference and EXCEPT: The MINUS operator eliminates duplicate rows, but EXCEPT does not.

Usage of MINUS operator in SQL

Meaning of MINUS operator:

MINUS operator is used in SQL to subtract rows from one table in another table. It returns the difference between two sets of rows, i.e. rows that are present in the first set of rows but not in the second set of rows.

Syntax:

##SELECT * FROM table1 MINUS SELECT * FROM table2;

Among them:

• table1 is the table to be subtracted.
• table2 is the table to be subtracted from table1.

Example:

Suppose we have two tables:

<code>**employees**
| emp_id | emp_name |
|---|---|
| 1      | John Doe |
| 2      | Jane Doe |
| 3      | Mark Smith |

**departments**
| dept_id | dept_name |
|---|---|
| 10     | Sales |
| 20     | Marketing |</code>

If we want to find employees who do not belong to any department, we can Use the following query:

<code>SELECT * FROM employees MINUS SELECT * FROM departments;</code>

The query results will look like this:

<code>| emp_id | emp_name |
|---|---|
| 3      | Mark Smith |</code>

The difference between MINUS and EXCEPT:

MINUS and EXCEPT operators in SQL are used to subtract a set of rows from another set of rows. However, there is one key difference between them:

• MINUS eliminates duplicate rows, while EXCEPT does not.

So, in the example above, if there are two rows in the Employees table that contain information about Mark Smith, the MINUS query will return only one row, while the EXCEPT query will return two rows.

The above is the detailed content of How to use minus in sql. For more information, please follow other related articles on the PHP Chinese website!