mysql

Khóa học miễn phí MySQL – MINUS Operator nhận dự án làm có lương

Posted on by user

MySQL – MINUS Operator

Table of content

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:

Minus Operator

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 −

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 −

$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

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user