Lecture 4 - Joins

Question 1

What is an inner join?

Answer 1

A condition that matches FK and PKs in SQL?

Question 2

What is EQUIJOIN?

Answer 2

it is another name for inner join

Question 3

Example of inner / equi join.

Answer 3

SELECT firstname , lastname, address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber)

Question 4

Unusual syntax example of inner join

Answer 4

SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dname = ‘Research’ %selection condition
AND DNO = DNUMBER; %equi-join condition

Question 5

Inner join only works when …

Answer 5

there is a matching tuple i.e. FK is not null

Question 6

What is an outer join?

Answer 6

joining on attributes but includes NULLs

Question 7

What is an outer left join?

Answer 7

LEFT OUTER JOIN (LR LEFT OUTER JOIN RR)
- Every tuple in the left relation LR must appear in result
- If no matching tuple exists, just add NULL values for attributes of
right relation RR

Question 8

What is an outer right join?

Answer 8

RIGHT OUTER JOIN (LR RIGHT OUTER JOIN RR)
- Every tuple in the right relation RR must appear in result
- `If no matching tuple exists, just add NULL values for attributes of left
relation LR

Question 9

Example of left outer join?

Answer 9

SELECT E.Lname, S.Lname
FROM (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS S
ON E.Super_SSN = S.SSN)

Question 10

What is this doing:

SELECT E.Fname, E.Minit, E.Lname, D.Dname
FROM EMPLOYEE AS E LEFT OUTER JOIN DEPARTMENT AS D
ON E.SSN = D.MGR_SSN

Answer 10

We are getting all employees and what department they’re in charge of if any (NULL if not)

Question 11

With joins, do NULL values represent uncertainity or certainity?

Answer 11

Certainity, we know the context i.e. employee has no supervisor or employee is not a department manager