admin管理员组文章数量:1487745
【数据库差异研究】别名与表字段冲突，不同数据库在where中的处理行为
⚛️总结
单层查询
数据库类型 | 别名与表字段重名冲突处理方式 |
|---|---|
SQLITE | 在 WHERE 子句中使用表字段而非别名 |
ORACLE | 在 WHERE 子句中使用表字段而非别名 |
PG | 在 WHERE 子句中使用表字段而非别名 |
嵌套查询
查询类型 | 子查询 | 内层 where 冲突处理 | 外层 where 冲突处理 |
|---|---|---|---|
SQLITE | 有别名 | 使用表字段 | 使用子查询中的表字段 |
SQLITE | 无别名 | 使用表字段 | 使用子查询中的表字段 |
ORACLE | 有别名 | 使用表字段 | 使用子查询中的表字段 |
ORACLE | 无别名 | 使用表字段 | 使用子查询中的表字段 |
PG | 有别名 | 使用表字段 | 使用子查询中的表字段 |
PG | 无别名 | PG报错 | PG报错 |
☪️1 问题描述
一、当单层查询发生别名与表字段重名冲突时，不同数据库在where中的处理行为是怎样的呢？
这里的处理行为无非两种
- where 处理的是表字段而非别名。
- where 处理的是别名而非表字段。
说明：对于表字段与别名重名冲突， where 有时处理的是表字段而非别名，有时处理的是别名而非表字段。显然这种数据库设计是存在问题的，本文不予考虑。
二、当嵌套查询发生别名与表字段重名冲突时，不同数据库在where中的处理行为是怎样的呢？
详见后文。
☪️2 测试用例
本文设计的测试表与数据如下：
代码语言：javascript代码运行次数：0运行复制-- 创建表 u_client
CREATE TABLE u_client (
client_id VARCHAR(20) PRIMARY KEY,
Name VARCHAR(50),
personalID VARCHAR(20),
address VARCHAR(100),
user_token VARCHAR(20),
Email VARCHAR(50),
mobile_tel VARCHAR(20)
);
-- 插入数据到 u_client 表
INSERT INTO u_client(client_id, Name, personalID, address, user_token, Email, mobile_tel)
VALUES('2', 'test', '20240702', 'hangzhou', '11111111', '306891687@163', '13345671234');
INSERT INTO u_client(client_id, Name, personalID, address, user_token, Email, mobile_tel)
VALUES('10002', 'test', '20240702', 'hangzhou', '1', '306891687@163', '13345671234');
-- 创建表 u_fund_account
CREATE TABLE u_fund_account (
password VARCHAR(50),
client_id VARCHAR(20),
fund_account VARCHAR(50),
PRIMARY KEY(client_id, fund_account)
);
-- 插入数据到 u_fund_account 表
INSERT INTO u_fund_account(password, client_id, fund_account)
VALUES('PWD1', '2', 'account1');
INSERT INTO u_fund_account(password, client_id, fund_account)
VALUES('PWD2', '10002', 'account2');u_client 表
client_id | Name | personalID | address | user_token | mobile_tel | |
|---|---|---|---|---|---|---|
2 | test | 20240702 | hangzhou | 11111111 | 306891687@163 | 13345671234 |
10002 | test | 20240702 | hangzhou | 1 | 306891687@163 | 13345671234 |
u_fund_account 表
password | client_id | fund_account |
|---|---|---|
PWD1 | 2 | account1 |
PWD2 | 10002 | account2 |
使用 LEFT JION 查询语句如下：
代码语言：javascript代码运行次数：0运行复制-- LEFT JION 之后的表
SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id;LEFT JION 之后的表——后续称之为 查询表
client_id | user_token |
|---|---|
2 | 211111111 |
10002 | 100021 |
对于 查询表 的表字段 user_token（别名） 与 u_client 表 的表字段 user_token（表字段） 发生了重名冲突。
♋2.1 测试单层查询
在测试用例基础上，设计的测试用例与预期行为如下：
测试场景一：
代码语言：javascript代码运行次数：0运行复制SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '11111111';client_id | user_token |
|---|---|
2 | 211111111 |
client_id | user_token |
|---|---|
测试场景二（二次验证）：
代码语言：javascript代码运行次数：0运行复制SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '211111111';client_id | user_token |
|---|---|
client_id | user_token |
|---|---|
2 | 211111111 |
♏2.1.1 SQLITE数据库
基础数据——满足测试表与数据
代码语言：javascript代码运行次数：0运行复制-- 创建表 u_client
CREATE TABLE u_client (
client_id VARCHAR(20) PRIMARY KEY,
Name VARCHAR(50),
personalID VARCHAR(20),
address VARCHAR(100),
user_token VARCHAR(20),
Email VARCHAR(50),
mobile_tel VARCHAR(20)
);
-- 插入数据到 u_client 表
INSERT INTO u_client(client_id, Name, personalID, address, user_token, Email, mobile_tel)
VALUES('2', 'test', '20240702', 'hangzhou', '11111111', '306891687@163', '13345671234');
INSERT INTO u_client(client_id, Name, personalID, address, user_token, Email, mobile_tel)
VALUES('10002', 'test', '20240702', 'hangzhou', '1', '306891687@163', '13345671234');
-- 创建表 u_fund_account
CREATE TABLE u_fund_account (
password VARCHAR(50),
client_id VARCHAR(20),
fund_account VARCHAR(50),
PRIMARY KEY(client_id, fund_account)
);
-- 插入数据到 u_fund_account 表
INSERT INTO u_fund_account(password, client_id, fund_account)
VALUES('PWD1', '2', 'account1');
INSERT INTO u_fund_account(password, client_id, fund_account)
VALUES('PWD2', '10002', 'account2');
-- 查询表
select * from u_client;
select * from u_fund_account;
-- 查询表
SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id;别名冲突查询结果
测试场景一：
代码语言：javascript代码运行次数：0运行复制SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '11111111';结论：说明当别名和表字段发生重名冲突时，在 where 中使用的是表字段而非别名。
测试场景二（二次验证）：
代码语言：javascript代码运行次数：0运行复制SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '211111111';结论：说明当别名和表字段发生重名冲突时，在 where 中使用的是表字段而非别名。
结论
单层查询：当别名和表字段发生重名冲突时，SQLITE 在 where 中使用的是表字段而非别名。
♐2.1.2 ORACLE数据库
基础数据——满足测试表与数据
代码语言：javascript代码运行次数：0运行复制-- 创建表 u_client
CREATE TABLE u_client (
client_id VARCHAR(20) PRIMARY KEY,
Name VARCHAR(50),
personalID VARCHAR(20),
address VARCHAR(100),
user_token VARCHAR(20),
Email VARCHAR(50),
mobile_tel VARCHAR(20)
);
-- 插入数据到 u_client 表
INSERT INTO u_client(client_id, Name, personalID, address, user_token, Email, mobile_tel)
VALUES('2', 'test', '20240702', 'hangzhou', '11111111', '306891687@163', '13345671234');
INSERT INTO u_client(client_id, Name, personalID, address, user_token, Email, mobile_tel)
VALUES('10002', 'test', '20240702', 'hangzhou', '1', '306891687@163', '13345671234');
-- 创建表 u_fund_account
CREATE TABLE u_fund_account (
password VARCHAR(50),
client_id VARCHAR(20),
fund_account VARCHAR(50),
PRIMARY KEY(client_id, fund_account)
);
-- 插入数据到 u_fund_account 表
INSERT INTO u_fund_account(password, client_id, fund_account)
VALUES('PWD1', '2', 'account1');
INSERT INTO u_fund_account(password, client_id, fund_account)
VALUES('PWD2', '10002', 'account2');
-- 查询表
select * from u_client;
select * from u_fund_account;
-- 查询表
SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id;别名冲突查询结果
测试场景一：
代码语言：javascript代码运行次数：0运行复制SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '11111111';结论：说明当别名和表字段发生重名冲突时，在 where 中使用的是表字段而非别名。
测试场景二（二次验证）：
代码语言：javascript代码运行次数：0运行复制SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '211111111';结论：说明当别名和表字段发生重名冲突时，在 where 中使用的是表字段而非别名。
结论
单层查询：当别名和表字段发生重名冲突时，ORACLE 在 where 中使用的是表字段而非别名。
♑2.1.3 PG数据库
基础数据——满足测试表与数据
代码语言：javascript代码运行次数：0运行复制-- 创建表 u_client
CREATE TABLE u_client (
client_id VARCHAR(20) PRIMARY KEY,
Name VARCHAR(50),
personalID VARCHAR(20),
address VARCHAR(100),
user_token VARCHAR(20),
Email VARCHAR(50),
mobile_tel VARCHAR(20)
);
-- 插入数据到 u_client 表
INSERT INTO u_client(client_id, Name, personalID, address, user_token, Email, mobile_tel)
VALUES('2', 'test', '20240702', 'hangzhou', '11111111', '306891687@163', '13345671234');
INSERT INTO u_client(client_id, Name, personalID, address, user_token, Email, mobile_tel)
VALUES('10002', 'test', '20240702', 'hangzhou', '1', '306891687@163', '13345671234');
-- 创建表 u_fund_account
CREATE TABLE u_fund_account (
password VARCHAR(50),
client_id VARCHAR(20),
fund_account VARCHAR(50),
PRIMARY KEY(client_id, fund_account)
);
-- 插入数据到 u_fund_account 表
INSERT INTO u_fund_account(password, client_id, fund_account)
VALUES('PWD1', '2', 'account1');
INSERT INTO u_fund_account(password, client_id, fund_account)
VALUES('PWD2', '10002', 'account2');
-- 查询表
select * from u_client;
select * from u_fund_account;
-- 查询表
SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id;别名冲突查询结果
测试场景一：
代码语言：javascript代码运行次数：0运行复制SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '11111111';结论：说明当别名和表字段发生重名冲突时，在 where 中使用的是表字段而非别名。
测试场景二（二次验证）：
代码语言：javascript代码运行次数：0运行复制SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '211111111';结论：说明当别名和表字段发生重名冲突时，在 where 中使用的是表字段而非别名。
结论
单层查询：当别名和表字段发生重名冲突时，PG 在 where 中使用的是表字段而非别名。
♋2.2 测试嵌套查询
根据3.1 章节可知：单层查询
数据库类型 | 别名与表字段重名冲突处理方式 |
|---|---|
SQLITE | 在 WHERE 子句中使用表字段而非别名 |
ORACLE | 在 WHERE 子句中使用表字段而非别名 |
PG | 在 WHERE 子句中使用表字段而非别名 |
在测试用例基础上，设计的测试用例与预期行为如下：
测试场景三（嵌套查询——含子查询别名）：
代码语言：javascript代码运行次数：0运行复制SELECT * FROM
(SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '11111111') t
WHERE t.user_token = '211111111';- 结果如下，说明在嵌套查询中子查询有别名，在内层查询的别名和表字段发生重名冲突时，内层 where 中使用的是表字段而非别名；外层 where 中使用的是实际的表字段。 client_iduser_token
- 结果如下，说明在嵌套查询中子查询有别名，在内层查询的别名和表字段发生重名冲突时，内层 where 中使用的是表字段而非别名；外层 where 中使用的是子查询结果中的表字段。 client_iduser_token2211111111
测试场景四（嵌套查询——不含子查询别名）：
代码语言：javascript代码运行次数：0运行复制SELECT * FROM
(SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '11111111')
WHERE user_token = '211111111';- 结果如下，说明在嵌套查询中子查询有别名，在内层查询的别名和表字段发生重名冲突时，内层 where 中使用的是表字段而非别名；外层 where 中使用的是实际的表字段。 client_iduser_token
- 结果如下，说明在嵌套查询中子查询有别名，在内层查询的别名和表字段发生重名冲突时，内层 where 中使用的是表字段而非别名；外层 where 中使用的是子查询结果中的表字段。 client_iduser_token2211111111
♉2.2.1 SQLITE数据库
基础数据——满足测试表与数据
代码语言：javascript代码运行次数：0运行复制-- 创建表 u_client
CREATE TABLE u_client (
client_id VARCHAR(20) PRIMARY KEY,
Name VARCHAR(50),
personalID VARCHAR(20),
address VARCHAR(100),
user_token VARCHAR(20),
Email VARCHAR(50),
mobile_tel VARCHAR(20)
);
-- 插入数据到 u_client 表
INSERT INTO u_client(client_id, Name, personalID, address, user_token, Email, mobile_tel)
VALUES('2', 'test', '20240702', 'hangzhou', '11111111', '306891687@163', '13345671234');
INSERT INTO u_client(client_id, Name, personalID, address, user_token, Email, mobile_tel)
VALUES('10002', 'test', '20240702', 'hangzhou', '1', '306891687@163', '13345671234');
-- 创建表 u_fund_account
CREATE TABLE u_fund_account (
password VARCHAR(50),
client_id VARCHAR(20),
fund_account VARCHAR(50),
PRIMARY KEY(client_id, fund_account)
);
-- 插入数据到 u_fund_account 表
INSERT INTO u_fund_account(password, client_id, fund_account)
VALUES('PWD1', '2', 'account1');
INSERT INTO u_fund_account(password, client_id, fund_account)
VALUES('PWD2', '10002', 'account2');
-- 查询表
select * from u_client;
select * from u_fund_account;
-- 查询表
SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id;表u_client client_idNamepersonalIDaddressuser_tokenEmailmobile_tel2test20240702hangzhou11111111306891687@1631334567123410002test20240702hangzhou1306891687@16313345671234
表u_fund_account
passwordclient_idfund_accountPWD12account1PWD210002account2
查询表 client_iduser_token221111111110002100021
别名冲突查询结果
测试场景三（嵌套查询——含子查询别名）：
代码语言：javascript代码运行次数：0运行复制SELECT * FROM
(SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '11111111') t
WHERE t.user_token = '211111111';结论：说明在嵌套查询中子查询有别名，在内层查询的别名和表字段发生重名冲突时，内层 where 中使用的是表字段而非别名；外层 where 中使用的是子查询结果中的表字段。
测试场景四（嵌套查询——不含子查询别名）：
代码语言：javascript代码运行次数：0运行复制SELECT * FROM
(SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '11111111')
WHERE user_token = '211111111';结论：说明在嵌套查询中子查询有别名，在内层查询的别名和表字段发生重名冲突时，内层 where 中使用的是表字段而非别名；外层 where 中使用的是子查询结果中的表字段。
结论
嵌套查询： 说明在嵌套查询中子查询有或没有别名，在内层查询的别名和表字段发生重名冲突时，内层 where 中使用的是表字段而非别名；外层 where 中使用的是子查询中的表字段。
♈2.2.2 ORACLE数据库
基础数据——满足测试表与数据
代码语言：javascript代码运行次数：0运行复制-- 创建表 u_client
CREATE TABLE u_client (
client_id VARCHAR(20) PRIMARY KEY,
Name VARCHAR(50),
personalID VARCHAR(20),
address VARCHAR(100),
user_token VARCHAR(20),
Email VARCHAR(50),
mobile_tel VARCHAR(20)
);
-- 插入数据到 u_client 表
INSERT INTO u_client(client_id, Name, personalID, address, user_token, Email, mobile_tel)
VALUES('2', 'test', '20240702', 'hangzhou', '11111111', '306891687@163', '13345671234');
INSERT INTO u_client(client_id, Name, personalID, address, user_token, Email, mobile_tel)
VALUES('10002', 'test', '20240702', 'hangzhou', '1', '306891687@163', '13345671234');
-- 创建表 u_fund_account
CREATE TABLE u_fund_account (
password VARCHAR(50),
client_id VARCHAR(20),
fund_account VARCHAR(50),
PRIMARY KEY(client_id, fund_account)
);
-- 插入数据到 u_fund_account 表
INSERT INTO u_fund_account(password, client_id, fund_account)
VALUES('PWD1', '2', 'account1');
INSERT INTO u_fund_account(password, client_id, fund_account)
VALUES('PWD2', '10002', 'account2');
-- 查询表
select * from u_client;
select * from u_fund_account;
-- 查询表
SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id;表u_client client_idNamepersonalIDaddressuser_tokenEmailmobile_tel2test20240702hangzhou11111111306891687@1631334567123410002test20240702hangzhou1306891687@16313345671234
表u_fund_account
passwordclient_idfund_accountPWD12account1PWD210002account2
查询表 client_iduser_token221111111110002100021
别名冲突查询结果
测试场景三（嵌套查询——含子查询别名）：
代码语言：javascript代码运行次数：0运行复制SELECT * FROM
(SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '11111111') t
WHERE t.user_token = '211111111';结论：说明在嵌套查询中子查询有别名，在内层查询的别名和表字段发生重名冲突时，内层 where 中使用的是表字段而非别名；外层 where 中使用的是子查询结果中的表字段。
测试场景四（嵌套查询——不含子查询别名）：
代码语言：javascript代码运行次数：0运行复制SELECT * FROM
(SELECT
a.client_id AS client_id,
b.client_id || a.user_token AS user_token
FROM u_client a LEFT JOIN u_fund_account b
ON a.client_id = b.client_id
WHERE user_token = '11111111')
WHERE user_token = '211111111';结论：说明在嵌套查询中子查询有别名，在内层查询的别名和表字段发生重名冲突时，内层 where 中使用的是表字段而非别名；外层 where 中使用的是子查询结果中的表字段。
结论
嵌套查询： 说明在嵌套查询中子查询有或没有别名，在内层查询的别名和表字段发生重名冲突时，内层 where 中使用的是表字段而非别名；外层 where 中使用的是子查询中的表字段。
本文标签： 数据库差异研究别名与表字段冲突，不同数据库在where中的处理行为
版权声明：本文标题：【数据库差异研究】别名与表字段冲突，不同数据库在where中的处理行为 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/shuma/1755020512a3182556.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论