Databases I JOINS in SQL

1
Databases I JOINS in SQL
versie 2007
Steven KlusenerVrije Universiteit,
Amsterdam (Slides met dank aan Wiebren de Jonge)
2
Voorbeeld Database zie company.sql

• DEPARTMENT
• ID NAME BUDGET
• D1 engineering 500,000
• D2 sales 200,000
• D3 logistics 100,000
• DEPENDENT
• EMPLOYEE ID NAME REL
• ID NAME BDATE dep_id E3 John son
• E1 John 1964-08-28 D1 E3 Sue wife
• E2 Joe 1968-04-04 D1 E4 Suzie
  daughter
• E3 Jack 1969-09-03 D1 E4 Will son
• E4 Will 1971-03-21 D2 E4 Mary wife
• E5 Bridget 1972-01-22 NULL

3
INNER JOIN SELECT-WHERE

• Query Geef alle werknemers en de naam van hun
  afdeling
• SELECT E.id, E.name, D.name
• FROM Employee AS E, Department AS D
• WHERE E.dep_id D.id
• Or
• SELECT E.id, E.name, D.name
• FROM Employee AS E INNER JOIN Department AS D
• ON E.dep_id D.id
• So, a JOIN just combines two (or even more)
  tables
• This will, howeverr, not give the employee
  Bridget, because she as an unknown (NULL)
  department

4
LEFT OUTER JOIN in case of NULLs

• Query Geef alle werknemers en de naam van hun
  afdeling, ook als de afdeling onbekend is.
• SELECT E.id, E.name, D.name
• FROM Employee AS E LEFT OUTER
• JOIN Department AS D
• ON E.dep_id D.id
• This will output rows from the Employee table
  (i.e., the left table) even if the dep_id column
  contains a NULL value.

5
Voorbeeld Database Company

• Query Geef alle werknemers en de naam van hun
  afdeling, ook als de afdeling onbekend is.
• SELECT E.id, E.name, D.name
• FROM Employee AS E LEFT OUTER JOIN Department
  AS D ON E.dep_id D.id

---- --------- ------------- id name
name ---- --------- -------------
1 john engineering 2 joe
engineering 3 jack
engineering 4 will sales
5 bridget NULL
ltltltltltltlt ---- --------- -------------
6
OTHER JOINs

• NATURAL JOIN the joining condition is implicit
• FROM Table1 AS T1 NATURAL JOIN Table2 AS T2
• combines all rows from T1 with rows from T2
  if their columns that occur in both tables have
  the same value
• RIGHT JOIN, opposite to LEFT JOIN (not
  recommended)
• CROSS JOIN (gives full cartesion product, not
  recommended)
• FULL JOIN, returns output for all rows in both
  tables, if no match row can be found, then all
  respective columns are set to NULL

7
NATURAL JOIN

• Query Geef alle familieleden die hetzelfde heten
  als de werknemer
• SELECT D.name, E.id, E.name
• FROM Employee AS E, Dependent AS D
• WHERE E.id D.id AND E.name D.name
• Or
• SELECT E.id, E.name, D.name
• FROM Employee AS E NATURAL JOIN Dependent AS D

8
Another example

• Query Geef alle familieleden en de bijbehorende
  werknemer
• SELECT D.name, E.id, E.name
• FROM Employee AS E, Dependent AS D
• WHERE E.id D.id
• or
• SELECT E.id, E.name, D.name
• FROM Employee AS E JOIN Dependent AS D
• ON E.id D.id
• So, do NOT use the natural JOIN here.
• Conclusion be careful when you choose the names
  of your columns in your datamodel

9
Complexe SQL query

• Geef voor ieder department het D en het aantal
  werknemers (D.w.z. ook als die afdeling helemaal
  geen employees heeft.)
• Vorige week kwamen we uit op
• SELECT D.id,
• COUNT (SELECT
• FROM EMPLOYEE AS E
• WHERE E.dep_id D.id)
• FROM DEPARTMENT AS D
• Echter, de COUNT(ltselect-statementgt) wordt niet
  door MySQL ondersteund !?

10
Complexe SQL query

• Geef voor ieder department het D en het aantal
  werknemers (D.w.z. ook als die afdeling helemaal
  geen employees heeft.)
• Wordt vervolgd ..