MySQL - MINUS 運算子

Table of content
上一頁
下一頁

MySQL MINUS 運算子

MySQL 的MINUS運算子是集合運算子之一，用於從一個表中獲取在另一個表中不存在的唯一記錄。換句話說，MINUS 運算子比較兩個表，並返回第一個表中在第二個表中不存在的唯一行。

讓我們考慮下圖來理解 MINUS 操作

Minus Operator

如上圖所示，MINUS 運算子返回包含值 1 和 3 的表作為輸出，因為它們是 table1 中在 table2 中不存在的唯一值。

MySQL 不支援 MINUS 運算子，我們可以使用 LEFT JOIN 代替 MINUS 運算子。

語法

以下是 MINUS 運算子的基本語法：

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

不幸的是，MySQL不支援 MINUS 運算子。但是，我們可以使用 MySQL 的JOIN子句代替 MINUS 運算子。

以下是用於執行 MINUS 操作的 JOIN 子句的語法：

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

示例

首先，讓我們使用以下查詢建立一個名為CUSTOMERS的表：

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)
);

在這裡，我們使用 INSERT 語句將一些記錄插入 CUSTOMERS 表中：

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);

表將建立如下：

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

現在，讓我們使用以下查詢建立另一個名為 ORDERS 的表：

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

在這裡，我們使用 INSERT INTO 語句將一些記錄插入 ORDERS 表中：

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);

表建立如下：

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

由於 MySQL 不支援MINUS運算子，我們可以使用 JOIN 來執行此操作：

以下查詢透過連線兩個表來選擇沒有任何訂單的客戶：

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

輸出

以下是沒有任何訂單的客戶

ID 姓名(NAME) 年齡(AGE)
1 Ramesh 32
5 Hardik 27
6 Komal 22
7 Muffy 24

使用客戶端程式的 MINUS 運算子

除了使用 SQL 查詢在 MySQL 表中執行 MINUS 運算子之外，我們還可以使用客戶端程式對錶執行其他操作。

語法

以下是各種程式語言中 MySQL 表中 MINUS 運算子的語法：

PHP NodeJS Java Python

要在透過 PHP 程式在 MySQL 中執行 MINUS 運算子，我們需要使用mysqli聯結器提供的query()函式執行帶有 JOIN 子句的 SQL 語句。

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

要在透過 JavaScript 程式在 MySQL 中執行 MINUS 運算子，我們需要使用mysql2聯結器提供的query()函式執行帶有 JOIN 子句的 SQL 語句。

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

要在透過 Java 程式在 MySQL 中執行 MINUS 運算子，我們需要使用JDBC type 4驅動程式提供的executeQuery()函式執行帶有 JOIN 子句的 SQL 語句。

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);

要在透過 Python 程式在 MySQL 中執行 MINUS 運算子，我們需要使用MySQL Connector/Python提供的execute()函式執行帶有 JOIN 子句的 SQL 語句。

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

示例

以下是此操作在各種程式語言中的實現：

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();

輸出

獲得的輸出如下：

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)
  });
});

輸出

生成的輸出如下：

  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://: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();
    }
  }
}

輸出

獲得的輸出如下所示：

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()

輸出

以下是上述程式碼的輸出：

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
列印頁面
上一篇
下一篇
廣告
© . All rights reserved.