¡Subconsultas por todas partes! ¡Y las prácticas recomendadas!

Manipulación de datos en SQL

Mona Khalil

Data Scientist, Greenhouse Software

Tantas subconsultas como quieras...

Se pueden incluir varias subconsultas en SELECT, FROM y WHERE

SELECT 
    s.stage,
    ROUND(s.avg_goals,2) AS avg_goal,
    (SELECT AVG(home_goal + away_goal)
     FROM match WHERE season = '2013/2014') AS overall_avg
FROM 
    (SELECT
         stage,
         AVG(home_goal + away_goal) AS avg_goals
     FROM match
     WHERE season = '2013/2014'
     GROUP BY stage) AS s
WHERE 
    s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                   FROM match WHERE season = '2013/2014');

Aplica formato a tus consultas

Alineación de SELECT, FROM, WHERE y GROUP BY

SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 = 2;

Anota tus consultas

/* This query filters for col1 = 2
and only selects data from table1 */
SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 = 2;

Anota tus consultas

SELECT
    col1,
    col2,
    col3
FROM table1 -- this table has 10,000 rows
WHERE col1 = 2; -- Filter WHERE value 2

Sangra tus consultas

¡Sangra tus subconsultas!

SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 IN
        (SELECT id
         FROM table2
         WHERE year = 1991);

Sangra tus consultas

SELECT 
  date, 
  hometeam_id, 
  awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea home win'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea away win'
       WHEN hometeam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea home loss'
       WHEN awayteam_id = 8455 AND home_goal > away_goal
            THEN 'Chelsea away loss'
       WHEN (hometeam_id = 8455 OR awayteam_id = 8455) 
            AND home_goal = away_goal THEN 'Chelsea Tie'
       END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;

Guía de estilo SQL de Holywell

¿Es necesaria esa subconsulta?

Las subconsultas requieren potencia de computación
- ¿Qué tamaño tiene tu base de datos?
- ¿Qué tamaño tiene la tabla que consultas?
¿Es realmente necesaria la subconsulta?

¡Filtra correctamente cada subconsulta!

¡Cuidado con tus filtros!

SELECT 
    s.stage,
    ROUND(s.avg_goals,2) AS avg_goal,
    (SELECT AVG(home_goal + away_goal)
     FROM match WHERE season = '2013/2014') AS overall_avg
FROM 
    (SELECT
         stage,
         AVG(home_goal + away_goal) AS avg_goals
     FROM match
     WHERE season = '2013/2014'
     GROUP BY stage) AS s
WHERE 
    s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                   FROM match WHERE season = '2013/2014');

¡Vamos a practicar!

Manipulación de datos en SQL