MySQL - UNION 運算子

要透過 PHP 程式使用 UNION 運算子組合表，我們需要使用名為query() 的mysql2 函式執行帶有 UNION 運算子的 SQL 語句，如下所示：

sql= " SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2";
con.query(sql);

要透過 PHP 程式使用 UNION 運算子組合表，我們需要使用名為executeQuery() 的JDBC 型別 4 驅動程式函式執行帶有 UNION 運算子的 SQL 語句，如下所示：

String sql = "SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2";
statement.executeQuery(sql);

要使用PHP程式透過UNION運算子組合表，我們需要使用名為MySQL Connector/Python的函式execute()執行帶有UNION運算子的SQL語句，如下所示：

union_query = "SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2"
cursorObj.execute(union_query);

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 PROSPECT table
  sql = "CREATE TABLE PROSPECT( fname varchar(400), lname varchar(400), addr varchar(200));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO PROSPECT (fname, lname, addr) VALUES ('peter', 'Jones', '482 Rush St., Apt. 402'), ('Bernice', 'Smith', '916 Maple Dr.');"
  con.query(sql);

  //Creating CUSTOMER table
  sql = "CREATE TABLE CUSTOMER( last_name varchar(400), first_name varchar(400), address varchar(200));"
  con.query(sql);
  
  //Inserting Records
  sql = "INSERT INTO CUSTOMER (last_name, first_name, address) VALUES ('Peterson', 'Grace', '16055 Seminole Ave.'), ('Smith', 'Bernice', '916 Maple Dr.'), ('Brown', 'Walter', '8602 1st St.');"
  con.query(sql);

  //Creating vendor table
  sql = "CREATE TABLE vendor( company varchar(400), street varchar(400));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO vendor (company, street) VALUES ('ReddyParts, Inc.', '38 Industrial Blvd.'), ('Parts-to-go, Ltd.', '213B Commerce Park.');"
  con.query(sql);

  //Using UNION
  sql = "SELECT fname, lname, addr FROM prospect UNION SELECT first_name, last_name, address FROM customer UNION SELECT company, '', street FROM vendor;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});   

輸出

生成的輸出如下：

Connected!
--------------------------
[
  { fname: 'peter', lname: 'Jones', addr: '482 Rush St., Apt. 402' },
  { fname: 'Bernice', lname: 'Smith', addr: '916 Maple Dr.' },
  { fname: 'Grace', lname: 'Peterson', addr: '16055 Seminole Ave.' },
  { fname: 'Walter', lname: 'Brown', addr: '8602 1st St.' },
  { fname: 'ReddyParts, Inc.', lname: '', addr: '38 Industrial Blvd.' },
  { fname: 'Parts-to-go, Ltd.', lname: '', addr: '213B Commerce Park.' }
]    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class UnionOperator {
    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 fname, lname, addr FROM prospect UNION SELECT first_name, last_name, address FROM customer UNION SELECT company, '', street FROM vendor";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
                String fname = rs.getString("fname");
                String lname = rs.getString("lname");
                String addr = rs.getString("addr");
                System.out.println("First Name: " + fname + ", Last Name: " + lname + ", Address: " + addr);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}                      

輸出

獲得的輸出如下所示：

Table records: 
First Name: Peter, Last Name: Jones, Address: 482 Rush St., Apt. 402
First Name: Bernice, Last Name: Smith, Address: 916 Maple Dr.
First Name: Grace, Last Name: Peterson, Address: 16055 Seminole Ave.
First Name: Walter, Last Name: Brown, Address: 8602 1st St.
First Name: ReddyParts, Inc., Last Name: , Address: 38 Industrial Blvd.
First Name: Parts-to-go, Ltd., Last Name: , Address: 213B Commerce Park.        

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
union_query = f"""
SELECT fname, lname, addr FROM prospect
    UNION
    SELECT first_name, last_name, address FROM customer
    UNION
    SELECT company, '', street FROM vendor;
"""
cursorObj.execute(union_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()                      

輸出

以上程式碼的輸出如下：

('Peter', 'Jones', '482 Rush St., Apt. 402')
('Bernice', 'Smith', '916 Maple Dr.')
('Grace', 'Peterson', '16055 Seminole Ave.')
('Walter', 'Brown', '8602 1st St.')
('ReddyParts, Inc.', '', '38 Industrial Blvd.')
('Parts-to-go, Ltd.', '', '213B Commerce Park.')