【SQL】【SQL Server】SELECTステートメントにサブクエリ(副問合せ)を書く

SELECTステートメントにサブクエリを書く。
今までサブクエリはFROM句やWHERE句には使ってたけど、SELECTにも書けることを知ったのでメモ。
ていうかどういう動きになるのかよくわからなかったので検証。

DBは今回はSQL Serverです。

早速SQLですが今回は下記のような場合。

CREATE TABLE #work1
(id int
 ,name varchar(10)
);

CREATE TABLE #work2
(id int
 ,name varchar(10)
);

INSERT INTO #work1
    ([id], [name])
VALUES
    (1, 'test1');

INSERT INTO #work2
    ([id], [name])
VALUES
    (2, 'test2');

select #work1.id ,(select id from #work2) from #work1

実行結果

2つのテーブルはどちらも1レコードのみなのでわかりやすい。

では、次のようなにどちらも複数レコードある時はどうだろう。

CREATE TABLE #work1
(id int
 ,name varchar(10)
);

CREATE TABLE #work2
(id int
 ,name varchar(10)
);

INSERT INTO #work1
    ([id], [name])
VALUES
    (1, 'test1');
    
INSERT INTO #work1
    ([id], [name])
VALUES
    (11, 'test11');
    
INSERT INTO #work2
    ([id], [name])
VALUES
    (2, 'test2');
    
INSERT INTO #work2
    ([id], [name])
VALUES
    (22, 'test22');

select #work1.id ,(select id from #work2) from #work1

実行結果

エラーになる。
そうだよね。だって1つに絞れないもん。

では、次のように1つに絞れるような場合はどうだろう。

select #work1.id ,(select id from #work2 where id = 2) from #work1

実行結果

これはいけるね。けど、これだとidは2しか表示されない。

じゃあ、ちょっとデータを変えて、work1とwork2でidで紐付けされるようにする。
で、いつものようにidを条件にする。

CREATE TABLE #work1
(id int
 ,name varchar(10)
);

CREATE TABLE #work2
(id int
 ,name varchar(10)
);

INSERT INTO #work1
    ([id], [name])
VALUES
    (1, 'test1');
    
INSERT INTO #work1
    ([id], [name])
VALUES
    (2, 'test11');
    
INSERT INTO #work2
    ([id], [name])
VALUES
    (1, 'test2');
    
INSERT INTO #work2
    ([id], [name])
VALUES
    (2, 'test22');

select #work1.id ,(select id from #work2 where id = #work1.id) from #work1

実行結果

これは思い通り。
ただ、SELECT句に書くメリットって何があるだろう。