MySQL - 交集運算子

Table of content
上一頁
下一頁

在數學集合論中，兩個集合的交集是一個僅包含兩個集合共有的元素的集合。換句話說，兩個集合的交集是存在於兩個集合中的元素的集合。

如果我們使用INTERSECT運算子對兩個集合執行交集運算，它將顯示兩個表中的公共行。此運算子會從最終結果集中刪除重複行。

MySQL INTERSECT 運算子

在 MySQL 中，INTERSECT 運算子用於返回兩個 SELECT（表）語句的結果集中相同的/公共記錄。

但是，只有當兩個 SELECT 語句具有相同資料型別和名稱的相同數量的列時，INTERSECT 運算子才有效。

語法

以下是 MySQL 中 INTERSECT 運算子的語法：

SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
INTERSECT
SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN

示例

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

CREATE TABLE STUDENTS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   PRIMARY KEY(ID)
);

在這裡，我們使用INSERT語句將一些值插入表中。

INSERT INTO STUDENTS VALUES
(1, 'Vijay', 'Cricket', 18),
(2, 'Varun', 'Football', 26),
(3, 'Surya', 'Cricket', 19),
(4, 'Karthik', 'Cricket', 25),
(5, 'Sunny', 'Football', 26),
(6, 'Dev', 'Cricket', 23);

表建立如下：

ID 姓名 愛好 年齡
1 Vijay 板球 18
2 Varun 足球 26
3 Surya 板球 19
4 Karthik 板球 25
5 Sunny 足球 26
6 Dev 板球 23

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

CREATE TABLE ASSOCIATES(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   SUBJECT VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   PRIMARY KEY(ID)
);

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

INSERT INTO ASSOCIATES VALUES
(1, 'Naina', 'Maths', 24, 'Cricket'),
(2, 'Varun', 'Physics', 26, 'Football'),
(3, 'Dev', 'Maths', 23, 'Cricket'),
(4, 'Priya', 'Physics', 25, 'Cricket'),
(5, 'Aditya', 'Chemistry', 21, 'Cricket'),
(6, 'Kalyan', 'Maths', 30, 'Football');

表建立如下：

ID 姓名 科目 年齡 愛好
1 Naina 數學 24 板球
2 Varun 物理 26 足球
3 Dev 數學 23 板球
4 Priya 物理 25 板球
5 Aditya 化學 21 板球
6 Kalyan 數學 30 足球

現在，我們使用以下查詢返回兩個表中的公共記錄：

SELECT NAME, AGE, HOBBY FROM STUDENTS
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES;

輸出

輸出如下所示：

姓名 年齡 愛好
Varun 26 足球
Dev 23 板球

BETWEEN 運算子與 INTERSECT

MySQL INTERSECT 運算子可以與 BETWEEN 運算子一起使用，以查詢存在於指定範圍內的行。

示例

在以下查詢中，我們檢索兩個表中都存在的記錄。此外；我們正在檢索年齡在 25 到 30 之間的記錄：

SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE AGE BETWEEN 25 AND 30
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE AGE BETWEEN 20 AND 30;

輸出

執行給定程式後，輸出將顯示如下：

姓名 年齡 愛好
Varun 26 足球

IN 運算子與 INTERSECT

在 MySQL 中，我們可以使用 INTERSECT 運算子與 IN 運算子一起使用，以查詢具有指定值的公共行。IN 運算子用於根據指定值的列表過濾結果集。

示例

在以下查詢中，我們嘗試從兩個表中返回公共記錄。此外；我們使用 th IN 運算子來檢索愛好為“板球”的記錄。

SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE HOBBY IN('Cricket')
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE HOBBY IN('Cricket');

輸出

上面程式的輸出如下所示：

姓名 年齡 愛好
Dev 23 板球

LIKE 運算子與 INTERSECT

LIKE 運算子用於對字串值執行模式匹配。

我們可以將 LIKE 運算子與 MySQL 中的 INTERSECT 運算子一起使用，以查詢與指定模式匹配的公共行。

示例

在以下查詢中，我們使用萬用字元“%”與 LIKE 運算子一起從兩個表的公共名稱中獲取包含“v”的名稱。

SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE NAME LIKE 'v%'
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE NAME LIKE 'v%';

輸出

讓我們編譯並執行程式，以產生以下結果：

姓名 年齡 愛好
Varun 26 足球

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

除了在 MySQL 伺服器上執行 Intersect 運算子之外，我們還可以使用客戶端程式在表上執行 Intersect 運算子。

語法

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

PHP NodeJS Java Python

要在 PHP 程式中透過 MySQL 表執行 Intersect 運算子，需要使用 **mysqli** 聯結器的 **query()** 函式執行 **INTERSECT** 語句。

$sql = "SELECT column1, column2,..., columnN FROM table1, table2,..., 
tableN INTERSECT SELECT column1, column2,..., columnN 
FROM table1, table2,..., tableN";  
$mysqli->query($sql);

要在 JavaScript 程式中透過 MySQL 表執行 Intersect 運算子，需要使用 **mysql2** 聯結器的 **query()** 函式執行 **INTERSECT** 語句。

sql = "SELECT column1, column2,..., columnN FROM table1, table2,..., tableN
INTERSECT SELECT column1, column2,..., columnN 
FROM table1, table2,..., tableN";
con.query(sql);

要在 Java 程式中透過 MySQL 表執行 Intersect 運算子，需要使用 **JDBC 4 型別** 驅動程式的 **executeQuery()** 函式執行 **INTERSECT** 語句。

String sql = "SELECT column1, column2,..., columnN FROM table1, table2,..., tableN 
INTERSECT SELECT column1, column2,..., columnN 
FROM table1, table2,..., tableN";
statement.executeQuery(sql);

要在 Python 程式中透過 MySQL 表執行 Intersect 運算子，需要使用 **MySQL Connector/Python** 提供的 **execute()** 函式執行 **INTERSECT** 語句。

intersect_query = SELECT column1, column2,..., column
FROM table1, table2,..., tableN
INTERSECT SELECT column1, column2,..., column
FROM table1, table2,..., tableN
cursorObj.execute(intersect_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 NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES;";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: \n");
   while($row = $result->fetch_assoc()) {
      printf("NAME %s, AGE %d, HOBBY %s",
      $row["NAME"],
      $row["AGE"],
      $row["HOBBY"],);
      printf("\n");         
   }
} else {
   printf('No record found.
');
}
mysqli_free_result($result);
$mysqli->close();

輸出

獲得的輸出如下：

Table records:
NAME Varun, AGE 26, HOBBY Football
NAME Dev, AGE 23, HOBBY Cricket       

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 STUDENTS table
  sql = "CREATE TABLE STUDENTS(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,HOBBY VARCHAR(20) NOT NULL,AGE INT NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(1, 'Vijay', 'Cricket',18),(2, 'Varun','Football', 26),(3, 'Surya', 'Cricket',19),(4, 'Karthik','Cricket', 25),(5, 'Sunny','Football', 26),(6, 'Dev', 'Cricket',23);"
  con.query(sql);

  //Creating ASSOCIATES table
  sql = "CREATE TABLE ASSOCIATES(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,SUBJECT VARCHAR(20) NOT NULL,AGE INT NOT NULL,HOBBY VARCHAR(20) NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);
  
  //Inserting Records
  sql = "INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(1, 'Naina','Maths', 24, 'Cricket'),(2, 'Varun','Physics', 26, 'Football'),(3, 'Dev','Maths', 23, 'Cricket'),(4, 'Priya','Physics', 25, 'Cricket'),(5,'Aditya', 'Chemistry', 21, 'Cricket'),(6,'Kalyan', 'Maths', 30, 'Football');"
  con.query(sql);

  //Using INTERSECT Operator
  sql = "SELECT NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

輸出

產生的輸出如下：

Connected!
--------------------------
[
  { NAME: 'Varun', AGE: 26, HOBBY: 'Football' },
  { NAME: 'Dev', AGE: 23, HOBBY: 'Cricket' }
]  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class IntersectOperator {
  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 NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String name = rs.getString("NAME");
              String age = rs.getString("AGE");
              String hobby = rs.getString("HOBBY");
              System.out.println("Name: " + name + ", Age: " + age + ", Hobby: " + hobby);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

輸出

獲得的輸出如下所示：

Table records: 
Name: Varun, Age: 26, Hobby: Football
Name: Dev, Age: 23, Hobby: Cricket  

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
intersect_query = f"""
SELECT NAME, AGE, HOBBY FROM STUDENTS
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES;
"""
cursorObj.execute(intersect_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

輸出

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

('Varun', 26, 'Football')
('Dev', 23, 'Cricket')
列印頁面
上一頁
下一頁
廣告