PostgreSQL in句でAND条件にするには

Question

menu_code | ingredient_code
-----------+-----------------
100 | abc
101 | efg
102 | hij
100 | xxx
100 | zzz

上記のようなテーブルがあったとします。
この時、 ingredient_code = 'abc' and ingredient_code ='xxx'
の場合はmenu_code == 100がヒットし、
ingredient_code = 'abc' and ingredient_code = 'xxx' and ingredient_code = 'yyy' の場合は、ヒットしないようなSQL文を書きたいのですが、exists演算子を使わないで
書きたいです（というか、副問い合わせはしようしないで（レスポンスが遅くなるので））が、
方法があるのかすらわかりません。
どなたか、教えていただけないでしょうか。

KotoriMaturiさんへ

返答ありがとうございます。
そうです。ingredient_codeの条件が一つでも外れている場合は
ヒットしない、という動作にしたいです。

Comments

[Panzer_vor]

今の例示だとどちらのケースも1レコードが取れないと(1レコード内1カラムに複数値を持つことがあり得ないため)、これはORに読み替えればいいのでしょうか？
そのOR条件のいずれか1件でも条件を満たさなければレコードを取得しないの認識で合ってますでしょうか?(複数コード取れる時も駄目？)

Answer 1

bool_orを使ってみました :-)

with t(menu_code,ingredient_code) as(
values(100,'abc'),
      (100,'xxx'),
      (100,'zzz'),
      (101,'efg'),
      (102,'hij'))
select menu_code
  from t
group by menu_code
having bool_or(ingredient_code = 'abc')
   and bool_or(ingredient_code = 'xxx');

| menu_code |
|-----------|
|       100 |

Answer 2

以下のSQLはいかがでしょうか。
同じような記述が続くので好みは分かれると思いますが、できるだけ簡単で確実な方法を考えてみました。

select menu_code from table001 where ingredient_code = 'abc'
intersect
select menu_code from table001 where ingredient_code = 'xxx'
intersect
select menu_code from table001 where ingredient_code = 'zzz'
;

ちなみに、menu_code列以外も取得する場合は、以下で実現できます。

select * from table001
where menu_code in(
  select menu_code from table001 where ingredient_code = 'abc'
  intersect
  select menu_code from table001 where ingredient_code = 'xxx'
  intersect
  select menu_code from table001 where ingredient_code = 'zzz'
);

追記：
以下の通り、動作確認をしてみました。PostgreSQLのバージョンは9.5.3です。
（長くなってしまい申し訳ございません。）

--テスト用テーブル・データ作成
drop table if exists table001;
create table table001(
  menu_code       varchar(3),
  ingredient_code varchar(3),
  other_key       varchar(3),
  note            text,
  --同一のmenu_code,ingredient_codeの組み合わせが複数行存在する場合も想定
  primary key(menu_code,ingredient_code,other_key)
);
insert into table001 values('100','abc','A','OK');
insert into table001 values('100','xxx','B','OK');
insert into table001 values('100','zzz','C','OK');
insert into table001 values('101','efg','A','NG');
insert into table001 values('102','hij','A','NG');
insert into table001 values('103','abc','A','NG');
insert into table001 values('104','abc','A','NG');
insert into table001 values('104','xxx','B','NG');
insert into table001 values('105','abc','A','NG');
insert into table001 values('105','abc','B','NG');
insert into table001 values('105','zzz','C','NG');
insert into table001 values('106','abc','A','OK');
insert into table001 values('106','xxx','B','OK');
insert into table001 values('106','zzz','C','OK');
insert into table001 values('106','zzz','D','OK');
insert into table001 values('107','abc','A','OK');
insert into table001 values('107','xxx','B','OK');
insert into table001 values('107','zzz','C','OK');
insert into table001 values('107','efg','D','OK');

--テスト1（menu_codeが1行以上該当する場合）
select * from table001
where menu_code in(
  select menu_code from table001 where ingredient_code = 'abc'
  intersect
  select menu_code from table001 where ingredient_code = 'xxx'
  intersect
  select menu_code from table001 where ingredient_code = 'zzz'
);

--結果
-- menu_code | ingredient_code | other_key | note
-------------+-----------------+-----------+------
-- 100       | abc             | A         | OK
-- 100       | xxx             | B         | OK
-- 100       | zzz             | C         | OK
-- 106       | abc             | A         | OK
-- 106       | xxx             | B         | OK
-- 106       | zzz             | C         | OK
-- 106       | zzz             | D         | OK
-- 107       | abc             | A         | OK
-- 107       | xxx             | B         | OK
-- 107       | zzz             | C         | OK
-- 107       | efg             | D         | OK
--(11 行)

--テスト2（menu_codeが1行も該当しない場合）
select * from table001
where menu_code in(
  select menu_code from table001 where ingredient_code = 'abc'
  intersect
  select menu_code from table001 where ingredient_code = 'xxx'
  intersect
  select menu_code from table001 where ingredient_code = 'fff'
);

--結果
-- menu_code | ingredient_code | other_key | note
-------------+-----------------+-----------+------
--(0 行)

Answer 3

Postgreはよくわからないんですが、普通にSQLで書けば
項目2つ

select menu_code
from tbl where ingredient_code in ('abc','xxx')
group by menu_code
having count(distinct ingredient_code)=2;

項目3つ

select menu_code
from tbl where ingredient_code in ('abc','xxx','yyy')
group by menu_code
having count(distinct ingredient_code)=3;

としませんか？
項目数を自動で提案したい場合はなんらかの別テーブルを用意するといいでしょう

Answer 4

JOINはサブクエリでは無いと言う事で。。

select A.menu_code from table A
join table B on A.menu_code=B.menu_code and A.ingredient_code='abc' and B.ingredient_code='xxx'
left join table C on A.menu_code=C.menu_code and A.ingredient_code='abc' and C.ingredient_code='yyy'
where C.ingredient_code is NULL;

Answer 5

サブクエリが一概にパフォーマンスを悪化させるって訳でもないのですが、
サブクエリを使わないという制約を課すなら以下のような感じでどうでしょう？

もう少し考えてみますが、一応下記で大丈夫です。
レコードが取れない条件を入力された際の対応がどうしても思いつかない･･･

SELECT
    MAX(menu_code) AS menu_code 
FROM
    TABLE 
WHERE
    ingredient_code IN ('abc', 'xxx', 'yyy') 
HAVING
    MAX(menu_code) = MIN(menu_code)
AND COUNT(*) >= 3    -- 条件の数(※ただし「aaa」が2回など同一条件が複数指定されるとNG)

ただ集約関数使いまくるのとHAVING句を使うからむしろパフォーマンスが悪いかも･･･

・追記
禁を犯してFROM句でサブクエリを用いてみました。
すごく苦しいコード・・・orz

SELECT
    MAX(menu_code) AS menu_code 
FROM
    ( 
        SELECT
              'abc' AS cond 
        UNION ALL 
        SELECT
              'xxx' 
        UNION ALL 
        SELECT
              'yyy'
    ) T1
    LEFT JOIN TABLE T2
        ON T2.ingredient_code = T1.cond 
HAVING
    MAX(menu_code) = MIN(menu_code)  -- 指摘を受け修正
AND COUNT(*) = COUNT(menu_code)

###補足
ちなみにFROM句におけるサブクエリを結合する方法を用いると、
WHERE句でのサブクエリ(相関副問い合わせ)と比べ高速に動作する機会は多いと思われます。

Comments

[doumeishi]

Kotoriさん、hirohiroさん
ありがとうございます！
Kotoriさんの案の方がすっきりしているように見えるので
Kotoriさんを、VAにさせていただきます。
Kotoriさんの最初の案を実行したところ、ちゃんと動きました
実際に解決したいコードは、ingredient_codeの指定は重複
しないので、この案でレスポンスを測ってみて、問題なければ
使います。
お二方とも、本当にありがとうございました。

[kutsulog]

横槍な上に蛇足ですが
2番目のSQLのほうは
こっちのHAVINGにも
MAX(menu_code) = MIN(menu_code)
をいれないとyyyがefgになったときにマッチしてしまいそう...

SELECT～UNION ALL～の部分は
PostgreSQLなら(SELECT UNNEST(ARRAY['abc', 'xyz', 'yyy']) AS cond) T1でいけるかと

[Panzer_vor]

> kutsulogさん
ご指摘ありがとうございます。完全に条件漏れですね、チョンボしてました…。コードは訂正させていただきます。
またARRAYとUNNESTなるものがあるのですね。
当方ベンダ拡張部分については知識不足なので、勉強になります。

Answer 6

当初投稿したものが間違っていたため再考したのですが、ましなコードは浮かびませんでした。
ごり押し気味な回答になっています。すみません

サブクエリを使わないとなると、こんな感じでしょうか？
(動作確認してません)

SELECT menu_code
FROM table
GROUP BY menu_code
HAVING SUM(
  CASE 
    WHEN ingredient_code = 'abc' OR ingredient_code ='xxx' 
    THEN 1 ELSE 0 
  END
) >= 2 /* この2は比較条件の数 */

または

SELECT menu_code
FROM table
GROUP BY menu_code
HAVING SUM(CASE WHEN ingredient_code = 'abc' THEN 1 ELSE 0 END) = 1
AND SUM(CASE WHEN ingredient_code = 'xxx' THEN 1 ELSE 0 END) = 1

テーブル設計やデータ件数とその偏りに大きく影響を受けると思うので、サブクエリ利用より常に早いか？と問われると怪しいです(むしろサブクエリ利用のほうが早いケースが多いかも)

Comments

[doumeishi]

返答ありがとうございます。
>ingredient_code = 'abc' OR ingredient_code ='xxx'
この部分を、ANDにしたいのです・・・

[hirohiro]

もう一度よくコードを確認してほしいのですが、
menu_codeでグループ化し、それぞれのmenu_codeに対して、ingredient_code = 'abc'かingredient_code ='xxx' の行の数を数えます
（これがsum。例示のデータなら合計値まで出すと次のようになっているはず）
menu_code　sum
100　　　　　2
101　　　　　0
102　　　　　0
そして合計が1以上のmenu_codeのみを選択しています。
結果的に以下の結果が得られていると思います
----
ingredient_code = 'abc' and ingredient_code ='xxx'
の場合はmenu_code == 100がヒットし、
ingredient_code = 'abc' and ingredient_code = 'xxx' and ingredient_code = 'yyy' の場合は、ヒットしない

[hirohiro]

コメント書いていて気がつきました！ダメですね。再考します
（sum()>=2　でよければ動きますね。2は条件の数）

[hirohiro]

間違っていた上に、修正しようとしたものが(一応動作はすると思うのですが...)不恰好なものになってしまってすみません。失礼しました。難しいですね...

[Panzer_vor]

> hirohiroさん
横から失礼いたします。
1つ目のサンプルコードのCASE式ですが、恐らく戻し値が逆になっています。
後、僕もコードを考えてみましたが結局は同じような形に収まりました^^;
レコードが取れない際の扱いが難しいですよね・・・

[hirohiro]

KotoriMaturi　さん　ありがとうございます
あ、ほんとですね。修正しておきます