欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

一个关于 SQL 别名的面试题

程序员文章站 2022-03-24 21:59:34
SQL 支持表别名和列别名,在 SQL 语句中别名的优先级比同名的表名和字段名更高。进一步来说,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