Interactive Data Transforming
Processing Multiple Tables
Join Operator
SELECT <attribute list> <- return info
FROM <relation list> <- join operator
WHERE <conditions> <- filtering
- NATURAL JOIN
- INNER JOIN ON<condition>
- INNER JOIN USING(<attribute list>)
- LEFT | RIGHT | FULL JOIN
Inner Join (also explained in lecture 1)
A relational operation that causes two or more tables to be combined into a single table. INNER can
be omitted, i.e. “inner join” same as “join”.
Example: Retrieve patient names and hospitals they have stayed at
SELECT DISTINCT pName, hName
FROM Patient JOIN Stay ON Patient.SSN = Stay.SSN
Same as: (but here only one time the SSN attribute)
SELECT DISTINCT pName, hName
FROM Patient JOIN Stay USING (SSN)
Natural Join
Joining condition is based on equality between values in the common columns
Attributed with the same name of associated tables will appear once only in the returned result
Example: Retrieve patient names and hospitals they have stayed at:
SELECT DISTINCT pName, hName
FROM Patient NATURAL JOIN Stay
So, let’s say there’s a table with each employee and their departmentID. In another table there’s a
departmentID and departmentName. You have 3 departmentIDs: 10, 20 and 30. Let’s say no
employees have departmentID 30, then that name is not gonna show in the results.
Left Join (also explained in lecture 1)
Returns all matched rows PLUS all unmatched rows from the table on the left of the join clause
Uses NULL values in fields of non-matching tuples.
Example: Retrieve patient info: name, SSN, hospital name, reason, date
SELECT pName, SSN, hName, reason, date
FROM Patient JOIN Stay USING (SSN)
Now with patients who haven’t been hospitalized
SELECT pName, SSN, hName, reason, date
FROM Patient LEFT JOIN Stay USING (SSN)
Right Join & Full Outer Join
Right Join: Returns all matched rows PLUS all unmatched rows from the table on the right of the join
clause. Uses NULL values in fields of non-matching tuples.
Full Join: Returns all matched and all unmatched rows from both tables of the join clause. Uses NULL
values in fields of non-matching tuples.
, Indices/Indexes/Indexing Structures
Mechanism to quickly locate data. Consider retrieving students. Typically, we use surname and an
identifier:
SELECT * FROM Student WHERE surname like “Smit%”
SELECT * FROM Student WHERE aanmeldnaam = “…”
Indices to the rescue
- Prepare data by building indices on chosen attributes
- Think of library indices, phonebooks, …
Indexing can speed-up access to the desired attributes. (Set of) attributes used for looking-up records.
- ID number
- {Postcode, Number}
- {Birth date, Surname, Name}
An index file (usually) contains records (index entries) of the form <Search-key, Pointer>
Different types of indices
For example:
- Ordered indices search keys are stored in sorted order E.g. finding all students whose surnames
start with “Smit” or searching for data within a particular range like birthdates between 1990 and
2000.
- Hashed indices search keys distributed uniformly across buckets using a hash function E.g.
finding a student by their unique ID or login name.
Views
A view in SQL is like a virtual table. It doesn’t actually store data, but acts like a table when you query
it. Think of it as a saved query that you can reuse. Can be seen as a virtual table. Based on the result-
set of an SQL statement involving a single or multiple tables. Contains rows and columns, just like a
real table. Attributes in a view are columns from one or more “real” tables in the database.
- Does not exist as a table. No data is associated with it. Definition stored as a query.
Purpose
Views are SQL statements to present the data as if the data were coming from one single table. Views
can be used instead of the SELECT.
So why are views useful?
- Confidentiality, i.e., users can only access views: e.g. don’t show salary for rows of Employee table.
So, it helps you to hide the “salary” column.
- Different attribute labels You can use views to rename columns
- Changes in tables are transparent to users When you add or remove a column, views can hide those
changes from users.
