一个关于 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