sql 多表查询

sql的多表查询。详细介绍与分解。 多表查询 等连接 通过两个表具有相同意义的列,建立连接条件. 查询结果只显示两个列中的值是等值条件的行数据 表中同名列被选择时必须添加表名前缀进行修饰 否则无法确定这一列是属于哪个表 不等连接 A表中的某列数据和B表中一列或多列的关系是非等值关系,大于,小于,不等于,等条件都属于不等连接的范畴 自连接 数据都来自一张表,所以在from字句中需要对表添加别名,添加表别名后才能合法化的引用表中的列名. 本质就是将一张表虚拟成了两张表 外连接 即是选择出满足等连接条件及其以外的行 (+)修饰符号用法:放置在选出结果只包含等连接的列后,则另一列的结果就是等值行+非等值行 ### SQL多表查询详解 #### 一、等连接（Equal Join） 等连接是最常见的多表连接方式之一，它通过两个表中具有相同意义的列来建立连接条件。具体来说，等连接只返回那些两个表中指定列的值相等的行。 **特点：** - **查询结果**：只包含那些在连接条件中两个列值相等的数据行。 - **列名冲突处理**：如果两个表中存在同名列，则必须在列名前加上表名前缀，以明确指出该列属于哪个表。 **示例代码**： ```sql SELECT * FROM dept, emp WHERE dept.deptno = emp.deptno; ``` 这条SQL语句从`dept`表和`emp`表中选择所有列，并通过`dept.deptno = emp.deptno`这一条件限制了输出结果，仅显示那些`deptno`列值相等的行。 #### 二、不等连接（Non-equal Join） 不等连接指的是当两个表中的某一列或某些列之间存在非等值关系时所使用的连接方式，包括大于、小于、不等于等条件。 **特点：** - **查询结果**：返回满足特定条件（如大于、小于等）的行。 - **应用场景**：通常用于比较两个表中的不同列，以获取满足特定条件的数据行。 **示例代码**： 假设`dept`表中有`budget`列，表示每个部门的预算，而`emp`表中有`salary`列，表示每个员工的薪资。若想查询所有薪资高于所在部门平均预算的员工信息，可以使用以下SQL语句： ```sql SELECT e.empno, e.ename, d.budget, e.salary FROM emp e, dept d WHERE e.deptno = d.deptno AND e.salary > d.budget / COUNT(d.deptno); ``` 注意这里使用了聚合函数`COUNT()`来计算部门的平均预算。 #### 三、自连接（Self Join） 自连接是指在一个查询中使用同一张表两次的情况，通常需要为表指定不同的别名，以便合法地引用表中的列。 **特点：** - **应用场景**：通常用于查询表中的层级关系或者重复数据。 - **示例**：例如，如果想查找所有员工的上级是谁，可以使用自连接。 **示例代码**： 假设`emp`表中有`mgr`列，表示每个员工的上级编号。可以使用以下SQL语句： ```sql SELECT e1.ename AS 'Employee', e2.ename AS 'Manager' FROM emp e1, emp e2 WHERE e1.mgr = e2.empno; ``` 这里`e1`和`e2`是`emp`表的不同别名。 #### 四、外连接（Outer Join） 外连接是指除了等连接条件下的行之外，还包含了至少一个表中的额外行。外连接分为左外连接、右外连接和全外连接。 **特点：** - **左外连接**：返回左表的所有行以及右表中匹配的行；如果没有匹配，则返回NULL。 - **右外连接**：返回右表的所有行以及左表中匹配的行；如果没有匹配，则返回NULL。 - **全外连接**：返回两个表中的所有行，无论是否匹配。 **示例代码**： - 左外连接示例： ```sql SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno; ``` - 右外连接示例： ```sql SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno; ``` - 全外连接示例： ```sql SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno; ``` ### 总结 以上介绍了几种常用的SQL多表查询方式，包括等连接、不等连接、自连接和外连接。每种连接方式都有其特定的应用场景和特点，在实际开发过程中根据需求选择合适的连接类型是非常重要的。通过合理运用这些连接方式，可以有效地从多个表中提取所需的信息，提高查询效率和准确性。