Database Systems Eero Kettunen

1
Subqueries

• In WHERE clause
• select from s where sno in (select sno from
  sp)
• In FROM clause
• select max(tot) from (select sno, sum(qty) from
  sp group by sno) t(sno,tot)
• Scalar subqueries in scalar expressions
• select sname, status / (select sum(status) from
  s) from s where status (select max(status) from
  s)

2
Non-correlated subqueries

• Non-correlated subqueries are complete SELECT
  statements that can be executed independently
  from the main query.
• As we can see subqueries as expressions,
  non-correlated subqueries are expressions, the
  outcomes of which can be evaluated without the
  main querys tables data.
• Experiment execute the subqueries in the
  previous slide separately from the main query and
  after that the main query as a whole.

3
Correlated subqueries

• Correlated subqueries are SELECT statements,
  which cannot be executed independently, because
  they include references to the main querys
  tables data.
• I.e., correlated subquery expression evaluations
  need data from the main querys table
• for each main query table row the expression is
  evaluated using data from that row.

4
Correlated subquery - Example 1
select a from one where (select count() from two
where two.b one.b) 1
one a b x 1 y 2 z 3
two c b 1 1 2 1 3 3
Evaluating the subquery for the first main query
table row...
select x where (select count() from two where
two.b 1 ) 1
5
Correlated subquery - Example 1
select a from one where (select count() from two
where two.b one.b) 1
one a b x 1 y 2 z 3
two c b 1 1 2 1 3 3
Evaluating the subquery for the second main query
table row...
select y where (select count() from two where
two.b 2 ) 1
6
Correlated subquery - Example 1
select a from one where (select count() from two
where two.b one.b) 1
one a b x 1 y 2 z 3
two c b 1 1 2 1 3 3
Evaluating the subquery for the third main query
table row...
select z where (select count() from two where
two.b 3 ) 1
7
Correlated subquery - Example 2
select a, (select count() from two where two.b
one.b) as q from one
one a b x 1 y 2 z 3
two c b 1 1 2 1 3 3
Evaluating the subquery for the first main query
table row...
select x, (select count() from two where two.b
1 )
a q x 2
RESULT SET
select x, 2
8
Correlated subquery - Example 2
select a, (select count() from two where two.b
one.b) as q from one
one a b x 1 y 2 z 3
two c b 1 1 2 1 3 3
Evaluating the subquery for the second main query
table row...
select y, (select count() from two where two.b
2 )
a q x 2 y 0
RESULT SET
select y, 0
9
Correlated subquery - Example 2
select a, (select count() from two where two.b
one.b) as q from one
one a b x 1 y 2 z 3
two c b 1 1 2 1 3 3
Evaluating the subquery for the third main query
table row...
select z, (select count() from two where two.b
3 )
a q x 2 y 0 z 1
RESULT SET
select z, 1
Ready.
10
Analyse

• select sname, (select count() from sp where
  sp.sno s.sno) from s
• select sname, count()from s join sp on s.sno
  sp.snogroup by sname
• select sname, count()from s left join sp on
  s.sno sp.snogroup by sname
• What did go wrong!!!???

11
Analyse

• select sname, count(sp.sno)from s left join sp
  on s.sno sp.snogroup by sname
• select sname, count(s.sno)from s left join sp on
  s.sno sp.snogroup by sname
• Whats the difference!!!???

12
Analyse

• select sname from s where (select max(qty) from
  sp where sp.sno s.sno) lt (select avg(maxqty)
  from (select max(qty) from sp group by sno)
  spm(maxqty))
• select pname, weight from p p1 where (select
  count() from p p2 where p2.weight lt p1.weight) lt
  1 -- try also lt 2, lt 3, ...

13
Application

• Two suppliers are competitors, if there is at
  least one part they both supply and if the
  suppliers are located in the same city.
• Write a query that returns a result set where
  each row shows one competitor pair and the number
  of parts they both supply (e.g., S1S43).

14
Application

• Lets build the solution step by step. How about
  first listing all supplier pairs suppliying same
  parts and the parts in common
• select sp1.sno as sno1, sp2.sno as
  sno2, sp1.pno as pnofrom sp sp1, sp sp2where
  sp1.pno sp2.pno and sp1.sno ltgt
  sp2.snoorder by sno1, sno2

15
Application

• Now we can count the number of parts in common
• select sp1.sno as sno1, sp2.sno as
  sno2, count() as incommonfrom sp sp1, sp
  sp2where sp1.pno sp2.pno and sp1.sno ltgt
  sp2.snogroup by sp1.sno, sp2.sno order by sno1,
  sno2

16
Application

• Information about cities should be included
• select sno1, sno2, incommon from (select
  sp1.sno as sno1, sp2.sno as sno2, count() as
  incommon from sp sp1, sp sp2 where sp1.pno
  sp2.pno and sp1.sno ltgt sp2.sno group by sp1.sno,
  sp2.sno) spt join s s1 on s1.sno spt.sno1
  join s s2 on s2.sno spt.sno2where s1.city
  s2.cityorder by sno1, sno2

17
Application

• How aboutselect sp1.sno as sno1, sp2.sno as
  sno2, count() as incommonfrom sp sp1, sp
  sp2where sp1.pno sp2.pno and sp1.sno ltgt
  sp2.snogroup by sp1.sno, sp2.snohaving (select
  city from s where sno sp1.sno) (select
  city from s where sno sp2.sno) order by sno1,
  sno2

18
Application

• Orselect sp12.sno1, sp12.sno2, count(sp12.pno)
  as incommon from ( select sp1.sno as sno1,
  sp2.sno as sno2, sp2.pno as pno from sp sp1, sp
  sp2 where sp1.pno sp2.pno and sp1.sno ltgt
  sp2.sno ) sp12 group by sp12.sno1, sp12.sno2
  having exists ( select s1.sname, s2.sname from s
  s1, s s2 where s1.city s2.city and s1.sno
  sp12.sno1 and s2.sno sp12.sno2 ) order by
  sp12.sno1, sp12.sno2
• Analyse this!