MySQL - MINUS Operator

Table of content

MySQL MINUS Operator
Minus Operator Using Client Program

Quiz

MySQL MINUS Operator

The MySQL MINUS operator is one of the set operators which is used to fetch unique records from one table that do not exist in another table. In other words, the MINUS operator compares two tables and returns the unique rows from the first table that do not exist in the second table.

Let's consider the following diagram to understand the MINUS operation:

As we can see in the above diagram, the MINUS operator returned the table containing 1 and 3 values as output because they are the distinct values in table1 which do not exist in table2.

MySQL does not support the MINUS operator, we can use the LEFT JOIN instead of the MINUS operator.

Syntax

Following is the basic syntax of the MINUS operator −

SELECT column_lists FROM table_name WHERE (condition)
MINUS
SELECT column_lists FROM table_name WHERE (condition);

Unfortunately MySQL does not support the MINUS operator. However we can use the MySQL JOIN clause instead of MINUS operator.

Following is the syntax for JOIN clause to perform MINUS operation −

SELECT column_list FROM table_name1
LEFT JOIN table_name2 ON join_predecate
WHERE table_name2.column_name IS NULL;

Example

Firstly, let us create a table named CUSTOMERS using the following query −

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

Here, we are inserting some records into the CUSTOMERS table using the INSERT statement −

INSERT INTO CUSTOMERS 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);

The table will be created as −

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

Now, let us create another table named ORDERS using the below query −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Here, we are inserting some records into the ORDERS table using the INSERT INTO statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

The table is created as follows −

OID	DATE	ID	AMOUNT
102	2009-10-08 00:00:00	3	3000.00
100	2009-10-08 00:00:00	3	1500.00
101	2009-11-20 00:00:00	2	1560.00
103	2008-05-20 00:00:00	4	2060.00

As MySQL does not support the MINUS operator instead we can use the JOIN to perform this operation −

The following query selects all the customers who do not have any orders by joining two tables −

SELECT ID, NAME, AGE FROM CUSTOMERS
LEFT JOIN ORDERS USING (ID)
WHERE ORDERS.ID IS NULL;

Output

The following are the customers who do not have any orders:

ID	NAME	AGE
1	Ramesh	32
5	Hardik	27
6	Komal	22
7	Muffy	24

Minus Operator Using Client Program

In addition to executing the Minus Operator in MySQL table using an SQL query, we can also perform the another operation on a table using a client program.

Syntax

Following are the syntaxes of the Minus Operator in MySQL table in various programming languages −

PHP NodeJS Java Python

To perform the Minus Operator in MySQL through a PHP program, we need to execute an SQL statement with JOIN clause using the query() function provided by mysqli connector.

$sql = "SELECT column_lists FROM table_name WHERE (condition)
LEFT JOIN SELECT column_lists FROM table_name WHERE (condition)";  
$mysqli->query($sql);

To perform the Minus Operator in MySQL through a JavaScript program, we need to execute an SQL statement with JOIN clause using the query() function provided by mysql2 connector.

sql = "SELECT column_list FROM table_name1 
LEFT JOIN table_name2 ON join_predecate 
WHERE table_name2.column_name IS NULL";
con.query(sql);

To perform the Minus Operator in MySQL through a Java program, we need to execute an SQL statement with JOIN clause using the executeQuery() function provided by JDBC type 4 driver.

String sql = "SELECT column_list FROM table_name1 
LEFT JOIN table_name2 ON join_predecate 
WHERE table_name2.column_name IS NULL";
statement.executeQuery(sql);

To perform the Minus Operator in MySQL through a Python program, we need to execute an SQL statement with JOIN clause using the execute() function provided by MySQL Connector/Python.

query = "SELECT column_list FROM table_name1 
LEFT JOIN table_name2 ON join_predecate 
WHERE table_name2.column_name IS NULL"
cursorObj.execute(query);

Example

Following are the implementations of this operation in various programming languages −

PHP NodeJS Java Python

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.
');
$sql = "SELECT * FROM CUSTOMERS LEFT JOIN ORDERS USING (CUST_ID) WHERE ORDERS.CUST_ID IS NULL";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: \n");
   while($row = $result->fetch_assoc()) {
      printf("CUST_ID %d, NAME %s, ADDRESS %s, SALARY %f, OID %d, DATE %s, ADDRESS %s, AMOUNT %f",  
      $row["CUST_ID"],
      $row["NAME"],
      $row["ADDRESS"],
      $row["SALARY"],
      $row["OID"],
      $row["DATE"],
      $row["ADDRESS"],
      $row["AMOUNT"],);
      printf("\n");         
   }
} else {
   printf('No record found.
');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
CUST_ID 1, NAME Ramesh, ADDRESS , SALARY 2000.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
CUST_ID 5, NAME Hardik, ADDRESS , SALARY 8500.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
CUST_ID 6, NAME Komal, ADDRESS , SALARY 4500.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
CUST_ID 7, NAME Muffy, ADDRESS , SALARY 10000.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating CUSTOMERS table
  sql = "CREATE TABLE CUSTOMERS (CUST_ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(25) NOT NULL,SALARY DECIMAL(18, 2),PRIMARY KEY(CUST_ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS (CUST_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, 'MP', 4500.00 ),(7,'Muffy', 24, 'Indore', 10000.00 );"
  con.query(sql);

  //Creating ORDERS table
  sql = "CREATE TABLE ORDERS (OID INT NOT NULL,DATE VARCHAR(20) NOT NULL,CUST_ID INT NOT NULL,ADDRESS CHAR(25),AMOUNT INT NOT NULL);"
  con.query(sql);
  
  //Inserting Records
  sql = "INSERT INTO ORDERS(OID, DATE, CUST_ID, AMOUNT) VALUES(102, '2009-10-08 00:00:00', 3, 3000),(100, '2009-10-08 00:00:00', 3, 1500),(101, '2009-11-20 00:00:00' , 2, 1560),(103, '2008-05-20 00:00:00', 4, 2060);"
  con.query(sql);

  //Using MINUS Operator
  sql = "SELECT * FROM CUSTOMERS LEFT JOIN ORDERS USING (CUST_ID) WHERE ORDERS.CUST_ID IS NULL;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

  Connected!
  --------------------------
[
  {
    CUST_ID: 1,
    NAME: 'Ramesh',
    AGE: 32,
    ADDRESS: null,
    SALARY: '2000.00',
    OID: null,
    DATE: null,
    AMOUNT: null
  },
  {
    CUST_ID: 5,
    NAME: 'Hardik',
    AGE: 27,
    ADDRESS: null,
    SALARY: '8500.00',
    OID: null,
    DATE: null,
    AMOUNT: null
  },
  {
    CUST_ID: 6,
    NAME: 'Komal',
    AGE: 22,
    ADDRESS: null,
    SALARY: '4500.00',
    OID: null,
    DATE: null,
    AMOUNT: null
  },
  {
    CUST_ID: 7,
    NAME: 'Muffy',
    AGE: 24,
    ADDRESS: null,
    SALARY: '10000.00',
    OID: null,
    DATE: null,
    AMOUNT: null
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class MinusOperator {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM CUSTOMERS LEFT JOIN ORDERS USING (CUST_ID) WHERE ORDERS.CUST_ID IS NULL";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("CUST_ID");
              String name = rs.getString("NAME");
              String age = rs.getString("AGE");
              String address = rs.getString("ADDRESS");
              String salary = rs.getString("SALARY");
              String oid = rs.getString("OID");
              String date = rs.getString("DATE");
              String address1 = rs.getString("ADDRESS");
              String amount = rs.getString("AMOUNT");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary + ", Oid: " + oid + ", Date: " + date + ", Address: " + address1 + ", Amount: " + amount);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table records: 
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00, Oid: null, Date: null, Address: Ahmedabad, Amount: null
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00, Oid: null, Date: null, Address: Bhopal, Amount: null
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00, Oid: null, Date: null, Address: MP, Amount: null
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00, Oid: null, Date: null, Address: Indore, Amount: null

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
query = f"""
SELECT * FROM CUSTOMERS
    LEFT JOIN ORDERS USING (CUST_ID)
    WHERE ORDERS.CUST_ID IS NULL;
"""
cursorObj.execute(query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Table records:
CUST_ID 1, NAME Ramesh, ADDRESS , SALARY 2000.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
CUST_ID 5, NAME Hardik, ADDRESS , SALARY 8500.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
CUST_ID 6, NAME Komal, ADDRESS , SALARY 4500.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
CUST_ID 7, NAME Muffy, ADDRESS , SALARY 10000.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000

Print Page

Save or share this article