《MYSQL 8 教學–DAY04–聯合表操作-談INNER JOIN、LEFT JOIN、RIGHT JOIN與子查詢》操作分享 | 學習筆記

本次的教學，主要分享MySQL在對資料表(Table)有了基本操作的相關知識後，後續開始針對聯合表操作，在做更進階的討論，主要我們會談談INNER JOIN、LEFT JOIN、RIGHT JOIN與子查詢的綜合操作，本教學是以Mysql 8.0為出發的教學，當然一些通用語法Mysql 8.0之前的版本也適用喔，所以大家不用擔心，只要一起做，相信你也可以了解MySQL 中的資料表操作是怎麼一回事。

本教學的範例說明

延續Day03教學以電子商務線上購物所會用的相關資料表為基礎，後續會再做相關的延伸

資料庫名稱:rdpapashop

CREATE DATABASE `rdpapashop` /*!40100 COLLATE 'utf8mb4_unicode_ci' */

須建立的資料表名稱:employees員工資料表、department部門資料表

建立資料表(employees)

use `rdpapashop`;
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    age INT,
    hire_date DATE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

建立範例資料(employees)

INSERT INTO employees (first_name, last_name, department_id, salary, age, hire_date)
VALUES
    ('John', 'Doe', 1, 55000.00, 30, '2020-01-15'),
    ('Jane', 'Smith', 2, 60000.50, 28, '2019-11-20'),
    ('Michael', 'Johnson', 3, 75000.75, 35, '2018-05-10'),
    ('Emily', 'Brown', 4, 50000.25, 27, '2021-02-18'),
    ('William', 'Jones', 1, 60000.00, 29, '2017-09-03'),
    ('Olivia', 'Davis', 3, 70000.50, 32, '2016-12-22'),
    ('James', 'Wilson', 2, 62000.75, 31, '2020-08-14'),
    ('Sophia', 'Taylor', 4, 51000.25, 26, '2019-03-25'),
    ('Benjamin', 'Anderson', 3, 77000.00, 33, '2015-06-12'),
    ('Emma', 'Martinez', 1, 58000.50, 28, '2022-04-30'),
    ('Mason', 'Hernandez', 4, 52000.75, 27, '2018-10-05'),
    ('Ava', 'Lopez', 2, 61000.25, 30, '2020-07-02'),
    ('Liam', 'Garcia', 3, 76000.00, 34, '2017-02-11'),
    ('Isabella', 'Rodriguez', 1, 59000.50, 29, '2019-09-19'),
    ('Noah', 'Martinez', 4, 53000.75, 26, '2021-01-08'),
    ('Oliver', 'Gonzalez', 3, 78000.25, 35, '2016-04-17'),
    ('Sophia', 'Hernandez', 2, 63000.50, 31, '2020-11-27'),
    ('Mia', 'Smith', 1, 60000.00, 28, '2018-08-07'),
    ('Liam', 'Anderson', 3, 79000.75, 36, '2017-03-15'),
    ('Ava', 'Johnson', 4, 54000.25, 27, '2019-06-28'),
    ('Lucas', 'Brown', 1, 65000.75, 29, '2022-01-05'),
    ('Harper', 'Garcia', 2, 72000.00, 32, '2017-11-12'),
    ('Evelyn', 'Davis', 3, 59000.50, 28, '2018-03-19'),
    ('Logan', 'Taylor', 4, 56000.25, 27, '2021-04-14'),
    ('Avery', 'Martinez', 1, 61000.00, 30, '2019-09-22'),
    ('Jackson', 'Wilson', 2, 68000.75, 33, '2016-02-28'),
    ('Penelope', 'Lopez', 3, 60000.25, 28, '2020-05-17'),
    ('Sebastian', 'Hernandez', 4, 57000.50, 27, '2021-08-10'),
    ('Luna', 'Gonzalez', 1, 62000.00, 29, '2019-12-03'),
    ('Mateo', 'Johnson', 2, 70000.75, 34, '2017-04-25'),
    ('Grace', 'Smith', 3, 61000.50, 30, '2018-06-28');

建立資料表(department)

use `rdpapashop`;
CREATE TABLE departments(
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

建立範例資料(department)

INSERT INTO departments (department_name)
VALUES
    ('HR'),
    ('Finance'),
    ('Engineering'),
    ('Sales'),
    ('Marketing'),
    ('IT'),
    ('Customer Support'),
    ('Research and Development'),
    ('Quality Assurance'),
    ('Operations'),
    ('Legal'),
    ('Public Relations'),
    ('Production'),
    ('Supply Chain'),
    ('Administration'),
    ('Business Development'),
    ('Design'),
    ('Training'),
    ('Health and Safety'),
    ('Facilities');

✒RDPAPA Tip

安裝好以上範例資料後，我們就可以開始我們的教學練習，本範例資料為employees與departments的資料表，包含員工的基本資訊，如姓名、部門、薪水等欄位

操作實例

查詢每位員工所在部門的名稱-使用INNER JOIN

SELECT 
		e.first_name, e.last_name, d.department_name
FROM 
		employees e
INNER JOIN 
		departments d
ON 
		e.department_id = d.department_id;

#這個查詢使用INNER JOIN操作，將employees和departments資料表聯合
#起來，並根據department_id這個共同的欄位匹配記錄。輸出結果將包含
#每位員工的姓名和他們所在部門的名稱。
#RDPAPA Tip d,e為別名你也可以取任何你想要叫的名稱以縮短整個查詢句的長度

=>查詢結果

查詢每個部門擁有的員工數-使用LEFT JOIN

SELECT 
			d.department_name, COUNT(e.employee_id) AS employee_count
FROM 
			departments d
LEFT JOIN 
			employees e
ON 
			d.department_id = e.department_id
GROUP BY 
			d.department_name;

#這個查詢使用LEFT JOIN操作，將departments資料表和employees資料表聯合
#起來，並計算每個部門的員工數量。即使某些部門沒有員工，它們也會在結果中
#顯示，並顯示員工數量為0。。
#RDPAPA Tip d,e為別名你也可以取任何你想要叫的名稱以縮短整個查詢句的長度

=>查詢結果

查詢每個部門以及該部門下的員工姓名-使用RIGHT JOIN

SELECT 
		d.department_name, e.first_name, e.last_name
FROM 
		employees e
RIGHT JOIN 
		departments d
ON 
		e.department_id = d.department_id;

#這個查詢使用 RIGHT JOIN 操作，它會返回所有departments資料表中
#的部門，並顯示該部門中的員工（如果有的話）。如果某個部門中沒有員工
#那麼結果集中將包含該部門的名稱以及員工相關的欄位，但員工欄位將為 NULL。
#RDPAPA Tip d,e為別名你也可以取任何你想要叫的名稱以縮短整個查詢句的長度

=>查詢結果

查詢月薪高於公司平均薪資的員工列表-子查詢

SELECT 
		first_name, last_name,salary
FROM 
		employees
WHERE 
		salary > (SELECT AVG(salary)  FROM employees);

#使用AVG函數找到公司的平均薪資。

=>查詢結果

查詢每個部門中薪水高於平均薪水的員工數-INNER JOIN+子查詢

SELECT 
		d.department_name, COUNT(*) AS above_avg_salary_count
FROM 
		employees e
INNER JOIN
		departments d
ON
		d.department_id=e.department_id
WHERE 
		salary > (SELECT AVG(salary) FROM employees)
GROUP BY d.department_name;

#這個查詢首先計算整個公司的平均薪水，然後找到薪水高於平均薪水的員工數，並根據部門分組。
#RDPAPA Tip d,e為別名你也可以取任何你想要叫的名稱以縮短整個查詢句的長度

=>查詢結果

查詢計算每個部門的平均薪水，然後與departments資料表JOIN-INNER JOIN+子查詢

SELECT 
		d.department_name, avg_salary
FROM 
		departments d
JOIN (SELECT department_id, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department_id) subqry
ON d.department_id = subqry.department_id;

=>查詢結果

Reference

• MySQL 8.0 Reference Manual JOIN Clause