MySQL - Subtraction Operator (-)

MySQL performs operations on its data using various types of operators. It supports arithmetic operators, comparison operators, logical operators, assignment operators, etc. However, only arithmetic operators deal solely with numeric values.

Arithmetic operators are used to operations such as addition, subtraction, multiplication, division and modulus operations on the data stored in MySQL; where the Subtraction Operator (-) is used to subtract one number from another number.

The subtraction operator can be used with the SELECT, UPDATE, and DELETE statements in MySQL, along with clauses like WHERE, ORDER BY etc.

Syntax

Following is the syntax of MySQL Subtraction operator −

[SELECT|DELETE|UPDATE] x - y;

Where, "x" and "y" are placeholders for the numeric values you want to subtract.

Example

In the following example, we are the subtraction operator (-) to subtract two numbers −

SELECT 4156456 - 56445;

Output

This will produce the following result −

4156456 - 56445
4100011

Example

In this query, we are subtracting two float numbers and a whole number using the subtraction operator −

SELECT 547.5478 - 657.3547 - 5475;

Output

This will produce the following result −

547.5478 - 657.3547 - 5475
-5584.8069

Example

In the following query, we are creating a table named CUSTOMERS using the CREATE TABLE statement −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The below query uses INSERT statement to insert 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Execute the following query to retrieve all the records inserted in CUSTOMERS table −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Here, we are using the MySQL subtraction operator to subtract the salary and age of every customer −

SELECT NAME, SALARY - AGE as Difference FROM CUSTOMERS;

This will produce the following result −

NAME RESULT
Ramesh 1968.00
Khilan 1475.00
Kaushik 1977.00
Chaitali 6475.00
Hardik 8473.00
Komal 4478.00
Muffy 9976.00

Example

You can also use the subtraction operator with DELETE statement. In such cases, we use WHERE clause along with assignment operators.

In the following query, we will delete rows from the CUSTOMERS table where the difference between their SALARY and AGE is less than 5000.

DELETE FROM CUSTOMERS 
WHERE SALARY - AGE < 5000;

Output

Four records has been deleted from the CUSTOMERS table −

Query OK, 4 rows affected (0.01 sec)

Verification

Execute the following query to verify whether the above records have been deleted or not −

Select * From CUSTOMERS;

As we can see the output, the customers whose SALARY and AGE is less than 5000 are deleted.

ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00

Example

Along with SELECT and DELETE statements, you can also use the subtraction operator with the UPDATE statement.

Let us see an example where we try to update the marks of Asha to at least pass percentage using the UPDATE... SET statement below −

UPDATE CUSTOMERS
SET NAME = 'Hrithik'
WHERE SALARY - AGE > 9000;

Output

This will produce the following result −

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

Execute the below query to verify whether the above records have been updated or not −

Select * From CUSTOMERS;

As we can see from the output, the customers whose SALARY and AGE are greater than 5000 have had their names changed to 'Hrithik'.

ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Hrithik 24 Indore 10000.00
Advertisements