一个关于 SQL 别名的面试题
大家好,我是只谈技术不剪发的 Tony 老师。最近有个同学问了我一个有关 SQL 语句中使用别名的问题,这是他在面试中碰到的实际问题,在这里分享给大家。如果觉得文章对你有用,欢迎评论????、点赞????、推荐????
以下内容在 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 数据库中进行了验证,不过应该也适用于其他数据库管理系统,欢迎大家补充。
问题 1:表别名
假如存在以下两个表 t1 和 t2,以及示例数据:
CREATE TABLE t1(id int, name varchar(10));
INSERT INTO t1 VALUES (1, 'SQL');
CREATE TABLE t2(id int, name varchar(10));
INSERT INTO t2 VALUES (1, 'Alias');
其中,t1 和 t2 都有两个字段:id 和 name;每个表中存在一条记录,name 字段的值不同。
请问,下面语句的查询结果是什么?
SELECT t1.id, t1.name
FROM t1 t
CROSS JOIN t2 t1;
查询本身非常简单,就是 t1 和 t2 进行连接查询。需要注意的是查询中 t1 定义了一个别名 t,t2 定义了一个别名 t1;那么在 SELECT 列表中的 t1 到底引用的是表 t1,还是别名 t1(也就是表 t2)呢?
如果了解编程语言的话,应该知道对于同名的变量,局部变量优先级比全局变量高;也就是说,作用域或者生命周期越小的变量在有效范围内优先级越高。即使不了解其他编程语言,也不难理解这个规则;因为一方面这比较复合我们的正常逻辑,另一方面不使用这个规则的话反而会引起问题。
这个规则同样适用于 SQL,别名(Alias)只在当前语句中有效,表名是一个持久化的对象标识符。因此,上面的查询语句最终返回了 t2 中的 id 和 name:
id|name |
--|-----|
1|Alias|
问题 2:列别名
假如存在以下表 t3 和示例数据:
CREATE TABLE t3(id int);
INSERT INTO t3 VALUES (0);
INSERT INTO t3 VALUES (1);
INSERT INTO t3 VALUES (2);
请问,下面语句的查询结果是什么?
SELECT -id AS id
FROM t3
ORDER BY id;
查询返回了 id 字段的相反数,同时给它指定了一个和字段名相同的别名 id,最后在 ORDER BY 子句中指定按照 id 进行排序。
问题的关键在于排序使用的 id 是字段名还是别名,这里还涉及一个问题就是 SQL 子句的执行顺序。SELECT 子句在 ORDER BY 子句之前执行,也就是说先有列别名 id,然后再进行排序操作。按照上面分析的标识符优先级,排序使用的是别名 id,查询的结果如下:
id|
--|
-2|
-1|
0|
返回的结果按照升序排序显示,相当于以下子查询:
SELECT id
FROM (SELECT -id AS id
FROM t3
) t3
ORDER BY id;
id|
--|
-2|
-1|
0|
如果去掉查询语句中的别名引用,按照 id 字段进行排序的结果如下:
SELECT -id
FROM t3
ORDER BY id;
-id|
---|
0|
-1|
-2|
返回的结果按照降序排序显示,相当于以下子查询:
SELECT -id AS id
FROM (SELECT id
FROM t3
ORDER BY id) t3;
id|
--|
0|
-1|
-2|
总结
SQL 和其他编程语言一样,作用域或者生命周期越小的标识符在有效范围内的优先级越高。
最后,给大家留下一个思考问题。对于 MySQL、PostgreSQL 以及 SQLite,创建一个临时表 t1:
CREATE TEMPORARY TABLE t1(id int, name varchar(10));
INSERT INTO t1 VALUES (1, 'TEMP');
下面查询语句的结果是什么?
SELECT * FROM t1;
对于 Oracle 而言,不允许临时表和普通表的名字相同;对于 SQL Server 而言,临时表必须以井号(#)开头;所以它们不存在上面的问题。
本文地址:https://blog.csdn.net/horses/article/details/108972712
推荐阅读
-
分享一道关于闭包、bind和this的面试题
-
jsp连接sql数据库的步骤(用jsp连接sql做一个登录界面)
-
SQLServer中用T—SQL命令查询一个数据库中有哪些表的sql语句
-
ORACLE官方SQL语言参考之关于用户定义函数的实例讲解
-
关于SQL语句中的AND和OR执行顺序遇到的问题
-
sql将一个表中的数据插入到另一个表中的方法
-
关于SQL中CTE(公用表表达式)(Common Table Expression)的总结
-
在SQL Server中使用SQL语句查询一个存储过程被其它所有的存储过程引用的存储过程名
-
多列复合索引的使用 绕过微软sql server的一个缺陷
-
SQL Server 完整备份遇到的一个不常见的错误及解决方法