複数レコードを跨いだ条件を指定したSELECT方法

Question

A,B,Cさんが存在し、それぞれの特徴を持つテーブルXがあります。

<テーブルX>
user | attr
-----+-------
A | pretty
A | cool
A | beauty
B | pretty
C | pretty
C | beauty

テーブルXからprettyでbeautyな特徴を保有するuserを抽出したいのですが、
select文の書き方がわかりません。
上の例だと、AさんとCさんが抽出結果になります。

以下のSQLを考えたのですが、これだとbeautyを保持しないCさんも抽出されてしまいます。

select distinct user from X where attr in ('pretty', 'beauty');

ご教示、よろしくお願いいたします。

Answer 1

select distinct user from X t
where exists(select 1 from X where user=t.user and attr ='pretty')
  and exists(select 1 from X where user=t.user and attr ='beauty')

ですね。

(user,attr)で一意なら以下の様な記述でも同様な結果になります。

select distinct user from X t
where (select count(*) from X where user=t.user and attr in ('pretty', 'beauty'))=2

性能重視で考えると以下の記述も

select user from x
group by user
having count(case when attr in ('pretty', 'beauty') then 1 end)=2

Comments

[mychikin]

抽出できました!
こういう時にexists使うんですね。勉強になりました。
ありがとうございます。

[mychikin]

なるほど、countを使う方法もあるんですね。
個人的にはexistsを使う方が、クエリからやりたいことがわかる気がするのでこちらを選択することにします。
データが増えてきて処理時間がかかるようになったら、
2つを比較して性能の良い方を選択したいと思います。
複数の方法をご教示いただきありがとうございます。

[sazi]

> 個人的にはexistsを使う方が、クエリからやりたいことがわかる気がする
そうですね。
そう思っての回答でしたが、意欲がありそうなので追記しました。
性能も気にされているようなので、パターンを追加しておきました。

[mychikin]

そんなトリッキーなやり方もあるんですね!
思いつきもしませんでした。
副問合せがない分、3つの中では一番性能が出そうですね。SQL面白いです。
ご教示いただきありがとうございます。