Subqueries overal! En best practices!

Gegevens manipuleren in SQL

Mona Khalil

Data Scientist, Greenhouse Software

Zoveel subqueries als je wilt...

Kan meerdere subqueries bevatten in SELECT, FROM, 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');

Formatteer je queries

Lijn SELECT, FROM, WHERE, en GROUP BY uit

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

Annoteren van je queries

/* Deze query filtert voor col1 = 2
en selecteert alleen data uit table1 */
SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 = 2;

Annoteren van je queries

SELECT
    col1,
    col2,
    col3
FROM table1 -- deze tabel heeft 10.000 rijen
WHERE col1 = 2; -- Filter WHERE waarde 2

Inspringen van je queries

Inspringen van je subqueries!

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

Inspringen van je queries

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;

Holywell's SQL Style Guide

Is die subquery nodig?

Subqueries kosten rekenkracht
- Hoe groot is je database?
- Hoe groot is de tabel die je bevraagt?
Is de subquery echt nodig?

Filter elke subquery goed!

Let op je filters!

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

Laten we oefenen!

Gegevens manipuleren in SQL