mysql - update - Cómo filtrar resultados SQL en una relación has-many-through

select into mysql

Question

Suponiendo que tengo las tablas student , club y student_club :

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

Quiero saber cómo encontrar a todos los estudiantes tanto en el club de fútbol (30) como en el de béisbol (50).
Si bien esta consulta no funciona, es lo más parecido que tengo hasta ahora:

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

Answer 1

Diferentes planes de consulta en la consulta 2) y 10)

Probé en un DB de la vida real, por lo que los nombres difieren de la lista catskin. Es una copia de seguridad, por lo que nada cambió durante todas las pruebas (excepto cambios menores en los catálogos).

Consulta 2)

SELECT a.*
FROM ef.adr a
JOIN (
SELECT adr_id
FROM ef.adratt
WHERE att_id IN (10,14)
GROUP BY adr_id
HAVING COUNT(*) > 1) t using (adr_id);
Merge Join (cost=630.10..1248.78 rows=627 width=295) (actual time=13.025..34.726 rows=67 loops=1)
Merge Cond: (a.adr_id = adratt.adr_id)
-> Index Scan using adr_pkey on adr a (cost=0.00..523.39 rows=5767 width=295) (actual time=0.023..11.308 rows=5356 loops=1)
-> Sort (cost=630.10..636.37 rows=627 width=4) (actual time=12.891..13.004 rows=67 loops=1)
Sort Key: adratt.adr_id
Sort Method: quicksort Memory: 28kB
-> HashAggregate (cost=450.87..488.49 rows=627 width=4) (actual time=12.386..12.710 rows=67 loops=1)
Filter: (count(*) > 1)
-> Bitmap Heap Scan on adratt (cost=97.66..394.81 rows=2803 width=4) (actual time=0.245..5.958 rows=2811 loops=1)
Recheck Cond: (att_id = ANY (''{10,14}''::integer[]))
-> Bitmap Index Scan on adratt_att_id_idx (cost=0.00..94.86 rows=2803 width=0) (actual time=0.217..0.217 rows=2811 loops=1)
Index Cond: (att_id = ANY (''{10,14}''::integer[]))
Total runtime: 34.928 ms

Consulta 10)

WITH two AS (
SELECT adr_id
FROM ef.adratt
WHERE att_id IN (10,14)
GROUP BY adr_id
HAVING COUNT(*) > 1
)
SELECT a.*
FROM ef.adr a
JOIN two using (adr_id);
Hash Join (cost=1161.52..1261.84 rows=627 width=295) (actual time=36.188..37.269 rows=67 loops=1)
Hash Cond: (two.adr_id = a.adr_id)
CTE two
-> HashAggregate (cost=450.87..488.49 rows=627 width=4) (actual time=13.059..13.447 rows=67 loops=1)
Filter: (count(*) > 1)
-> Bitmap Heap Scan on adratt (cost=97.66..394.81 rows=2803 width=4) (actual time=0.252..6.252 rows=2811 loops=1)
Recheck Cond: (att_id = ANY (''{10,14}''::integer[]))
-> Bitmap Index Scan on adratt_att_id_idx (cost=0.00..94.86 rows=2803 width=0) (actual time=0.226..0.226 rows=2811 loops=1)
Index Cond: (att_id = ANY (''{10,14}''::integer[]))
-> CTE Scan on two (cost=0.00..50.16 rows=627 width=4) (actual time=13.065..13.677 rows=67 loops=1)
-> Hash (cost=384.68..384.68 rows=5767 width=295) (actual time=23.097..23.097 rows=5767 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1153kB
-> Seq Scan on adr a (cost=0.00..384.68 rows=5767 width=295) (actual time=0.005..10.955 rows=5767 loops=1)
Total runtime: 37.482 ms

Answer 2

@ erwin-brandstetter Por favor, compare este punto:

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

Es como el número 6) de @sean, simplemente más limpio, supongo.

Answer 3

Como nadie ha agregado esta versión (clásica):

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

o similar:

SELECT s.*
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.student_id = s.id
AND sc.club_id = c.club_id
)
)

Una prueba más con un enfoque ligeramente diferente. Inspirado por un artículo en Explain Extended: múltiples atributos en una tabla de EAV: GROUP BY vs. NOT EXISTS :

SELECT s.*
FROM student_club AS sc
JOIN student AS s
ON s.student_id = sc.student_id
WHERE sc.club_id = 50 --- one option here
AND NOT EXISTS
( SELECT *
FROM
( SELECT 30 AS club_id --- all the rest in here
--- as in previous query
) AS c
WHERE NOT EXISTS
( SELECT *
FROM student_club AS scc
WHERE scc.student_id = sc.id
AND scc.club_id = c.club_id
)
)

Otro enfoque:

SELECT s.stud_id
FROM student s
EXCEPT
SELECT stud_id
FROM
( SELECT s.stud_id, c.club_id
FROM student s
CROSS JOIN (VALUES (30),(50)) c (club_id)
EXCEPT
SELECT stud_id, club_id
FROM student_club
WHERE club_id IN (30, 50) -- optional. Not needed but may affect performance
) x ;

Answer 4

Estaba curioso. Y como todos sabemos, la curiosidad tiene fama de matar gatos.

Entonces, ¿cuál es la forma más rápida de despellejar a un gato?

El entorno preciso de desollado para esta prueba:

• PostgreSQL 9.0 en Debian Squeeze con una RAM decente y configuraciones.
• 6.000 estudiantes, 24,000 membresías de club (datos copiados de una base de datos similar con datos de la vida real).
• Desviación leve del esquema de nombres en la pregunta: club.id es club.club_id y club.id es club.club_id aquí.
• Puse el nombre de las consultas después de su autor en este hilo, con un índice donde hay dos.
• Ejecuté todas las consultas un par de veces para completar el caché, luego elegí el mejor de 5 con EXPLAIN ANALYZE.

• Los índices relevantes (deberían ser los óptimos, siempre que no tengamos conocimiento previo de qué clubes se consultarán):

  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 no es requerido por la mayoría de las consultas aquí.
  Las claves primarias implementan índices únicos automáticamente en PostgreSQL.
  El último índice es para compensar esta falla conocida de los índices de varias columnas en PostgreSQL:

Se puede usar un índice de árbol B de varias columnas con condiciones de consulta que involucran cualquier subconjunto de las columnas del índice, pero el índice es más eficiente cuando hay restricciones en las columnas principales (la izquierda).

Resultados:

Total de tiempos de EXPLAIN ANALYZE.

1) Martin 2: 44.594 ms

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.217 ms

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) Martin 1: 31.735 ms

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) Derek: 2.287 ms

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.181 ms

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.043 ms

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;

Los últimos tres realizan más o menos lo mismo. 4) y 5) dan como resultado el mismo plan de consulta.

Adiciones tardías:

Fancy SQL, pero el rendimiento no puede seguir el ritmo.

7) ypercube 1: 148.649 ms

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.497 ms

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
)
);

Como se esperaba, esos dos realizan casi lo mismo. Los resultados del plan de consulta en escaneos de tabla, el planificador no encuentra una manera de usar los índices aquí.

9) wildplasser 1: 49.849 ms

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;

Fancy SQL, rendimiento decente para un CTE. Plan de consulta muy exótico.
De nuevo, sería interesante cómo 9.1 maneja esto. Voy a actualizar el cluster db usado aquí a 9.1 pronto. Tal vez vuelva a ejecutar todo el 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);

Variante CTE de la consulta 2). Sorprendentemente, puede resultar en un plan de consulta ligeramente diferente con los mismos datos exactos. Encontré un escaneo secuencial en el student , donde la subversión-variante utilizó el índice.

11) ypercube 3: 101.482 ms

Otra adición tardía @ycubcube. Es positivamente sorprendente, cuántas formas hay.

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) erwin 3: 2.377 ms

@ ypercube''s 11) es en realidad solo el enfoque inverso de esta variante más simple, que aún faltaba. Se realiza casi tan rápido como los mejores gatos.

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) erwin 4: 2.375 ms

Difícil de creer, pero aquí hay otra variante genuinamente nueva. Veo potencial para más de dos membresías, pero también se ubica entre los mejores gatos con solo dos.

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
)

Número dinámico de membresías del club

En otras palabras: variando el número de filtros. Esta pregunta requería exactamente dos membresías de club. Pero muchos casos de uso tienen que prepararse para un número variable.

Discusión detallada en esta respuesta posterior relacionada:

• Usar la misma columna varias veces en la cláusula WHERE

Answer 5

Otro CTE. Se ve limpio, pero probablemente generará el mismo plan que un grupo por medio de una subconsulta normal.

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)
;

Para aquellos que quieran probar, una copia de mis datos de prueba de prueba:

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)
;

Answer 6

Si solo quieres student_id, entonces:

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

Si también necesita el nombre del alumno, entonces:

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 )

Si tiene más de dos clubes en una mesa de selección de club, entonces:

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 ) )

Answer 7

Entonces, hay más de una manera de despellejar a un gato .
Agregaré dos más para hacerlo, bueno, más completo.

1) GRUPO primero, ÚNASE más tarde

Asumiendo un modelo de datos sensato donde (student_id, club_id) es único en student_club . La segunda versión de Martin Smith es similar, pero se une primero, luego a grupos. Esto debería ser más rápido:

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) EXISTE

Y, por supuesto, está el clásico EXISTS . Similar a la variante de Derek con IN . Simple y rápido. (En MySQL, esto debería ser bastante más rápido que la variante con 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);

Answer 8

-- EXPLAIN ANALYZE
WITH two AS (
SELECT c0.student_id
FROM tmp.student_club c0
, tmp.student_club c1
WHERE c0.student_id = c1.student_id
AND c0.club_id = 30
AND c1.club_id = 50
)
SELECT st.* FROM tmp.student st
JOIN two ON (two.student_id=st.id)
;

El plan de consulta:

 Hash Join (cost=1904.76..1919.09 rows=337 width=15) (actual time=6.937..8.771 rows=324 loops=1)
Hash Cond: (two.student_id = st.id)
CTE two
-> Hash Join (cost=849.97..1645.76 rows=337 width=4) (actual time=4.932..6.488 rows=324 loops=1)
Hash Cond: (c1.student_id = c0.student_id)
-> Bitmap Heap Scan on student_club c1 (cost=32.76..796.94 rows=1614 width=4) (actual time=0.667..1.835 rows=1646 loops=1)
Recheck Cond: (club_id = 50)
-> Bitmap Index Scan on sc_club_id_idx (cost=0.00..32.36 rows=1614 width=0) (actual time=0.473..0.473 rows=1646 loops=1)
Index Cond: (club_id = 50)
-> Hash (cost=797.00..797.00 rows=1617 width=4) (actual time=4.203..4.203 rows=1620 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 57kB
-> Bitmap Heap Scan on student_club c0 (cost=32.79..797.00 rows=1617 width=4) (actual time=0.663..3.596 rows=1620 loops=1)
Recheck Cond: (club_id = 30)
-> Bitmap Index Scan on sc_club_id_idx (cost=0.00..32.38 rows=1617 width=0) (actual time=0.469..0.469 rows=1620 loops=1)
Index Cond: (club_id = 30)
-> CTE Scan on two (cost=0.00..6.74 rows=337 width=4) (actual time=4.935..6.591 rows=324 loops=1)
-> Hash (cost=159.00..159.00 rows=8000 width=15) (actual time=1.979..1.979 rows=8000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 374kB
-> Seq Scan on student st (cost=0.00..159.00 rows=8000 width=15) (actual time=0.093..0.759 rows=8000 loops=1)
Total runtime: 8.989 ms
(20 rows)

Por lo tanto, todavía parece querer el escaneo seq en el estudiante.

Answer 9

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)

O una solución más general más fácil de extender a n clubs y que evita INTERSECT (no disponible en MySQL) y IN (ya que el rendimiento de esto apesta en 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

Answer 10

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

Answer 11

SELECT s.stud_id, s.name
FROM student s,
(
select x.stud_id from
student_club x
JOIN student_club y ON x.stud_id = y.stud_id
WHERE x.club_id = 30
AND y.club_id = 50
) tmp_tbl
where tmp_tbl.stud_id = s.stud_id
;

Uso de la variante más rápida (Sr. Sean en el cuadro del Sr. Brandstetter). Puede ser una variante con solo unir a solo la matriz student_club tiene derecho a vivir. Entonces, la consulta más larga tendrá solo dos columnas para calcular, la idea es hacer que la consulta sea más delgada.

Answer 12

WITH RECURSIVE two AS
( SELECT 1::integer AS level
, student_id
FROM tmp.student_club sc0
WHERE sc0.club_id = 30
UNION
SELECT 1+two.level AS level
, sc1.student_id
FROM tmp.student_club sc1
JOIN two ON (two.student_id = sc1.student_id)
WHERE sc1.club_id = 50
AND two.level=1
)
SELECT st.* FROM tmp.student st
JOIN two ON (two.student_id=st.id)
WHERE two.level> 1
;

Esto parece funcionar razonablemente bien, ya que el escaneo CTE evita la necesidad de dos subconsultas separadas.

¡Siempre hay una razón para usar mal las consultas recursivas!

(Por cierto: mysql no parece tener consultas recursivas)

Answer 13

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)