行业知识
【六】【SQL】多表查询，笛卡尔积
2026-11-03 14:21:43
来源：互联网转载
���卡尔积
（）笛卡尔积发生在当你在查询中将两个或多个表进行交叉连接（CROSS JOIN）或者没有指定任何连接条件时。假设第一个表有M行，第二个表有N行，那么结果集将包含M x N个记录。在大多数情况下，笛卡尔积并不是你想要的结果，因为它会产生大量的无关数据。但是，理解它是如何发生的对于避免无意中产生笛卡尔积是很有帮助的。
显示雇员名、雇员工资以及所在部门的名字
因为上面的数据来自于EMP和DEPT表，因此要联合查询
（）mysql> select *from emp;+--------+--------+-----------+------+---------------------+---------+---------+--------+| empno | ename | job | mgr | hiredate | sal | comm | deptno |+--------+--------+-----------+------+---------------------+---------+---------+--------+| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 || 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 || 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 || 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 || 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 || 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 || 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 || 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 || 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 || 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 || 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |+--------+--------+-----------+------+---------------------+---------+---------+--------+14 rows in set (0.00 sec)mysql> select *from dept;+--------+------------+----------+| deptno | dname | loc |+--------+------------+----------+| 10 | ACCOUNTING | NEW YORK || 20 | RESEARCH | DALLAS || 30 | SALES | CHICAGO || 40 | OPERATIONS | BOSTON |+--------+------------+----------+4 rows in set (0.00 sec)mysql> select *from emp,dept;+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+| empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc |+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK || 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 30 | SALES | CHICAGO || 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 40 | OPERATIONS | BOSTON || 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 10 | ACCOUNTING | NEW YORK || 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 20 | RESEARCH | DALLAS || 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO || 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 40 | OPERATIONS | BOSTON || 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 10 | ACCOUNTING | NEW YORK || 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 20 | RESEARCH | DALLAS || 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO || 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 40 | OPERATIONS | BOSTON || 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK || 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 30 | SALES | CHICAGO || 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 40 | OPERATIONS | BOSTON || 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 10 | ACCOUNTING | NEW YORK || 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 20 | RESEARCH | DALLAS || 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO || 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 40 | OPERATIONS | BOSTON || 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 10 | ACCOUNTING | NEW YORK || 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 20 | RESEARCH | DALLAS || 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO || 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 40 | OPERATIONS | BOSTON || 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK || 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 20 | RESEARCH | DALLAS || 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 30 | SALES | CHICAGO || 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 40 | OPERATIONS | BOSTON || 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK || 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 30 | SALES | CHICAGO || 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 40 | OPERATIONS | BOSTON || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 20 | RESEARCH | DALLAS || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 30 | SALES | CHICAGO || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 40 | OPERATIONS | BOSTON || 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 10 | ACCOUNTING | NEW YORK || 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 20 | RESEARCH | DALLAS || 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO || 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 40 | OPERATIONS | BOSTON || 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK || 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 30 | SALES | CHICAGO || 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 40 | OPERATIONS | BOSTON || 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 10 | ACCOUNTING | NEW YORK || 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 20 | RESEARCH | DALLAS || 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO || 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 40 | OPERATIONS | BOSTON || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 30 | SALES | CHICAGO || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 40 | OPERATIONS | BOSTON || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 20 | RESEARCH | DALLAS || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 30 | SALES | CHICAGO || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 40 | OPERATIONS | BOSTON |+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+56 rows in set (0.02 sec)mysql> select *from emp,dept where emp.deptno=dept.deptno;+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+| empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc |+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO || 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO || 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO || 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO || 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK || 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK || 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO || 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+14 rows in set (0.00 sec)mysql> select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;+--------+---------+------------+| ename | sal | dname |+--------+---------+------------+| SMITH | 800.00 | RESEARCH || ALLEN | 1600.00 | SALES || WARD | 1250.00 | SALES || JONES | 2975.00 | RESEARCH || MARTIN | 1250.00 | SALES || BLAKE | 2850.00 | SALES || CLARK | 2450.00 | ACCOUNTING || SCOTT | 3000.00 | RESEARCH || KING | 5000.00 | ACCOUNTING || TURNER | 1500.00 | SALES || ADAMS | 1100.00 | RESEARCH || JAMES | 950.00 | SALES || FORD | 3000.00 | RESEARCH || MILLER | 1300.00 | ACCOUNTING |+--------+---------+------------+14 rows in set (0.00 sec)mysql>
这些MySQL查询示例展示了如何从emp（员工）和dept（部门）两个表中进行数据检索，以及如何通过连接条件避免生成笛卡尔积，从而获取有意义的结果。
简单的多表查询（产生笛卡尔积）: 当你执行select * from emp,dept;时，没有指定连接条件，因此结果是emp表和dept表之间的笛卡尔积。每个emp表中的行都会与dept表中的每行组合，如果emp表有14行，dept表有4行，那么结果集将有56行（14*4）。
指定连接条件的多表查询: 通过添加where emp.deptno=dept.deptno;，你明确指定了连接条件，即只有当员工所在部门编号（deptno）与部门表中的部门编号相匹配时，相应的记录才会出现在结果集中。这种方式有效地避免了笛卡尔积，只返回了相关联的记录，即每个员工及其对应的部门信息。
特定字段选择: 最后一个查询select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;进一步细化了输出，只选择了员工的姓名（ename）、薪水（sal）和部门名称（dname）。通过这种方式，可以清晰地看到每个员工所属的部门及其薪水，而不是返回两个表的所有列，这使得结果更加简洁和有用。
显示部门号为10的部门名，员工名和工资
mysql> select *from emp,dept where emp.deptno=dept.deptno;+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+| empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc |+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO || 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO || 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO || 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO || 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK || 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK || 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO || 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+14 rows in set (0.00 sec)mysql> select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;+--------+---------+------------+| ename | sal | dname |+--------+---------+------------+| SMITH | 800.00 | RESEARCH || ALLEN | 1600.00 | SALES || WARD | 1250.00 | SALES || JONES | 2975.00 | RESEARCH || MARTIN | 1250.00 | SALES || BLAKE | 2850.00 | SALES || CLARK | 2450.00 | ACCOUNTING || SCOTT | 3000.00 | RESEARCH || KING | 5000.00 | ACCOUNTING || TURNER | 1500.00 | SALES || ADAMS | 1100.00 | RESEARCH || JAMES | 950.00 | SALES || FORD | 3000.00 | RESEARCH || MILLER | 1300.00 | ACCOUNTING |+--------+---------+------------+14 rows in set (0.00 sec)mysql> select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;+--------+---------+------------+| ename | sal | dname |+--------+---------+------------+| CLARK | 2450.00 | ACCOUNTING || KING | 5000.00 | ACCOUNTING || MILLER | 1300.00 | ACCOUNTING |+--------+---------+------------+3 rows in set (0.00 sec)mysql> select ename,sal,dname,deptno from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;ERROR 1052 (23000): Column 'deptno' in field list is ambiguousmysql> select ename,sal,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;+--------+---------+------------+--------+| ename | sal | dname | deptno |+--------+---------+------------+--------+| CLARK | 2450.00 | ACCOUNTING | 10 || KING | 5000.00 | ACCOUNTING | 10 || MILLER | 1300.00 | ACCOUNTING | 10 |+--------+---------+------------+--------+3 rows in set (0.00 sec)mysql>
在这些MySQL查询中，我们看到了如何通过连接两个表（emp和dept）来获取员工的信息以及他们所属的部门信息。最后两个查询特别展示了如何解决列名冲突和如何正确地引用列名以避免错误。
解决列名冲突： 在执行多表查询时，如果两个表中存在同名的列（在这个例子中是deptno），直接引用这个列名会导致“列名是模糊的”（ambiguous）错误。这是因为SQL无法判断你希望选择哪个表中的deptno列。
错误示例： 查询select ename,sal,dname,deptno from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;失败并报错ERROR 1052 (23000): Column 'deptno' in field list is ambiguous。这是因为deptno同时存在于emp和dept表中，而查询没有指明应该从哪个表中选择该列。
解决方法： 通过在列名前加上表名或别名来解决这个问题。如select ename,sal,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;明确指出选择emp.deptno，从而解决了列名冲突问题，并成功执行了查询。
显示每个员工的姓名、工资和工资级别
mysql> select *from salgrade;+-------+-------+-------+| grade | losal | hisal |+-------+-------+-------+| 1 | 700 | 1200 || 2 | 1201 | 1400 || 3 | 1401 | 2000 || 4 | 2001 | 3000 || 5 | 3001 | 9999 |+-------+-------+-------+5 rows in set (0.00 sec)mysql> select *from emp,salgrade;+--------+--------+-----------+------+---------------------+---------+---------+--------+-------+-------+-------+| empno | ename | job | mgr | hiredate | sal | comm | deptno | grade | losal | hisal |+--------+--------+-----------+------+---------------------+---------+---------+--------+-------+-------+-------+| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 1 | 700 | 1200 || 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 2 | 1201 | 1400 || 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 3 | 1401 | 2000 || 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 4 | 2001 | 3000 || 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 5 | 3001 | 9999 || 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 1 | 700 | 1200 || 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 2 | 1201 | 1400 || 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 3 | 1401 | 2000 || 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 4 | 2001 | 3000 || 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 5 | 3001 | 9999 || 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 1 | 700 | 1200 || 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 2 | 1201 | 1400 || 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 3 | 1401 | 2000 || 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 4 | 2001 | 3000 || 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 5 | 3001 | 9999 || 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 1 | 700 | 1200 || 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 2 | 1201 | 1400 || 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 3 | 1401 | 2000 || 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 4 | 2001 | 3000 || 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 5 | 3001 | 9999 || 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 1 | 700 | 1200 || 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 2 | 1201 | 1400 || 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 3 | 1401 | 2000 || 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 4 | 2001 | 3000 || 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 5 | 3001 | 9999 || 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 1 | 700 | 1200 || 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 2 | 1201 | 1400 || 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 3 | 1401 | 2000 || 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 4 | 2001 | 3000 || 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 5 | 3001 | 9999 || 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 1 | 700 | 1200 || 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 2 | 1201 | 1400 || 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 3 | 1401 | 2000 || 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 4 | 2001 | 3000 || 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 5 | 3001 | 9999 || 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 1 | 700 | 1200 || 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 2 | 1201 | 1400 || 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 3 | 1401 | 2000 || 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 4 | 2001 | 3000 || 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 5 | 3001 | 9999 || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 1 | 700 | 1200 || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 2 | 1201 | 1400 || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 3 | 1401 | 2000 || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 4 | 2001 | 3000 || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 5 | 3001 | 9999 || 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 1 | 700 | 1200 || 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 2 | 1201 | 1400 || 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 3 | 1401 | 2000 || 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 4 | 2001 | 3000 || 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 5 | 3001 | 9999 || 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 1 | 700 | 1200 || 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 2 | 1201 | 1400 || 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 3 | 1401 | 2000 || 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 4 | 2001 | 3000 || 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 5 | 3001 | 9999 || 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 1 | 700 | 1200 || 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 2 | 1201 | 1400 || 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 3 | 1401 | 2000 || 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 4 | 2001 | 3000 || 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 5 | 3001 | 9999 || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 1 | 700 | 1200 || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 2 | 1201 | 1400 || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 3 | 1401 | 2000 || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 4 | 2001 | 3000 || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 5 | 3001 | 9999 || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 1 | 700 | 1200 || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 2 | 1201 | 1400 || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 3 | 1401 | 2000 || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 4 | 2001 | 3000 || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 5 | 3001 | 9999 |+--------+--------+-----------+------+---------------------+---------+---------+--------+-------+-------+-------+70 rows in set (0.00 sec)mysql> select ename,sal ,grade from emp,salgrade;+--------+---------+-------+| ename | sal | grade |+--------+---------+-------+| SMITH | 800.00 | 1 || SMITH | 800.00 | 2 || SMITH | 800.00 | 3 || SMITH | 800.00 | 4 || SMITH | 800.00 | 5 || ALLEN | 1600.00 | 1 || ALLEN | 1600.00 | 2 || ALLEN | 1600.00 | 3 || ALLEN | 1600.00 | 4 || ALLEN | 1600.00 | 5 || WARD | 1250.00 | 1 || WARD | 1250.00 | 2 || WARD | 1250.00 | 3 || WARD | 1250.00 | 4 || WARD | 1250.00 | 5 || JONES | 2975.00 | 1 || JONES | 2975.00 | 2 || JONES | 2975.00 | 3 || JONES | 2975.00 | 4 || JONES | 2975.00 | 5 || MARTIN | 1250.00 | 1 || MARTIN | 1250.00 | 2 || MARTIN | 1250.00 | 3 || MARTIN | 1250.00 | 4 || MARTIN | 1250.00 | 5 || BLAKE | 2850.00 | 1 || BLAKE | 2850.00 | 2 || BLAKE | 2850.00 | 3 || BLAKE | 2850.00 | 4 || BLAKE | 2850.00 | 5 || CLARK | 2450.00 | 1 || CLARK | 2450.00 | 2 || CLARK | 2450.00 | 3 || CLARK | 2450.00 | 4 || CLARK | 2450.00 | 5 || SCOTT | 3000.00 | 1 || SCOTT | 3000.00 | 2 || SCOTT | 3000.00 | 3 || SCOTT | 3000.00 | 4 || SCOTT | 3000.00 | 5 || KING | 5000.00 | 1 || KING | 5000.00 | 2 || KING | 5000.00 | 3 || KING | 5000.00 | 4 || KING | 5000.00 | 5 || TURNER | 1500.00 | 1 || TURNER | 1500.00 | 2 || TURNER | 1500.00 | 3 || TURNER | 1500.00 | 4 || TURNER | 1500.00 | 5 || ADAMS | 1100.00 | 1 || ADAMS | 1100.00 | 2 || ADAMS | 1100.00 | 3 || ADAMS | 1100.00 | 4 || ADAMS | 1100.00 | 5 || JAMES | 950.00 | 1 || JAMES | 950.00 | 2 || JAMES | 950.00 | 3 || JAMES | 950.00 | 4 || JAMES | 950.00 | 5 || FORD | 3000.00 | 1 || FORD | 3000.00 | 2 || FORD | 3000.00 | 3 || FORD | 3000.00 | 4 || FORD | 3000.00 | 5 || MILLER | 1300.00 | 1 || MILLER | 1300.00 | 2 || MILLER | 1300.00 | 3 || MILLER | 1300.00 | 4 || MILLER | 1300.00 | 5 |+--------+---------+-------+70 rows in set (0.00 sec)mysql> select ename,sal ,grade from emp,salgrade where sal between losal and hisal;+--------+---------+-------+| ename | sal | grade |+--------+---------+-------+| SMITH | 800.00 | 1 || ALLEN | 1600.00 | 3 || WARD | 1250.00 | 2 || JONES | 2975.00 | 4 || MARTIN | 1250.00 | 2 || BLAKE | 2850.00 | 4 || CLARK | 2450.00 | 4 || SCOTT | 3000.00 | 4 || KING | 5000.00 | 5 || TURNER | 1500.00 | 3 || ADAMS | 1100.00 | 1 || JAMES | 950.00 | 1 || FORD | 3000.00 | 4 || MILLER | 1300.00 | 2 |+--------+---------+-------+14 rows in set (0.00 sec)mysql> select ename,sal ,grade,losal ,hisal from emp,salgrade where sal between losal and hisal;+--------+---------+-------+-------+-------+| ename | sal | grade | losal | hisal |+--------+---------+-------+-------+-------+| SMITH | 800.00 | 1 | 700 | 1200 || ALLEN | 1600.00 | 3 | 1401 | 2000 || WARD | 1250.00 | 2 | 1201 | 1400 || JONES | 2975.00 | 4 | 2001 | 3000 || MARTIN | 1250.00 | 2 | 1201 | 1400 || BLAKE | 2850.00 | 4 | 2001 | 3000 || CLARK | 2450.00 | 4 | 2001 | 3000 || SCOTT | 3000.00 | 4 | 2001 | 3000 || KING | 5000.00 | 5 | 3001 | 9999 || TURNER | 1500.00 | 3 | 1401 | 2000 || ADAMS | 1100.00 | 1 | 700 | 1200 || JAMES | 950.00 | 1 | 700 | 1200 || FORD | 3000.00 | 4 | 2001 | 3000 || MILLER | 1300.00 | 2 | 1201 | 1400 |+--------+---------+-------+-------+-------+14 rows in set (0.00 sec)mysql>
这些MySQL命令和查询展示了如何使用emp和salgrade表来匹配员工的薪资与相应的薪资等级。通过正确应用条件语句，可以有效地筛选出符合特定薪资范围的员工记录，并将其与相应的薪资等级关联起来。
直接笛卡尔积查询： 初始查询select *from emp,salgrade;产生了一个笛卡尔积，这意味着emp表中的每一行都与salgrade表中的每一行配对，不考虑任何匹配条件。这通常不是期望的结果，因为它返回了所有可能的行组合，而不是根据实际的薪资等级来筛选员工。
筛选匹配薪资等级的员工： 通过执行select ename,sal ,grade from emp,salgrade where sal between losal and hisal;，我们可以获得一个更实用的结果集，其中只包括那些其薪资位于salgrade表定义的最低薪资(losal)和最高薪资(hisal)范围内的员工。这种查询方式直观地展示了如何将两个表中的数据根据实际业务逻辑关联起来。
增加薪资范围信息： 最后的查询select ename,sal ,grade,losal ,hisal from emp,salgrade where sal between losal and hisal;进一步扩展了上一查询，除了返回员工的姓名、薪资和薪资等级外，还包括了确定该薪资等级的最低和最高薪资范围。这提供了更详细的信息，帮助理解为什么特定的员工会被归入特定的薪资等级。
结尾
最后，感谢您阅读我的文章，希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点，请随时在评论区留言。
同时，不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中，我将继续探讨这个话题的不同方面，为您呈现更多深度和见解。
谢谢您的支持，期待与您在下一篇文章中再次相遇！
