CS157A: Introduction to Database Management Systems
Chapter 6:
The Database Language SQL-Part II
Suneuy Kim
CS157A Chapter 6 1
Join Variants
• CROSS JOINàCartesian product • (INNER) JOIN ONàTheta Join
• NATURAL JOINàNatural Join
• LEFT|RIGHT|FULL OUTER JOIN ON
: augment the result of a join by the dangling tuples
CS157A Chapter 6 2
MySQL Join Variants
• In MySQL, Join, Cross Join, Inner Join are the same, working as theta join.
select uName, title, age
from User INNER JOIN Loan ON User.uID = Loan.uID and age < 20;
• Replacing INNER JOIN with Cross Join or Join will not change the result.
CS157A Chapter 6 3
MySQL: Inner Join, Cross Join, and Join (Alternative Syntaxes)
using(a1, a2, ...)Clause
- Thisissimilartoon,butthenameofthejoin
attribute(s) must be the same in each table.
- Thejoinattribute(s)onlyappearsonceinthe result set.
select uName, title
from User INNER JOIN Loan using(uID);
CS157A Chapter 6 4
Outer Join
• Dangling tuple
A tuple that fails to join with any tuple
of the other relation.
• Outer join augments the result of join by the dangling tuples, padded with NULL.
CS157A Chapter 6 5
Outer Join
• LEFT|RIGHT|FULL OUTER JOIN pads dangling tuples from LEFT, RIGHT, or BOTH.
• Theta Join
R LEFT|RIGHT|FULL OUTER JOIN S ON
R NATURAL LEFT|RIGHT|FULL OUTER JOIN S CS157A Chapter 6 6
R
S
c
d
7
40
10
5
30
8
R LEFT|RIGHT|FULL OUTER JOIN S ON b > c
a
b
10
20
50
5
a
b
c
d
10
20
7
40
10
20
10
5
50
5
N
N
LEFT
FULL
a
b
c
d
10
20
7
40
10
20
10
5
50
5
N
N
N
N
30
8
a
10
10
N
b
20
20
N
c
7
10
30
d
40
5
RIGHT
8 CS157A Chapter 6
7
Rewriting left outer join
select uName, User.uID, title, overdue from User, Loan
where User.uID = Loan.uID
union all
select uName, uID, NULL, NULL
from User
where uID not in (select uID from Loan);
CS157A Chapter 6 8
right outer join select uID, uName, title, overdue
from User right outer join Loan using (uID);
CS157A Chapter 6 9
full outer join select uID, uName, title, overdue
from User full outer join Loan using (uID);
[Q] MySQL does not support full outer join. Can
you rewrite full outer join without using join ?
CS157A Chapter 6 10
full outer join without using join
select uName, User.uID, title, overdue from User, Loan
where User.uID = Loan.uID
union all
select uName, uID, NULL, NULL
from User
where uID not in (select uID from Loan) union all
select NULL, uID, title, overdue
from Loan
where uID not in (select uID from User);
CS157A Chapter 6 11
• Commutativity : (A op B) = (B op A)
• Associativity: (AopB)opC=Aop(BopC)
Left|Right outer joins are not commutative. Full outer join is commutative.
Left|Right|Full outer joins are not associative. It is important to think of the order of ( ).
CS157A Chapter 6 12
Example: Violation of Associativity
DROP DATABASE IF EXISTS JOINTEST; CREATE DATABASE JOINTEST;
USE JOINTEST;
CREATE TABLE A (id int, M int); CREATE TABLE B (id int, N int); CREATE TABLE C (id int, val text);
INSERT INTO A VALUES (1, 1); INSERT INTO A VALUES (2, 2); INSERT INTO A VALUES (3, 3);
INSERT INTO B VALUES (1, 1); INSERT INTO B VALUES (2, 3); INSERT INTO B VALUES (4, 5);
INSERT INTO C VALUES (1, ‘X’); INSERT INTO C VALUES (3, ‘Y’); INSERT INTO C VALUES (5, ‘Z’);
CS157A Chapter 6 13
Example: Violation of Associativity
SELECT * FROM (A LEFT OUTER JOIN B ON A.M = B.id)
LEFT OUTER JOIN C ON B.id IS NULL;
SELECT * FROM A LEFT OUTER JOIN (B LEFT OUTER
JOIN C ON B.id IS NULL) ON A.M = B.id;
CS157A Chapter 6 14
On or Where?
select uName, age
from User join Loan on User.uID=Loan.uID where loaned > 3 and title = ‘Bambi’;
select uName, age
from User join Loan on User.uID=Loan.uID and
loaned > 3 and title = ‘Bambi’;
CS157A Chapter 6 15
On or Where?
• With an Inner Join, the clauses are effectively equivalent.
• With an Outer Join, they are not the same. select * from User left outer join Loan on
User.uID = Loan.uID where Loan.overdue = true ; vs.
select * from User left outer join Loan
on User.uID = Loan.uID and Loan.overdue = true;
CS157A Chapter 6 16
select User.uID, uName, Loan.uID, title, overdue from User left join Loan on User.uID = Loan.uID
where Loan.overdue = true;
CS157A Chapter 6 17
select User.uID, uName, Loan.uID, title, overdue
from User left join Loan on User.uID = Loan.uID
and Loan.overdue = true;
CS157A Chapter 6 18
Join using and on together
select U1.uID, U1.uName, U1.age, U2.uID, U2.uName, U2.age
from User U1 join User U2 using (age) where U1.uID < U2.uID;
select U1.uID, U1.uName, U1.age, U2.uID, U2.uName, U2.age
from User U1 join User U2 on U1.age = U2.age where U1.uID < U2.uID;
CS157A Chapter 6 19
Changing three way join to binary join
select *
from Loan join User join Book
on Loan.uID = User.uID and Loan.title = Book.title ; à
select *
from (Loan join User on Loan.uID = User.uID)
join Book on Loan.title = Book.title;
CS157A Chapter 6 20
Natural Join
// Suppose uID is the only common attribute in // User and Loan
select distinct uName, title
from User natural join Loan;
à
select distinct uName, title from User join Loan
on User.uID = Loan.uID;
Note: select * will return relations with a different schema.
CS157A Chapter 6 21
MySQL doesn’t support except (or minus) and intersect
AB
x
y
1
a
3
c
x
y
1
a
2
b
3
c
4
d
DROP TABLE IF EXISTS A;
CREATE TABLE A (x INT, y VARCHAR(5));
DROP TABLE IF EXISTS B;
CREATE TABLE B (x INT, y VARCHAR(5));
INSERT INTO A(x,y) VALUES(1,'a');
INSERT INTO A(x,y) VALUES(2,'b');
INSERT INTO A(x,y) VALUES(3,'c');
INSERT INTO A(x,y) VALUES(4,'d');
INSERT INTO B(x,y) VALUES(1,'a');
INSERT INTO B(x,y) VALUES(3,'c');
CS157A Chapter 6 22
AB
SELECT * FROM A
WHERE (x,y) NOT IN (SELECT *
FROM B);
SELECT * FROM A
WHERE NOT EXISTS
(SELECT * FROM B WHERE B.x =
A.x AND B.y = A.y);
SELECT DISTINCT A.x AS x, A.y AS y
FROM A LEFT OUTER JOIN B USING (x,
y) WHERE B.x IS NULL;
Difference in MySQL
x
y
1
a
3
c
x
y
1
a
2
b
3
c
4
d
A-B
x
y
2
b
4
d
CS157A Chapter 6 23
AB
SELECT * FROM A WHERE (x,y)
IN (SELECT * FROM B);
SELECT * FROM A WHERE EXISTS
(SELECT * FROM B
WHERE B.x=A.x AND B.y =A.y);
SELECT DISTINCT A.x AS x, A.y AS y
FROM A INNER JOIN B USING (x,y);
Intersection in MySQL
x
y
1
a
3
c
x
y
1
a
2
b
3
c
4
d
A ÇB
x
y
1
a
3
c
CS157A Chapter 6 24
Aggregation
• min, max, sum, avg, count
select A1, A2, ..., Anßaggregation appears here. from R1, R2, ..., Rn
whereßapply to the single tuple at a time group by
having ß filter the group
CS157A Chapter 6 25
select avg(age) from User;
select min(age)
from User, Loan
where User.uID = Loan.uID and title = 'Bambi';
select count(*)
from User, Loan
where User.uID = Loan.uID and title = 'Bambi';
CS157A Chapter 6 26
select avg(distinct age)
from User, Loan
where User.uID = Loan.uID and title = 'Bambi' ;
VS
select avg(age)
from User
where uID in
(select uID from Loan where title = 'Bambi');
CS157A Chapter 6 27
Eliminating Duplicates in an Aggregation
select count(distinct uID) from Loan
where title = 'Bambi';
CS157A Chapter 6 28
group by
• We may follow a SELECT-FROM-WHERE expression by GROUP BY and a list of grouping attributes.
• The relation that results from the FROM- WHERE is grouped according to the values of all those attributes, and any aggregation in SELECT is applied only within each group.
CS157A Chapter 6 29
group by
When there is an aggregation in SELECT clause, there are only two types of terms the SELECT clause can have:
1. Aggregations - these terms are evaluated for each group.
2. Groupingattributescanbeunaggregated.
CS157A Chapter 6 30
Example: group by
select title, count(*) from Loan
group by title;
CS157A Chapter 6 31
group by
select title, min(age), max(age) from User, Loan
where User.uID = Loan.uID group by title;
CS157A Chapter 6 32
group by
[Q] To find the Largest span of ages of users who borrowed the same book
select max(mx-mn) from
(select title, min(age) as mn, max(age) as mx from User, Loan
where User.uID = Loan.uID
group by title) ST;
CS157A Chapter 6 33
group by
[Q] To find the number of different books a user loaned
select User.uID, uName, count(distinct title) from User, Loan
where User.uID = Loan.uID
group by User.uID;
CS157A Chapter 6 34
Does it work ?
select User.uID, uName, count(distinct title), title from User, Loan
where User.uID = Loan.uID
group by User.uID
In MySQL, run this query with
1. set sql_mode=only_full_group_by and
2. set sql_mode=""
CS157A Chapter 6 35
Quiz
Number of books loaned by each user. If a user did not loan any book, show 0 for the number of loaned books.
CS157A Chapter 6 36
select User.uID, uName, count(distinct title) from User, Loan
where User.uID = Loan.uID
group by User.uID
union
select User.uID, uName, 0
from User
where uID not in (select uID from Loan);
CS157A Chapter 6 37
HAVING Clauses
• HAVING
• If so, the condition applies to each group, and groups not satisfying the condition are eliminated.
CS157A Chapter 6 38
Requirements on HAVING Conditions 1. An aggregation in a HAVING clause applies
only to the group being tested.
select loaned, count(*) from User
group by loaned having count(*) >2
CS157A Chapter 6
Tests if the current group has more than 2 counts 39
Requirements on HAVING Conditions
2. Any attributes of relations in the FROM clause may be aggregated in the HAVING clause, but only grouping attributes may appear un-aggregated in the HAVING clause.
select loaned, count(*), avg(age)
from User
group by loaned
having avg(age) > 40 and count(*) >=3 ;
CS157A Chapter 6 40
Example: Violation
select loaned, count(*), age
from User
group by loaned
having age > 40 and count(*) >=3 ;
an error.
an error.
CS157A Chapter 6 41
Having
[Q] To find books loaned at least three times select title
from Loan
group by title
having count(*) >=3;
[Q] To find a book loaned by at least three different users. select title
from Loan
group by title
having count(distinct uID) >= 3;
CS157A Chapter 6 42
Without using group by and having To find books with fewer than 3 borrowers
select distinct title from Loan L1 where
( select count(*)
from Loan L2
where L2.title = L1.title) < 3;
select title
from Loan
group by title having count(*) < 3;
CS157A Chapter 6 43
[Q] To find books whose loaner’s maximum age is below the average age of users
select title
from User, Loan
where User.uID = Loan.uID
group by title
having max(age) <(select avg(age) from User);
CS157A Chapter 6 44
Null values and Aggregation
• NULLisignoredinanyaggregationexceptfor count(*)
select sum(age) from User: null is ignored count(*) counts all tuples including null count(age) counts non-null ages.
count(distinct age) counts non-null unique ages.
• NULListreatedasanordinarywhenforminggroups. e.g.) select age, count(*) from user group by age;
• Anyaggregation,exceptcount,overanemptybagof values returns NULL. The count of an empty bag is 0.
CS157A Chapter 6 45
Example: Null values and Aggregation
1. select count(*)
from User
where age is not null;
2. select count(distinct age) from User
where age is not null;
3. select count(distinct age) from User
4. select distinct age from User
Notes:
2 and 3 return the same result.
4 returns distinct ages including null.
CS157A Chapter 6 46
Data Modification
• insert into R values (V1, V2,.., Vn) • insert into R select statement
• delete from R where condition
• updateR
set attribute = expression where condition
• updateR
set A1 = expr1, A2 = expr2, ..., An = exprn where condition
CS157A Chapter 6 47
Insert
• To insert a single tuple: INSERT INTO R (A1, ..., An) VALUES (v1, ..., vn);
• We may add to the relation name a list of attributes. Two reasons to do so:
1. We forget the standard order of attributes for the relation.
2. We don’t have values for all attributes, and we want the system to fill in missing components with NULL or a default value.
CS157A Chapter 6 48
Insert
insert into Book(title,author,copies)
values('This Book', 'That Author', 40);
=
insert into Book values
('This Book', 'That Author', 40);
insert into Book(title, author) values
('This Book', 'That Author');
àThe system will initialize the value of copies to null.
Error: insert into Book(title,author,copies)
values('This Book', 'That Author');
Error: insert into Book
values ('This Book', 'That Author');
CS157A Chapter 6 49
on update
CREATE TABLE USER
(uID INT AUTO_INCREMENT,
uNAME VARCHAR(30),
age INT,
loaned INT,
updatedOn timestamp on update current_timestamp, PRIMARY KEY (uID)
);
When a row is updated, the field (updatedOn in this example) will get the current timestamp.
CS157A Chapter 6 50
on update
insert into user (uname, age, loaned, updatedon)
values('John Smith', 23,4, now());
Inserts a row with the updateon value set to the current timestamp.
1032 | John Smith| 23 | 4 | 2020-09-27 08:43:44
update user set age = 99 where uID = 1032;
The updateon value of the row will set to the current timestamp of updating.
1032 | John Smith | 99 | 4 | 2020-09-27 08:46:42
CS157A Chapter 6 51
AUTO_INCREMENT
CREATE TABLE USER
(uID INT AUTO_INCREMENT,
uNAME VARCHAR(30),
age INT,
loaned INT,
updatedOn timestamp on update current_timestamp, PRIMARY KEY (uID)
);
CS157A Chapter 6 52
AUTO_INCREMENT
• The value of an auto increment attribute is incremented sequentially whenever a new row is added into the database.
• By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record
• To let AUTO_INCREMENT sequence start with another value , use, for example, ALTER TABLE USER AUTO_INCREMENT = 1001;
• The auto increment is commonly used to generate primary keys.
• The defined data type on the auto increment should be large enough to accommodate many records. (e.g. TINYINT limits the number of records
that can be added to the table to 255.)
• When a row is deleted from a table, its auto incremented id is not re-used.
MySQL continues generating new numbers sequentially.
• The LAST_INSERT_ID() function returns the first automatically generated
integer successfully inserted for an AUTO_INCREMENT column.
CS157A Chapter 6 53
Insert
• When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.
e.g.
insert into user (uid, uname, age, loaned,
updatedon) values(1500, 'John Smith',
23,4, now());
CS157A Chapter 6 54
Inserting Many Tuples
• We may insert the entire result of a query into a relation, using the form:
INSERT INTO
insert into Loan
(select uID,’Let”s Read!’,’0000-00-00′,false
from User where uID not in
(select uID from Loan)
);
CS157A Chapter 6 55
[Q] To have users, who loaned Bambi and not being overdue, loan ‘Lion King ‘.
insert Loan
(select uid, ‘Lion King’, UTC_DATE(), false from USER
where uid in
(select uid from Loan where title=’Bambi’ and overdue = false));
CS157A Chapter 6 56
Delete
• To delete tuples satisfying a condition from some relation:
DELETE FROM
CS157A Chapter 6 57
Delete
[Q] To delete a user who borrowed the same books
delete from User where uID in (select uID
from Loan
group by uID
having count(title) <> count(distinct title)) ;
CS157A Chapter 6 58
Delete – Error
delete
from Loan
where uID in
(
select uID
from Loan
group by uID
having count(title) <> count(distinct title) );
Note: You can’t specify the target relation Loan for update in From clause.
CS157A Chapter 6 59
Update
• To change certain attributes in certain tuples of a relation:
UPDATE
SET WHERE
CS157A Chapter 6 60
Update
[Q] To find a user with age < 15, and turn their overdue to false.
update Loan
set overdue = false
where overdue = true and uID in (select uID from user where age < 15);
CS157A Chapter 6 61
update Loan
set overdue=false
where overdue = true and uID in (select uID from user where age =(select max(age)from user natural join Loan where title = 'Bambi'));
Error: Can’t specify the target
loan for update in FROM clause
CS157A Chapter 6 62
DROP VIEW IF EXISTS OldestBambiUser;
CREATE VIEW OldestBambiUser AS
select distinct uID
from user natural join Loan
where title = 'Bambi' and age =
(select max(age)
from user natural join loan
where title = 'Bambi');
update Loan
set overdue = false
where overdue = true and uID in
(select * from OldestBambiUser);
CS157A Chapter 6 63