一個關(guān)于 SQL 別名的面試題

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        問題 1：表別名
        問題 2：列別名
        總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。最近有個同學(xué)問了我一個有關(guān) SQL 語句中使用別名的問題，這是他在面試中碰到的實際問題，在這里分享給大家。如果覺得文章對你有用，歡迎評論??、點贊??、推薦??

以下內(nèi)容在 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 數(shù)據(jù)庫中進行了驗證，不過應(yīng)該也適用于其他數(shù)據(jù)庫管理系統(tǒng)，歡迎大家補充。
問題 1：表別名

假如存在以下兩個表 t1 和 t2，以及示例數(shù)據(jù)：

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 字段的值不同。

請問，下面語句的查詢結(jié)果是什么？

SELECT t1.id, t1.name
FROM t1 t
CROSS JOIN t2 t1;

查詢本身非常簡單，就是 t1 和 t2 進行連接查詢。需要注意的是查詢中 t1 定義了一個別名 t，t2 定義了一個別名 t1；那么在 SELECT 列表中的 t1 到底引用的是表 t1，還是別名 t1（也就是表 t2）呢？

如果了解編程語言的話，應(yīng)該知道對于同名的變量，局部變量優(yōu)先級比全局變量高；也就是說，作用域或者生命周期越小的變量在有效范圍內(nèi)優(yōu)先級越高。即使不了解其他編程語言，也不難理解這個規(guī)則；因為一方面這比較復(fù)合我們的正常邏輯，另一方面不使用這個規(guī)則的話反而會引起問題。

這個規(guī)則同樣適用于 SQL，別名（Alias）只在當前語句中有效，表名是一個持久化的對象標識符。因此，上面的查詢語句最終返回了 t2 中的 id 和 name：

id|name |
--|-----|
 1|Alias|

問題 2：列別名

假如存在以下表 t3 和示例數(shù)據(jù)：

CREATE TABLE t3(id int);
INSERT INTO t3 VALUES (0);
INSERT INTO t3 VALUES (1);
INSERT INTO t3 VALUES (2);

請問，下面語句的查詢結(jié)果是什么？

SELECT -id AS id
FROM t3
ORDER BY id;

查詢返回了 id 字段的相反數(shù)，同時給它指定了一個和字段名相同的別名 id，最后在 ORDER BY 子句中指定按照 id 進行排序。

問題的關(guān)鍵在于排序使用的 id 是字段名還是別名，這里還涉及一個問題就是 SQL 子句的執(zhí)行順序。SELECT 子句在 ORDER BY 子句之前執(zhí)行，也就是說先有列別名 id，然后再進行排序操作。按照上面分析的標識符優(yōu)先級，排序使用的是別名 id，查詢的結(jié)果如下：

id|
--|
-2|
-1|
 0|

返回的結(jié)果按照升序排序顯示，相當于以下子查詢：

SELECT id
FROM (SELECT -id AS id
      FROM t3
      ) t3
ORDER BY id;

id|
--|
-2|
-1|
 0|

如果去掉查詢語句中的別名引用，按照 id 字段進行排序的結(jié)果如下：

SELECT -id
FROM t3
ORDER BY id;

-id|
---|
  0|
 -1|
 -2|

返回的結(jié)果按照降序排序顯示，相當于以下子查詢：

SELECT -id AS id
FROM (SELECT id
      FROM t3
      ORDER BY id) t3;

id|
--|
 0|
-1|
-2|

總結(jié)

SQL 和其他編程語言一樣，作用域或者生命周期越小的標識符在有效范圍內(nèi)的優(yōu)先級越高。

最后，給大家留下一個思考問題。對于 MySQL、PostgreSQL 以及 SQLite，創(chuàng)建一個臨時表 t1：

CREATE TEMPORARY TABLE t1(id int, name varchar(10));
INSERT INTO t1 VALUES (1, 'TEMP');

下面查詢語句的結(jié)果是什么？

SELECT * FROM t1;

對于 Oracle 而言，不允許臨時表和普通表的名字相同；對于 SQL Server 而言，臨時表必須以井號（#）開頭；所以它們不存在上面的問題。