MySQL - Subtraction Operator (-)

Quiz

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

Print Page