Slide 1
SQL
Past Muddiest Points
RA => SQL Natural Join
SQL: Natural Join
SQL: Division
SQL
SQL
Natural Join
SQL
How many attributes / columns are there?
SQL
Natural Join
S1-S2: name, dept, rank, sal S2-S1: cid, yearA: pid
SQL
SQL
Past Muddiest Points
RA => SQL Natural Join
SQL: Join Condition, Inner Join, Natural Join
SQL: Division
SQL
Past Muddiest Points
Phone codes
select *
from City, Area
Where City.areaCode = Area.areaCode
areaCode cityCode cityName areaCode areaName
40 01792 Swansea 40 United Kingdom
86 10 Beijing 86 China
1 416 Toronto 1 North America
1 212 NewYork 1 North America
Explicit Join Condition
areaCode cityCode cityName
40 01792 Swansea
86 10 Beijing
1 416 Toronto
1 212 NewYork
areaCode areaName
40 United Kingdom
852 Hong Kong
1 North America
86 China
City Area
SQL
Past Muddiest Points
Phone codes
select *
from City inner join Area
on City.areaCode = Area.areaCode
areaCode cityCode cityName areaCode areaName
40 01792 Swansea 40 United Kingdom
86 10 Beijing 86 China
1 416 Toronto 1 North America
1 212 NewYork 1 North America
areaCode cityCode cityName
40 01792 Swansea
86 10 Beijing
1 416 Toronto
1 212 NewYork
areaCode areaName
40 United Kingdom
852 Hong Kong
1 North America
86 China
City Area
SQL
Past Muddiest Points
Phone codes
select *
from City natural join Area
areaCode cityCode cityName areaName
40 01792 Swansea United Kingdom
86 10 Beijing China
1 416 Toronto North America
1 212 NewYork North America
areaCode cityCode cityName
40 01792 Swansea
86 10 Beijing
1 416 Toronto
1 212 NewYork
areaCode areaName
40 United Kingdom
852 Hong Kong
1 North America
86 China
City Area
SQL
Past Muddiest Points
One day, a guy added a column
areaCode cityCode cityName lastupdate
40 01792 Swansea 1990
86 10 Beijing 1988
1 416 Toronto 1975
1 212 NewYork 1971
areaCode areaName lastupdate
40 United Kingdom 1980
852 Hong Kong 1978
1 North America 1979
86 China 1987
City Area
select *
from City, Area
Where City.areaCode = Area.areaCode
select *
from City inner join Area
on City.areaCode = Area.areaCode
areaCode cityCode cityName lastupdate areaCode areaName lastupdate
40 01792 Swansea 1990 40 United Kingdom 1980
86 10 Beijing 1988 86 China 1987
1 416 Toronto 1975 1 North America 1979
1 212 NewYork 1971 1 North America 1979
SQL
Past Muddiest Points
One day, a guy added a column
areaCode cityCode cityName lastupdate
40 01792 Swansea 1990
86 10 Beijing 1988
1 416 Toronto 1975
1 212 NewYork 1971
areaCode areaName lastupdate
40 United Kingdom 1980
852 Hong Kong 1978
1 North America 1979
86 China 1987
City Area
areaCode cityCode cityName areaName lastupdate
empty
select *
from City natural join Area
Natural Join breaks the whole system!
SQL
Past Muddiest Points
One day, the guy change the schema (by mistake)
select *
from City natural join Area
areaCode areaName
40 United Kingdom
852 Hong Kong
1 North America
86 China
City Area
select *
from City, Area
where City.areaCode = Area.areaCode
select *
from City inner join Area
on City.areaCode = Area.areaCode
Cartesian Product!SQL Warning:
No areaCode
It breaks the system!
But no warning.
cityCode cityName
01792 Swansea
10 Beijing
416 Toronto
212 NewYork
SQL
Past Muddiest Points
Lesson learnt
Database related codes may not update for years.
If something wrong, you need some warnings reminders.
Old School Wisdom
Use join with explicit join condition in SQL
Never use Natural Join Keyword in SQL
Natural join in Relational Algebra is fine.
Part of the theory, designed for easy manipulation.
You should use the concept in Relational Algebra and SQL too.
But practically, treat Natural Join keyword in SQL
as if it never exists, avoids it likes a plague.
In CW2 & Exam, if you use natural join keyword in SQL, no marks.
Learn the correct way of translating concept of natural join from RA to SQL.
http://www.dba-oracle.com/oracle_news/2004_2_19_rittman.htm
http://www.postgresqltutorial.com/postgresql-natural-join
https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax
aka, compile-time warning in
Java / C / C++. Spot mistakes early.
http://www.dba-oracle.com/oracle_news/2004_2_19_rittman.htm
http://www.postgresqltutorial.com/postgresql-natural-join
https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax
SQL
Past Muddiest Points
RA => SQL Natural Join
SQL: Natural Join
SQL: Division
SQL
Division
RA: Division was a bit confusing and to
understand
Results:
p1
p4
SQL
Division
Which operations go first?
SQL
SQL and Division
SQL
SQL and Division
SQL
SQL and Division
SQL
SQL and Division
SQL
SQL and Natural Join
SQL
SQL and Natural Join
SQL
SQL and Division