Slide 1
SQL (MySQL)
MySQL and Set
SQL discussed in previous lecture are for
Oracle.
However, mysql does not support the
following SQL operations!
minus / except
intersect
What should we do?
Write the query in an alternative way.
Use our knowledge in relational algebra!
SQL (MySQL)
Relational algebra
Revisit
Set Union ∪
Set difference (minus) –
Set Intersection ∩
Join
Division ÷
SQL (MySQL)
Union
x y
1 A
2 B
3 C
4 D
x y
1 A
3 C
x y
1 A
2 B
3 C
4 D
a b
(select * from a)
union
(select * from b);
x y
1 A
2 B
3 C
4 D
1 A
3 C
(select * from a)
union all
(select * from b);
∪
SQL (MySQL)
Difference
x y
1 A
2 B
3 C
4 D
x y
1 A
3 C
a b
x y
2 B
4 D
select * from a
where (x,y) not in (select *
from b);
–
For every tuple in a, check that the tuple values does not exists in b;
For every tuple in a, check that it is not in b;
x y
2 B
4 D
select * from a
where not exists (select * from b
where b.x=a.x and b.y=a.y)
SQL (MySQL)
Intersection
x y
1 A
2 B
3 C
4 D
x y
1 A
3 C
a b
x y
1 A
3 C
select * from a
where (x,y) in (select * from b);
∩
For every tuple in a, check that the tuple values also exists in b;
For every tuple in a, check that it is also in b;
x y
1 A
3 C
select * from a
where exists (select * from b
where b.x=a.x and b.y=a.y)
SQL (MySQL)
Join (recap)
Natural join
+—–+———+——+——+——-+——+——+
| pid | name | dept | rank | sal | cid | year |
+—–+———+——+——+——-+——+——+
| p1 | Adam | CS | asst | 6000 | c1 | 2011 |
| p1 | Adam | CS | asst | 6000 | c2 | 2012 |
| p2 | Bob | EE | asso | 8000 | c2 | 2012 |
+—–+———+——+——+——-+——+——+
SQL (MySQL)
Join
Natural join
+—–+———+——+——+——-+——+——+——+
| pid | name | dept | rank | sal | pid | cid | year |
+—–+———+——+——+——-+——+——+——+
| p1 | Adam | CS | asst | 6000 | p1 | c1 | 2011 |
| p1 | Adam | CS | asst | 6000 | p1 | c2 | 2012 |
| p2 | Bob | EE | asso | 8000 | p2 | c2 | 2012 |
+—–+———+——+——+——-+——+——+——+
select *
from PROF inner join TEACH
on PROF.pid = TEACH.pid;
SQL (MySQL)
Join
Left Outer Join (also check right outer join)
+—–+———+——+——+——-+——+——+——+
| pid | name | dept | rank | sal | pid | cid | year |
+—–+———+——+——+——-+——+——+——+
| p1 | Adam | CS | asst | 6000 | p1 | c1 | 2011 |
| p1 | Adam | CS | asst | 6000 | p1 | c2 | 2012 |
| p2 | Bob | EE | asso | 8000 | p2 | c2 | 2012 |
| p3 | Calvin | CS | full | 10000 | NULL | NULL | NULL |
| p4 | Dorothy | EE | asst | 5000 | NULL | NULL | NULL |
| p5 | Emily | EE | asso | 8500 | NULL | NULL | NULL |
+—–+———+——+——+——-+——+——+——+
select * from PROF left outer join TEACH on PROF.pid = TEACH.pid;
http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg
SQL (MySQL)
Division (revisit)
(select distinct y from T1)
minus
select distinct y from (
(select * from (select distinct y from T1), T2)
minus
(select * from T1)
)
S1 – S2 = {y, x} – {x} = {y}
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
T1 T2
SQL (MySQL)
Division (MySQL)
MySQL
– does not support minus!
– But we can use not in / not exists.
S1 – S2 = {y, x} – {x} = {y}
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
T1 T2
SQL (MySQL)
Division (MySQL: no minus)
select distinct y
from T1
where y not in (select distinct y
from ((select distinct y from T1) as Tt, T2)
where (y,x) not in (select * from T1)
);
S1 – S2 = {y, x} – {x} = {y}
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
MySQL: Every derived table must have its own alias
T1 T2
S1 S2
SQL (MySQL)
Division (MySQL: no minus)
select distinct y
from T1
where y not in (select distinct y
from ((select distinct y from T1) as Tt, T2)
where (y,x) not in (select * from T1)
);
(select distinct y from T1)
minus
select distinct y from (
(select * from (select distinct y from T1), T2)
minus
(select * from T1)
)
Same colour shows same block
Rewrite using not in
If you understand the relational algebra, you understand the above.
SQL (MySQL)
Division (not exists)
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
T1 T2
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
fo
r
lo
o
p
T1 T2
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
fo
r
lo
o
p
fo
r
lo
o
p
T1 T2
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
fo
r
lo
o
p
fo
r
lo
o
p
fo
r
lo
o
p
T1 T2
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = ‘A’ and
s.x = 1
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = ‘A’ and
s.x = 1
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
y x
A 1
T1 T2
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = ‘A’ and
s.x = 2
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = ‘A’ and
s.x = 2
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
y x
A 2
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = ‘A’ and
s.x = 2
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
y x
A 2
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = ‘A’ and
s.x = 2
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
y x
A 3
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = ‘A’ and
s.x = 2
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
x
empty
✓
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
✓
y x
A 1
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
✓
y x
A 2
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
✓
y x
A 3
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists ( select * from T1 as s
where s.y = ‘A’ and
s.x = 2
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
x
empty
✓
✓
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists (select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
✓
✓
✓
y x
B 1
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists (select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
✓
✓
✓
y x
B 2
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists (select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
✓
✓
✓
y x
B 3
✓
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists (select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
✓
✓
✓
x
3
✓
SQL (MySQL)
Division – analysis
select distinct y
from T1
where not exists ( select * from T2
where not exists (select * from T1 as s
where s.y = T1.y and
s.x = T2.x
)
);
y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3
÷
x
1
2
3
y
A=
where clause – tuple filter
T1 T2
✓
✓
✓
SQL (MySQL)
MySQL and Set
This slide is outside Exam Syllabus
There are at least three other techniques to
rewrite Division SQL queries in MySQL.
http://users.abo.fi/soini/divisionEnglish.pdf
See blackboard for a cache.
A clear pictorial explanation is also provided.
http://users.abo.fi/soini/divisionEnglish.pdf