나는 테이블을 가지고 가정 student, club그리고 student_club:

student {
    id
    name
}
club {
    id
    name
}
student_club {
    student_id
    club_id
}

축구 (30) 동아리와 야구 (50) 동아리의 모든 학생들을 찾는 방법을 알고 싶습니다.
이 쿼리는 작동하지 않지만 지금까지 가장 가까운 것입니다.

SELECT student.*
FROM   student
INNER  JOIN student_club sc ON student.id = sc.student_id
LEFT   JOIN club c ON c.id = sc.club_id
WHERE  c.id = 30 AND c.id = 50

답변

나는 궁금했어. 그리고 우리 모두 알다시피 호기심은 고양이를 죽이는 것으로 유명합니다.

그렇다면 고양이의 피부를 가장 빨리 살리는 방법은 무엇일까요?

이 테스트를위한 정확한 고양이 가죽 환경 :

적절한 RAM 및 설정을 갖춘 Debian Squeeze의 PostgreSQL 9.0 .
학생 6.000 명, 클럽 회원 24.000 명 (실제 데이터와 유사한 데이터베이스에서 복사 한 데이터)
질문에서 명명 스키마에서 약간의 전환 : student.idis student.stud_idand club.idis club.club_idhere.
이 스레드에서 작성자의 이름을 따서 쿼리 이름을 지정했습니다. 인덱스는 2 개입니다.
캐시를 채우기 위해 모든 쿼리를 몇 번 실행 한 다음 EXPLAIN ANALYZE를 사용하여 5 개 중 최고를 선택했습니다.
관련 인덱스 (최적이어야합니다-어떤 클럽이 쿼리 될 것인지에 대한 사전 지식이 부족한 한) :
```
ALTER TABLE student ADD CONSTRAINT student_pkey PRIMARY KEY(stud_id );
ALTER TABLE student_club ADD CONSTRAINT sc_pkey PRIMARY KEY(stud_id, club_id);
ALTER TABLE club       ADD CONSTRAINT club_pkey PRIMARY KEY(club_id );
CREATE INDEX sc_club_id_idx ON student_club (club_id);
```
club_pkey여기에서 대부분의 쿼리에는 필요하지 않습니다.
기본 키는 PostgreSQL에서 자동으로 고유 인덱스를 구현합니다.
마지막 인덱스는 PostgreSQL 에서 다중 열 인덱스 의 알려진 단점을 보완하는 것입니다 .

다중 열 B- 트리 인덱스는 인덱스 열의 하위 집합을 포함하는 쿼리 조건과 함께 사용할 수 있지만 선행 (가장 왼쪽) 열에 제약 조건이있는 경우 인덱스가 가장 효율적입니다.

결과 :

EXPLAIN ANALYZE의 총 런타임.

1) 마틴 2 : 44.594ms

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id IN (30, 50)
GROUP  BY 1,2
HAVING COUNT(*) > 1;

2) Erwin 1 : 33.217ms

SELECT s.stud_id, s.name
FROM   student s
JOIN   (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30, 50)
   GROUP  BY 1
   HAVING COUNT(*) > 1
   ) sc USING (stud_id);

3) 마틴 1 : 31.735ms

SELECT s.stud_id, s.name
   FROM   student s
   WHERE  student_id IN (
   SELECT student_id
   FROM   student_club
   WHERE  club_id = 30
   INTERSECT
   SELECT stud_id
   FROM   student_club
   WHERE  club_id = 50);

4) 데릭 : 2.287ms

SELECT s.stud_id,  s.name
FROM   student s
WHERE  s.stud_id IN (SELECT stud_id FROM student_club WHERE club_id = 30)
AND    s.stud_id IN (SELECT stud_id FROM student_club WHERE club_id = 50);

5) Erwin 2 : 2.181ms

SELECT s.stud_id,  s.name
FROM   student s
WHERE  EXISTS (SELECT * FROM student_club
               WHERE  stud_id = s.stud_id AND club_id = 30)
AND    EXISTS (SELECT * FROM student_club
               WHERE  stud_id = s.stud_id AND club_id = 50);

6) Sean : 2.043ms

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club x ON s.stud_id = x.stud_id
JOIN   student_club y ON s.stud_id = y.stud_id
WHERE  x.club_id = 30
AND    y.club_id = 50;

마지막 세 개는 거의 동일하게 수행됩니다. 4) 및 5) 결과는 동일한 쿼리 계획입니다.

늦은 추가 :

멋진 SQL이지만 성능이 따라갈 수 없습니다.

7) ypercube 1 : 148.649ms

SELECT s.stud_id,  s.name
FROM   student AS s
WHERE  NOT EXISTS (
   SELECT *
   FROM   club AS c
   WHERE  c.club_id IN (30, 50)
   AND    NOT EXISTS (
      SELECT *
      FROM   student_club AS sc
      WHERE  sc.stud_id = s.stud_id
      AND    sc.club_id = c.club_id
      )
   );

8) ypercube 2 : 147.497ms

SELECT s.stud_id,  s.name
FROM   student AS s
WHERE  NOT EXISTS (
   SELECT *
   FROM  (
      SELECT 30 AS club_id
      UNION  ALL
      SELECT 50
      ) AS c
   WHERE NOT EXISTS (
      SELECT *
      FROM   student_club AS sc
      WHERE  sc.stud_id = s.stud_id
      AND    sc.club_id = c.club_id
      )
   );

예상대로이 두 가지는 거의 동일하게 수행됩니다. 쿼리 계획 결과 테이블 스캔이 발생하지만 플래너는 여기서 인덱스를 사용하는 방법을 찾지 못합니다.

9) wildplasser 1 : 49.849ms

WITH RECURSIVE two AS (
   SELECT 1::int AS level
        , stud_id
   FROM   student_club sc1
   WHERE  sc1.club_id = 30
   UNION
   SELECT two.level + 1 AS level
        , sc2.stud_id
   FROM   student_club sc2
   JOIN   two USING (stud_id)
   WHERE  sc2.club_id = 50
   AND    two.level = 1
   )
SELECT s.stud_id, s.student
FROM   student s
JOIN   two USING (studid)
WHERE  two.level > 1;

멋진 SQL, CTE를위한 괜찮은 성능. 매우 이국적인 쿼리 계획.
다시 말하지만 9.1이 이것을 어떻게 처리하는지 흥미로울 것입니다. 여기서 사용하는 db 클러스터를 곧 9.1로 업그레이드 할 예정입니다. 아마 나는 전체 shebang을 다시 실행할 것입니다 …

10) wildplasser 2 : 36.986 ms

WITH sc AS (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30,50)
   GROUP  BY stud_id
   HAVING COUNT(*) > 1
   )
SELECT s.*
FROM   student s
JOIN   sc USING (stud_id);

쿼리 2의 CTE 변형). 놀랍게도 정확히 동일한 데이터를 사용하는 쿼리 계획이 약간 다를 수 있습니다. student하위 쿼리 변형이 인덱스를 사용하는에서 순차 스캔을 찾았습니다 .

11) ypercube 3 : 101.482ms

또 다른 늦은 추가 @ypercube. 얼마나 많은 방법이 있는지 긍정적으로 놀랍습니다.

SELECT s.stud_id, s.student
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id = 10                 -- member in 1st club ...
AND    NOT EXISTS (
   SELECT *
   FROM  (SELECT 14 AS club_id) AS c  -- can't be excluded for missing the 2nd
   WHERE  NOT EXISTS (
      SELECT *
      FROM   student_club AS d
      WHERE  d.stud_id = sc.stud_id
      AND    d.club_id = c.club_id
      )
   )

12) 어윈 3 : 2.377ms

@ypercube의 11)은 실제로이 단순한 변형의 마음을 뒤틀는 역방향 접근 방식이며 여전히 누락되었습니다. 최고 고양이만큼 빠르게 수행합니다.

SELECT s.*
FROM   student s
JOIN   student_club x USING (stud_id)
WHERE  sc.club_id = 10                 -- member in 1st club ...
AND    EXISTS (                        -- ... and membership in 2nd exists
   SELECT *
   FROM   student_club AS y
   WHERE  y.stud_id = s.stud_id
   AND    y.club_id = 14
   )

13) 어윈 4 : 2.375ms

믿기 어렵지만 여기에 또 다른 진정으로 새로운 변형이 있습니다. 두 개 이상의 멤버십에 대한 잠재력이 있다고 생각하지만 두 명만있는 최고의 고양이 중 하나이기도합니다.

SELECT s.*
FROM   student AS s
WHERE  EXISTS (
   SELECT *
   FROM   student_club AS x
   JOIN   student_club AS y USING (stud_id)
   WHERE  x.stud_id = s.stud_id
   AND    x.club_id = 14
   AND    y.club_id = 10
   )

동적 클럽 회원 수

즉, 다양한 필터 수입니다. 이 질문은 정확히 두 개의 클럽 회원을 요구했습니다 . 그러나 많은 사용 사례는 다양한 수에 대비해야합니다.

이 관련 이후 답변에 대한 자세한 설명은 다음과 같습니다.

WHERE 절에서 동일한 열을 여러 번 사용

답변

SELECT s.*
FROM student s
INNER JOIN student_club sc_soccer ON s.id = sc_soccer.student_id
INNER JOIN student_club sc_baseball ON s.id = sc_baseball.student_id
WHERE
 sc_baseball.club_id = 50 AND
 sc_soccer.club_id = 30

답변

select *
from student
where id in (select student_id from student_club where club_id = 30)
and id in (select student_id from student_club where club_id = 50)

답변

student_id를 원하면 다음을 수행하십시오.

    Select student_id
      from student_club
     where club_id in ( 30, 50 )
  group by student_id
    having count( student_id ) = 2

학생의 이름도 필요하면 :

Select student_id, name
  from student s
 where exists( select *
                 from student_club sc
                where s.student_id = sc.student_id
                  and club_id in ( 30, 50 )
             group by sc.student_id
               having count( sc.student_id ) = 2 )

club_selection 테이블에 두 개 이상의 클럽이있는 경우 :

Select student_id, name
  from student s
 where exists( select *
                 from student_club sc
                where s.student_id = sc.student_id
                  and exists( select *
                                from club_selection cs
                               where sc.club_id = cs.club_id )
             group by sc.student_id
               having count( sc.student_id ) = ( select count( * )
                                                   from club_selection ) )

답변

SELECT *
FROM   student
WHERE  id IN (SELECT student_id
              FROM   student_club
              WHERE  club_id = 30
              INTERSECT
              SELECT student_id
              FROM   student_club
              WHERE  club_id = 50)

또는 더 쉽게 더 일반적인 솔루션으로 확장하는 n클럽 것을 피합니다 INTERSECT(MySQL을 사용할 수 없습니다) 및 IN(로 이것의 성능이 MySQL을 빨아 )

SELECT s.id,
       s.name
FROM   student s
       join student_club sc
         ON s.id = sc.student_id
WHERE  sc.club_id IN ( 30, 50 )
GROUP  BY s.id,
          s.name
HAVING COUNT(DISTINCT sc.club_id) = 2

답변

다른 CTE. 깔끔해 보이지만 일반 하위 쿼리의 groupby와 동일한 계획을 생성 할 수 있습니다.

WITH two AS (
    SELECT student_id FROM tmp.student_club
    WHERE club_id IN (30,50)
    GROUP BY student_id
    HAVING COUNT(*) > 1
    )
SELECT st.* FROM tmp.student st
JOIN two ON (two.student_id=st.id)
    ;

테스트하려는 사람들을 위해 내 생성 테스트 데이터 사본 :

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp;

CREATE TABLE tmp.student
    ( id INTEGER NOT NULL PRIMARY KEY
    , sname VARCHAR
    );

CREATE TABLE tmp.club
    ( id INTEGER NOT NULL PRIMARY KEY
    , cname VARCHAR
    );

CREATE TABLE tmp.student_club
    ( student_id INTEGER NOT NULL  REFERENCES tmp.student(id)
    , club_id INTEGER NOT NULL  REFERENCES tmp.club(id)
    );

INSERT INTO tmp.student(id)
    SELECT generate_series(1,1000)
    ;

INSERT INTO tmp.club(id)
    SELECT generate_series(1,100)
    ;

INSERT INTO tmp.student_club(student_id,club_id)
    SELECT st.id  , cl.id
    FROM tmp.student st, tmp.club cl
    ;

DELETE FROM tmp.student_club
WHERE random() < 0.8
    ;

UPDATE tmp.student SET sname = 'Student#' || id::text ;
UPDATE tmp.club SET cname = 'Soccer' WHERE id = 30;
UPDATE tmp.club SET cname = 'Baseball' WHERE id = 50;

ALTER TABLE tmp.student_club
    ADD PRIMARY KEY (student_id,club_id)
    ;

답변

따라서 고양이 피부를 만드는 방법은 여러 가지가 있습니다 . 더 완벽하게 만들기 위해 두 개 를 더
추가하겠습니다 .

1) 먼저 GROUP, JOIN 나중에

온전한 데이터 모델 가정 (student_id, club_id)입니다 고유 의를 student_club. Martin Smith의 두 번째 버전은 다소 비슷하지만 먼저 그룹에 합류합니다. 더 빠를 것입니다.

SELECT s.id, s.name
  FROM student s
  JOIN (
   SELECT student_id
     FROM student_club
    WHERE club_id IN (30, 50)
    GROUP BY 1
   HAVING COUNT(*) > 1
       ) sc USING (student_id);

2) 존재

물론 고전적인 EXISTS. Derek의 IN. 간단하고 빠릅니다. (MySQL에서는를 사용하는 변형보다 훨씬 빠릅니다 IN) :

SELECT s.id, s.name
  FROM student s
 WHERE EXISTS (SELECT 1 FROM student_club
               WHERE  student_id = s.student_id AND club_id = 30)
   AND EXISTS (SELECT 1 FROM student_club
               WHERE  student_id = s.student_id AND club_id = 50);