

以下の問題について、解説が理解できず困っています。

> 問) 平均給与が400を超える部署に所属する従業員を検索し、正しい記述について選択せよ。
[code]

>     SELECT employee_name, salary, department_id FROM employees
>     WHERE department_id IN
>     　(SELECT department_id, AVG(salary) FROM employees
>     　 GROUP BY department_id HAVING AVG(salary) > 400);
>  
[/code]

>
> 解答)  
>  副問合せのSELECT句の列数が多いためエラーとなる。

解説では以下のような記載がありました。

> 副問合せで部署毎の平均給与が400を超える部署と平均給与を検索しており、複数の列を返す複数列副問合せになっています。  
>
> しかし、主問合せのWHERE句でDEPARTMENT_ID列と副問合せの結果を比較していますが、副問合せのSELECT句の列数が多いためエラーとなります。

### 質問

副問い合わせのSELECT句が、どの部分と比較して列数が多いのでしょうか。  
参考サイトにあるように、WHERE句に指定された列のみ指定できるということなのでしょうか。

初歩的な質問で申し訳ございません。お手数ですがご教授お願いいたします。

参考サイト：  
<https://omachizura.com/2015/04/sql-subquery.html>


CC BY-SA 4.0
2023-09-23T14:22:08.727
96321
2023-10-04T02:35:58.790
2023-09-23T17:06:54.933
3060
59882
question
sql oracle
副問い合わせのSELECT句につきましてご質問です


> WHERE句に指定された列のみ指定できるということなのでしょうか。

違います。副問い合わせの結果(サブクエリのSELECTで選択する)列の「数」の問題です。

ごく単純化して言うと、IN でサブクエリを使用する場合、サブクエリのSELECTには1つの列しか指定できません。例では2つ指定されてるのでエラーになります。

参考サイトに記載されてるのは自己相関サブクエリの例なので、結果として「INの左辺」と「右辺のサブクエリのSELECTで指定する列」が同じになってるものになっていますが、一般論として考えれば、外側(親)のクエリに現れる列とサブクエリは独立してるものなので、「INの左辺」と「右辺のサブクエリのSELECTで指定する列」は一致しないのが普通です。


CC BY-SA 4.0
2023-09-26T02:08:20.453
96348
2023-09-26T02:08:20.453
5793
96321
answer


以下、sqlite3では、  
「INの左辺」と「右辺のサブクエリのSELECTで指定する列」の数が異なると、  
エラーが発生するようです。  
どうしても列数を同じにできない場合は、EXISTS句を利用するべきかと思っていました。  
認識に誤りありましたら、教えていただけると助かります。

[code]

    from django.db import connection
    
    with connection.cursor() as cursor:
        # データの作成
        cursor.execute(sql='DROP TABLE IF EXISTS 果物テーブル;')
        cursor.execute(sql='CREATE TABLE 果物テーブル(id text, 名称 text);')
        cursor.execute(sql='INSERT INTO 果物テーブル(id, 名称) VALUES("A001", "りんご");')
        cursor.execute(sql='INSERT INTO 果物テーブル(id, 名称) VALUES("A002", "バナナ");')
        cursor.execute(sql='INSERT INTO 果物テーブル(id, 名称) VALUES("A003", "グレープ");')
    
        cursor.execute(sql='DROP TABLE IF EXISTS 価格テーブル;')
        cursor.execute(sql='CREATE TABLE 価格テーブル(id text, 価格 integer);')
        cursor.execute(sql='INSERT INTO 価格テーブル(id, 価格) VALUES("A001", "500");')
        cursor.execute(sql='INSERT INTO 価格テーブル(id, 価格) VALUES("A002", "600");')
        cursor.execute(sql='INSERT INTO 価格テーブル(id, 価格) VALUES("A003", "700");')
    
    # データの検索
    with connection.cursor() as cursor:
        print('◇果物テーブル一覧表示')
        cursor.execute(sql='SELECT * FROM 果物テーブル;')
        for row in cursor.fetchall():
            print(row[0], row[1])
        print('')
    
        print('◇価格テーブル一覧表示')
        cursor.execute(sql='SELECT * FROM 価格テーブル;')
        for row in cursor.fetchall():
            print(row[0], row[1])
        print('')
    
        print('◇シンプルなIN句')
        cursor.execute(sql='SELECT * FROM 果物テーブル WHERE id IN("A001", "A003");')
        for row in cursor.fetchall():
            print(row[0], row[1])
        print('')
    
        print('◇IN句で副問い合わせ')
        cursor.execute(sql='SELECT * FROM 果物テーブル WHERE id IN( SELECT id FROM 価格テーブル WHERE 価格>=600 );')
        # cursor.execute(sql='SELECT * FROM 果物テーブル WHERE id IN( SELECT * FROM 価格テーブル WHERE 価格>=600 );')
        # ↑副問い合わせのSELECT句に*を指定するとエラー「OperationalError: sub-select returns 2 columns - expected 1」が発生しました。
    
        for row in cursor.fetchall():
            print(row[0], row[1])
        print('')
    
        print('◇Existsで副問い合わせ')
        cursor.execute(sql='SELECT * FROM 果物テーブル WHERE EXISTS( SELECT * FROM 価格テーブル WHERE 果物テーブル.id==価格テーブル.id AND 価格>=600 );')
        for row in cursor.fetchall():
            print(row[0], row[1])
        print('')
    
    
[/code]

以下、実行結果です。

[code]

    ◇果物テーブル一覧表示
    A001 りんご
    A002 バナナ
    A003 グレープ
    
    ◇価格テーブル一覧表示
    A001 500
    A002 600
    A003 700
    
    ◇シンプルなIN句
    A001 りんご
    A003 グレープ
    
    ◇IN句で副問い合わせ
    A002 バナナ
    A003 グレープ
    
    ◇Existsで副問い合わせ
    A002 バナナ
    A003 グレープ
    
[/code]

参考にしたサイト:<https://www.sejuku.net/blog/73615>


CC BY-SA 4.0
2023-09-26T09:31:48.413
96351
2023-09-26T09:31:48.413
35267
96321
answer


副問合せで返ってくるデータセットをイメージすると良いと思います。  
例題の副問合せデータセット(想像)

department_id | AVG(salary)  
---|---  
aaaaa | 401  
bbbbb | 402  
ccccc | 403  
  
主問合せのWHERE句にある条件である  
`WHERE department_id IN(...)`で  
department_idと一致するデータを見つける訳ですが  
上記のデータセットの場合だと  
`department_id`と`AVG(salary)`のどちらに一致したら結果を返せば良いか分からない訳です。  
その為、一般的にはIN句を使用した副問合せの場合は下記のような **1列のみ** が返ってくるSQLを作る必要があります。

[code]

    (SELECT department_id FROM employees
    　 GROUP BY department_id HAVING AVG(salary) > 400)
    
[/code]

department_id  
---  
aaaaa  
bbbbb  
ccccc  
  
または、department_idと一致することはほぼないので  
実務上は意味のないデータとなりますが以下の副問合せでも構いません。

> 参考サイトにあるように、WHERE句に指定された列のみ指定できるということなのでしょうか。

これに対する回答としては違います。(※`AVG(salary)`でも良いため。)

[code]

    (SELECT AVG(salary) FROM employees
    　 GROUP BY department_id HAVING AVG(salary) > 400)
    
[/code]

AVG(salary)  
---  
401  
402  
403


CC BY-SA 4.0
2023-10-04T02:35:58.790
96418
2023-10-04T02:35:58.790
29342
96321
answer