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

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


文章目錄

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

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

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

假如存在以下兩個(gè)表 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 都有兩個(gè)字段：id 和 name；每個(gè)表中存在一條記錄，name 字段的值不同。

請(qǐng)問(wèn)，下面語(yǔ)句的查詢(xún)結(jié)果是什么？

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



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

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

這個(gè)規(guī)則同樣適用于 SQL，別名（Alias）只在當(dāng)前語(yǔ)句中有效，表名是一個(gè)持久化的對(duì)象標(biāo)識(shí)符。因此，上面的查詢(xún)語(yǔ)句最終返回了 t2 中的 id 和 name：

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



問(wèn)題 2：列別名

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

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



請(qǐng)問(wèn)，下面語(yǔ)句的查詢(xún)結(jié)果是什么？

SELECT -id AS id
FROM t3
ORDER BY id;



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

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

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



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

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

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



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

SELECT -id
FROM t3
ORDER BY id;

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



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

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

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



總結(jié)

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

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

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



下面查詢(xún)語(yǔ)句的結(jié)果是什么？

SELECT * FROM t1;


對(duì)于 Oracle 而言，不允許臨時(shí)表和普通表的名字相同；對(duì)于 SQL Server 而言，臨時(shí)表必須以井號(hào)（#）開(kāi)頭；所以它們不存在上面的問(wèn)題。