Subqueries

En subquery er en spørring inne i en annen spørring.
En subquery kan brukes enten med WHERE eller HAVING.
En subquery bruker i tillegg til de vanlige operatorene =, !=, >, >=, < og <= også operatorene IN, NOT IN, EXISTS, NOT EXISTS, ANY/SOME og ALL.
En subquery må alltid stå på høyre side av en operator.
En subquery kan returnere en eller flere rader og kolonner, men det er kun subqueries som bruker EXISTS/NOT EXISTS som kan returnere flere kolonner.
En subquery kan inneholde en ny subqery, da kaller vi det en nested subquery.

Vanlige operatorer

Skriv ut navn på pasienter som bruker legen Einor Ilderhaug som fastlege
select navn
from menneske m, pasient p
where m.personnr = p.personnr
and p.fastlege_id = (
  select lege_id
  from lege l, menneske m
  where l.personnr = m.personnr
  and m.navn LIKE 'Einor Ilderhaug');
      navn       
-----------------
 Arne B. Olsen
 Ilma S. Arnesen
(2 rows)

Skriv ut navn og adresse på pasienter som ikke bruker legen Einor Ilderhaug som fastlege
select navn, adresse
from menneske m, pasient p
where m.personnr = p.personnr
and p.fastlege_id != (
  select lege_id
  from lege l, menneske m
  where l.personnr = m.personnr
  and m.navn LIKE 'Einor Ilderhaug');
       navn       |             adresse              
------------------+----------------------------------
 Sebastian Soby   | Høstbakken 6, 2074 Jarpasthaugen
 Anne Andersen    | Høstbakken 8, 2074 Jarpasthaugen
 Helga Halstensen | Kammerstredet 3, 2070 Jarpast
 Eivind Moen      | Himlingveien 3, 2072 Jarpast
(4 rows)

IN/NOT IN

Skriv ut pasient_id og navn for alle pasienten som har vært og målt puls og blodtrykk hos legen
select DISTINCT pasient_id, navn
from menneske m, pasient p
where m.personnr = p.personnr
and p.pasient_id IN (
  select pasient_id
  from besoksJournal
  where timebehandling LIKE '%puls%' or timebehandling LIKE '%Puls%'
    or timebehandling LIKE '%blodtrykk%' or timebehandling LIKE '%Blodtrykk%' or timebehandling LIKE '%BT%');
 pasient_id |     navn      
------------+---------------
          1 | Arne B. Olsen
          3 | Eivind Moen
(2 rows)

Skriv ut navn og internnummer for alle leger som ikke har behandlet pasient med pasient_id = 1
select m.navn, l.tlf_intern
from menneske m, lege l
where m.personnr = l.personnr
and l.lege_id NOT IN (
  select id_behandler
  from besoksJournal
  where pasient_id = 1);
        navn         | tlf_intern 
---------------------+------------
 Elling Pedersen     | 3216
 Ano Nym             | 
 Anne Andersen       | 3218
 Petter Johnshaltatt | 3215
 Helle Jensen        | 1113
(5 rows)

EXISTS/NOT EXISTS

Skriv ut navn og behandlingsdato på leger som også har vært pasienter
select navn, dato_legebesok
from menneske, besoksJournal, lege
where menneske.personnr = lege.personnr
and besoksJournal.id_behandler = lege.lege_id
and EXISTS (
  select *
  from pasient
  where lege.personnr = pasient.personnr);
     navn      |   dato_legebesok    
---------------+---------------------
 Anne Andersen | 2003-12-10 08:45:00
(1 row)

Skriv ut navn på pasienter som ikke har blitt behandlet ved Jarpastelia Legekontor
select DISTINCT navn
from menneske, pasient, lege
where menneske.personnr = pasient.personnr
and pasient.fastlege_id = lege.lege_id
and NOT EXISTS (
  select *
  from legekontor
  where legekontor.legekontor_id = lege.legekontor
  and navn = 'Jarpastelia Legekontor');
       navn       
------------------
 Helga Halstensen
(1 row)

ANY/SOME og ALL

SOME og ANY er synonymer; de betyr det samme og kan brukes om hverandre

Finn de legekontorene som har ansatt færre leger enn hvertfall ett annet legekontor
select navn AS "Legekontor", count(navn) AS "Antall ansatte leger"
from legekontor, lege
where legekontor_id = legekontor
group by navn
having count(navn) < ANY(
  select count(legekontor)
  from lege
  group by legekontor)
union
  select navn AS "Legekontor", 0 AS "Antall ansatte leger"
  from legekontor 
  where legekontor_id NOT IN (
    select legekontor 
    from lege 
    where legekontor is not NULL);
      Legekontor       | Antall ansatte leger 
-----------------------+----------------------
 Det Tomme Legesenter  |                    0
 Jarpast-spesialistene |                    1
 Sentrum Legesenter    |                    1
(3 rows)

select navn AS "Legekontor", count(navn) AS "Antall ansatte leger"
from legekontor, lege
where legekontor_id = legekontor
group by navn
having count(navn) < SOME(
  select count(legekontor)
  from lege
  group by legekontor)
union
  select navn AS "Legekontor", 0 AS "Antall ansatte leger"
  from legekontor 
  where legekontor_id NOT IN ( 
    select legekontor 
    from lege 
    where legekontor is not NULL);
      Legekontor       | Antall ansatte leger 
-----------------------+----------------------
 Det Tomme Legesenter  |                    0
 Jarpast-spesialistene |                    1
 Sentrum Legesenter    |                    1
(3 rows)

Finn det legekontoret som har ansatt flest leger
select navn AS "Legekontor", count(navn) AS "Antall ansatte leger"
from legekontor, lege
where legekontor_id = legekontor
group by navn
having count(navn) >= ALL(
  select count(legekontor)
  from lege
  group by legekontor);
       Legekontor       | Antall ansatte leger 
------------------------+----------------------
 Jarpastelia Legekontor |                    4
(1 row)