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://127.0.0.1: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
打印页面
上一篇
下一篇
广告