PostgreSQL入门到实战-第二十三弹

PostgreSQL中表连接操作(七)

使用PostgreSQL FULL OUTER JOIN从两个表中查询数据

官网地址

声明: 由于操作系统, 版本更新等原因, 文章所列内容不一定100%复现, 还要以官方信息为准

https://www.postgresql.org/

PostgreSQL概述

PostgreSQL: 世界上最先进的开源关系数据库。

PostgreSQL中命令理论

• FULL OUTER JOIN组合来自两个表的数据，并返回两个表中的所有行，包括两侧的匹配行和不匹配行。

• 换句话说，FULL OUTER JOIN组合了左联接和右联接的结果。

• 基础语法

  SELECT select_list
  FROM table1
  FULL OUTER JOIN table2 
     ON table1.column_name = table2.column_name;
  

PostgreSQL中命令实战

数据库样例数据来源: https://blog.csdn.net/zwq56693/article/details/137473602

• 数据准备

  CREATE TABLE departments (
    department_id serial PRIMARY KEY, 
    department_name VARCHAR (255) NOT NULL
  );
  CREATE TABLE employees (
    employee_id serial PRIMARY KEY, 
    employee_name VARCHAR (255), 
    department_id INTEGER
  );
  INSERT INTO departments (department_name) 
  VALUES 
    ('Sales'), 
    ('Marketing'), 
    ('HR'), 
    ('IT'), 
    ('Production');
  INSERT INTO employees (employee_name, department_id) 
  VALUES 
    ('Bette Nicholson', 1), 
    ('Christian Gable', 1), 
    ('Joe Swank', 2), 
    ('Fred Costner', 3), 
    ('Sandra Kilmer', 4), 
    ('Julia Mcqueen', NULL);
  SELECT * FROM departments;
  

• 使用FULL OUTER JOIN从employees表和departments表中查询数据

  SELECT 
    employee_name, 
    department_name 
  FROM 
    employees e 
  FULL OUTER JOIN departments d 
    ON d.department_id = e.department_id;
  

• 使用FULL OUTER JOIN和WHERE子句来查找没有任何员工的部门

  SELECT 
    employee_name, 
    department_name 
  FROM 
    employees e 
  FULL OUTER JOIN departments d 
    ON d.department_id = e.department_id 
  WHERE 
    employee_name IS NULL;
  

• 使用带有WHERE子句的FULL OUTER JOIN cluase来查找不属于任何部门的员工

  SELECT 
    employee_name, 
    department_name 
  FROM 
    employees e 
  FULL OUTER JOIN departments d 
    ON d.department_id = e.department_id 
  WHERE 
    department_name IS NULL;
  

更新计划

欲知后事如何, 请听下回分解