首页 > 数据库 >解析数据库联结:应用与实践中的 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN 与 CROSS JOIN

解析数据库联结:应用与实践中的 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN 与 CROSS JOIN

时间:2023-12-13 21:04:51浏览次数:78  
标签:RIGHT JOIN name employees CROSS departments department id

在PostgreSQL中,JOIN是一个关键的数据库操作,用于合并多个表中的数据,根据特定条件进行关联。JOIN操作包括多种类型,每种类型都有其特定的用途和语法。下面将深入探讨PostgreSQL中所有类型的JOIN查询,并提供详细示例以帮助读者理解和应用这些操作。


1. 创建示例表

创建两个示例表employeesdepartments,并添加一些示例数据。

首先,创建 employees 表:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INTEGER
);

INSERT INTO employees (employee_name, department_id) VALUES
    ('John', 1),
    ('Alice', 2),
    ('Bob', 1),
    ('Emma', 3);

接着,创建 departments 表:

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100)
);

INSERT INTO departments (department_name) VALUES
    ('IT'),
    ('HR'),
    ('Marketing');

展示这两个表中的数据如下:

SELECT * FROM employees;

这将显示 employees 表中的数据:

employee_id

employee_name

department_id

1

John

1

2

Alice

2

3

Bob

1

4

Emma

3

SELECT * FROM departments;

这将显示 departments 表中的数据:

department_id

department_name

1

IT

2

HR

3

Marketing

以上是创建 employeesdepartments 表,并添加了一些示例数据。

2. INNER JOIN

INNER JOIN 用于从两个表中选择满足指定连接条件的行。

语法

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

对于我们的示例表,让我们使用 INNER JOIN 找出每个员工所属的部门信息。

示例

SELECT employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

结果

employee_name

department_name

John

IT

Alice

HR

Bob

IT

Emma

Marketing

详细解释

  • SELECT employees.employee_name, departments.department_name:指定要选择的列。
  • FROM employees:指定主表。
  • INNER JOIN departments:指定要连接的表。
  • ON employees.department_id = departments.department_id:定义连接条件,此处是员工表中的 department_id 与部门表中的 department_id 匹配。

应用场景

  1. 关联查询:当需要将两个或多个表中的数据关联起来时,INNER JOIN 允许根据共同的列将这些表连接在一起,提供了完整的关联数据集。
  2. 数据筛选:通过 INNER JOIN,可以筛选出两个表中互相匹配的数据,只返回符合连接条件的记录,排除不匹配的数据。
  3. 获取相关信息:INNER JOIN 可以用于获取有关联的信息,比如员工和部门之间的关系,订单和客户之间的关系等。

使用建议

  1. 谨慎选择连接条件:确保连接条件准确,以避免错误的匹配或过多的数据返回。
  2. 优化查询性能:在大型数据库中,使用索引、避免多余的列、尽量减少连接表的数量等方法可以提高查询性能。
  3. 理解数据模型:对数据模型有深入理解可以更好地选择适当的连接方式,确保查询结果符合预期。

INNER JOIN 对于连接两个表并返回匹配行非常有用。它仅返回两个表中满足连接条件的数据,提供了一个强大而灵活的查询方式,能够轻松地从多个相关联的表中获取所需的信息。

3. LEFT JOIN

LEFT JOIN 用于从左表中选择所有记录,并匹配右表中满足指定连接条件的行。

语法

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

让我们使用 LEFT JOIN 找出每个员工以及他们所属的部门信息,即使员工没有被分配到任何部门。

示例

SELECT employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

结果

employee_name

department_name

John

IT

Alice

HR

Bob

IT

Emma

Marketing

Sam

NULL

详细解释

  • SELECT employees.employee_name, departments.department_name:指定要选择的列。
  • FROM employees:指定主表。
  • LEFT JOIN departments:指定要连接的表。
  • ON employees.department_id = departments.department_id:定义连接条件,此处是员工表中的 department_id 与部门表中的 department_id 匹配。
  • Sam | NULL:这表示 Sam 没有分配到任何部门,因此部门名显示为 NULL。

应用场景

  1. 获取左表所有数据:LEFT JOIN 用于保留左表(主表)中的所有记录,无论右表是否存在匹配的数据。这对于需要左表全部数据的情况非常有用。
  2. 处理缺失的关联数据:当需要检索左表数据,并且右表中的数据可能缺失时,LEFT JOIN 可以返回左表数据,并将右表中无匹配数据的行填充为 NULL 值。
  3. 信息展示:在展示信息时,LEFT JOIN 可以确保即使右表中没有匹配的数据,左表的信息也能够显示,从而提供完整的数据视图。

使用建议

  1. 理解数据结构:深入了解数据模型,确保左右表的连接条件是准确的,避免不必要的数据混合或遗漏。
  2. 合理处理 NULL 值:在左表中没有匹配到右表数据时,结果会显示 NULL 值,需要在应用程序中进行合理的处理。
  3. 性能优化:在大型数据库中,LEFT JOIN 可能导致返回的数据量很大,因此需要合理使用索引和优化查询以提高性能。

LEFT JOIN 允许选择左表的所有行,无论右表是否存在匹配行。

4. RIGHT JOIN

RIGHT JOIN 用于从右表中选择所有记录,并匹配左表中满足指定连接条件的行。

语法

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

让我们使用 RIGHT JOIN 找出每个部门以及该部门下的员工信息,即使某个部门没有员工。

示例

SELECT employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

结果

employee_name

department_name

John

IT

Alice

HR

Bob

IT

Emma

Marketing

NULL

Sales

详细解释

  • SELECT employees.employee_name, departments.department_name:指定要选择的列。
  • FROM employees:指定主表。
  • RIGHT JOIN departments:指定要连接的表。
  • ON employees.department_id = departments.department_id:定义连接条件,此处是员工表中的 department_id 与部门表中的 department_id 匹配。
  • NULL | Sales:这表示 Sales 部门没有任何员工,因此员工名显示为 NULL。

应用场景

  1. 获取右表所有数据:RIGHT JOIN 用于保留右表(次要表)中的所有记录,无论左表是否存在匹配的数据。这对于需要右表全部数据的情况非常有用。
  2. 查找缺失的关联数据:当需要检索右表数据,并且左表中的数据可能缺失时,RIGHT JOIN 可以返回右表数据,并将左表中无匹配数据的行填充为 NULL 值。
  3. 信息展示:在展示信息时,RIGHT JOIN 可以确保即使左表中没有匹配的数据,右表的信息也能够显示,从而提供完整的数据视图。

使用建议

  1. 理解数据结构:深入了解数据模型,确保左右表的连接条件是准确的,避免不必要的数据混合或遗漏。
  2. 合理处理 NULL 值:在右表中没有匹配到左表数据时,结果会显示 NULL 值,需要在应用程序中进行合理的处理。
  3. 性能优化:在大型数据库中,RIGHT JOIN 可能导致返回的数据量很大,因此需要合理使用索引和优化查询以提高性能。

RIGHT JOIN 允许选择右表的所有行,无论左表是否存在匹配行。

5. FULL OUTER JOIN

FULL OUTER JOIN 返回两个表中的所有行,如果在另一个表中没有匹配的行,则会显示 NULL 值。

语法

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

让我们使用 FULL OUTER JOIN 找出每个员工以及他们所属的部门信息,同时显示没有匹配的情况。

示例

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

结果

employee_name

department_name

John

IT

Alice

HR

Bob

IT

Emma

Marketing

Sam

NULL

NULL

Sales

详细解释

  • SELECT employees.employee_name, departments.department_name:指定要选择的列。
  • FROM employees:指定主表。
  • FULL OUTER JOIN departments:指定要连接的表。
  • ON employees.department_id = departments.department_id:定义连接条件,此处是员工表中的 department_id 与部门表中的 department_id 匹配。
  • Sam | NULL:这表示 Sam 没有分配到任何部门,因此部门名显示为 NULL。同时,NULL | Sales 表示 Sales 部门没有任何员工。

应用场景

  1. 获取两个表的所有数据:FULL OUTER JOIN 返回左表和右表中的所有记录,即使在另一个表中没有匹配的行,也会将所有行都包括在结果中。
  2. 处理缺失的关联数据:对于需要同时检索两个表数据,并且希望展示缺失关联的情况,FULL OUTER JOIN 可以返回左右表中无匹配数据的行,并将匹配的数据进行关联。
  3. 信息展示:在需要展示完整的数据视图,同时考虑两个表的所有记录时,FULL OUTER JOIN 提供了一个完整的数据集。

使用建议

  1. 处理 NULL 值:FULL OUTER JOIN 可能导致结果中出现大量的 NULL 值,需要在应用程序中进行合理的处理,以便展示或处理这些空值。
  2. 性能优化:在大型数据库中,FULL OUTER JOIN 可能导致返回的数据量非常庞大,需要合理使用索引和优化查询以提高性能。
  3. 了解数据关系:深入了解数据模型和表之间的关系,以确保 FULL OUTER JOIN 的连接条件和预期结果符合预期。

FULL OUTER JOIN 允许选择两个表的所有行,并显示没有匹配的情况。这种查询方法对于需要显示两个表中所有数据并保留所有信息的情况非常有用。

6. CROSS JOIN

CROSS JOIN 返回两个表中所有可能的组合,即每个表的行与另一个表的每一行进行组合。

语法

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

让我们使用 CROSS JOIN 找出所有可能的员工和部门组合。

示例

SELECT employees.employee_name, departments.department_name
FROM employees
CROSS JOIN departments;

结果

employee_name

department_name

John

IT

John

HR

John

Marketing

Alice

IT

Alice

HR

Alice

Marketing

Bob

IT

Bob

HR

Bob

Marketing

Emma

IT

Emma

HR

Emma

Marketing

详细解释

  • SELECT employees.employee_name, departments.department_name:指定要选择的列。
  • FROM employees:指定第一个表。
  • CROSS JOIN departments:指定要连接的表。
  • 这个查询返回了每个员工与每个部门的所有可能组合,即表 employees 中的每一行都与表 departments 中的每一行进行了组合。

应用场景

  1. 组合数据:CROSS JOIN 用于生成两个或多个表的所有可能的组合,无论这些组合是否有实际意义。这在某些情况下对于生成测试数据或计算所有可能性非常有用。
  2. 数据排列:当需要展示多个表的所有可能组合时,CROSS JOIN 可以用于生成排列方式,展示出所有可能的数据组合。
  3. 分析复杂情况:在一些特殊情况下,CROSS JOIN 可以用于解决复杂的数据排列和组合问题,为进一步分析提供基础。

使用建议

  1. 小心数据量:CROSS JOIN 可能会生成非常庞大的结果集,特别是当表中数据量较大时,请谨慎使用,并确保了解结果可能变得巨大的情况。
  2. 合理用途:CROSS JOIN 通常用于特定的需求,比如生成测试数据或者处理特定的排列组合问题,确保只在合适的情况下使用。
  3. 了解数据模型:深入了解数据模型和表之间的关系,以确保 CROSS JOIN 的结果符合预期,不会产生意想不到的数据组合。

CROSS JOIN 返回两个表的笛卡尔积,对于需要所有可能的组合情况非常有用。但请注意,当表中行数较大时,CROSS JOIN 可能导致结果集非常庞大。

7. 总结

通过对 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN 和 CROSS JOIN 的全面解析,深入理解这些关键联结操作的应用场景和使用方式。在实际数据库查询中,选择合适的联结方式能够帮助我们处理不同的数据关系,提取出所需的关联信息,进一步加强了我们对数据库查询和数据处理的能力。精通这些联结方式,将使我们在处理数据库中的复杂关联数据时游刃有余,为数据分析和决策提供更加可靠的支持。



标签:RIGHT,JOIN,name,employees,CROSS,departments,department,id
From: https://blog.51cto.com/u_16170163/8806022

相关文章

  • 联表查询joinON详解
    --联表查询join--查询参加了考试的同学(学号,姓名,成绩)ALTERTABLE`student2_copy`ADDgradeINT(100)UPDATE`student2_copy`SET`grade`=66WHEREid=1;UPDATE`student2_copy`SET`grade`=89WHEREid<8ANDid>2--添加成绩SELECT*FROM`student2`;SELECT*FROM`student......
  • cross-request谷歌插件笔记
    使用yapi在线文档,高级mock点运行时提示需要cross-request插件,那么就下载一个。链接:https://pan.baidu.com/s/1FKDhtYymw67T1jc7dW7TWw提取码:nf4e然后谷歌扩展安装就行。设置|扩展程序|开发者模式打开|加载已解压的扩展程序|选中cross-request解压的文件夹。也有可能会提......
  • PostgreSQL 执行动态crosstab查询
    在本文中,我们将介绍如何在PostgreSQL中执行动态crosstab查询。crosstab查询是一种将行转换为列的查询方式,常用于生成交叉表格。使用动态crosstab查询可以根据查询结果自动调整生成的表格结构,使其更加灵活和可扩展。 什么是动态crosstab查询?动态crosstab查询是一种使用动态SQL......
  • MySQL 数据库操作指南:LIMIT,OFFSET 和 JOIN 的使用
    限制结果您可以通过使用"LIMIT"语句来限制查询返回的记录数量。以下是一个示例,获取您自己的Python服务器中"customers"表中的前5条记录:importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",......
  • MySQL 数据库操作指南:LIMIT,OFFSET 和 JOIN 的使用
    限制结果您可以通过使用"LIMIT"语句来限制查询返回的记录数量。以下是一个示例,获取您自己的Python服务器中"customers"表中的前5条记录:importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",......
  • Thread常见方法:join 方法详解
    为什么需要join下面的代码执行,打印r是什么?staticintr=0;publicstaticvoidmain(String[]args)throwsInterruptedException{test1();}privatestaticvoidtest1()throwsInterruptedException{log.debug("开始");Threadt1=newThread(()->{log.de......
  • How to get printk format specifiers right (如何正确使用printk格式说明符)(翻译 by
    原文:https://www.kernel.org/doc/html/latest/core-api/printk-formats.html#printk-specifiers如何正确使用printk格式说明符整数类型如果变量是Type类型,则使用printk格式说明符:signedchar%d或%hhxunsignedchar%u或%xchar......
  • Python - pandas DataFrame数据的合并与拼接(merge、join、concat)
    Python-pandasDataFrame数据的合并与拼接(merge、join、concat)0概述pandas包的merge、join、concat方法可以完成数据的合并和拼接。merge方法主要基于两个dataframe的共同列进行合并;join方法主要基于两个dataframe的索引进行合并;concat方法是对series或dataframe进行行......
  • mysql join
    select*froma,b 等同于innerjoin join有两种执行方式NestedLoopJoin(嵌套循环连接):这是最简单和最基础的连接算法。它会遍历一个表中的每一条记录,并与另一个表进行比较,以查找匹配的记录。这种方法适用于较小的表或者没有索引的情况。其中NestedLoopJoin有包括三种......
  • 2023ICCV_Feature Modulation Transformer: Cross-Refinement of Global Representati
    一.Motivation1.transformer的工作主要集中在设计transformer块以获得全局信息,而忽略了合并高频先验的潜力2. 关于频率对性能的影响的详细分析有限(Additionally,there islimiteddetailedanalysisoftheimpactoffrequencyon performance.)注: (1) 图说明:随着高......