Introduction to Table Joins and User Views in Relational Databases
The majority of the work in creating a database is in the design phase.
As a database designer, your job is to create tables that store data efficiently and prevent users from inadvertently deleting important data.
This module introduces joins and views, features of relational database management systems
that build on the table construction and normalization techniques you studied earlier in this course.
- join: An operation that links table records based on data in common fields.
- View: A database object that enables you to define the columns and rows that a specific user can see. A view can also serve as a tool for enforcing security within your database.
After completing the lessons in this module, you should be able to:
- Explain how to form table joins
- Describe the types of joins
- Define user views
- Explain how views are stored in an RDBMS
- List the benefits of creating user views
- Describe permissions
- Determine which permissions individual users require
The next lesson introduces you to table joins.
Introductory example of joining two tables
Even though the joining of the tables is done by fingers and mind rather than a CPU, it is a real, legitimate relational database. It is even fairly close to what a relational application designer
would call normalized, a word that simply means the data is collected into natural groupings.
Query the first table which is WEATHER.
select City, Condition, Temperature from WEATHER;
CITY CONDITION TEMPERATURE
----------- ----------- -----------
LIMA RAIN 45
PARIS CLOUDY 81
MANCHESTER FOG 66
ATHENS SUNNY 97
CHICAGO RAIN 66
SYDNEY SNOW 29
SPARTA CLOUDY 74
Query the second table which is the LOCATION table:
select City, Longitude, EastWest, Latitude, NorthSouth
CITY LONGITUDE E LATITUDE N
------------------------- --------- - -------- -
ATHENS 23.43 E 37.58 N
CHICAGO 87.38 W 41.53 N
CONAKRY 13.43 W 9.31 N
LIMA 77.03 W 12.03 S
MADRAS 80.17 E 13.05 N
MANCHESTER 2.15 W 53.3 N
MOSCOW 37.35 E 55.45 N
PARIS 2.2 E 48.52 N
SHENYANG 123.3 E 41.48 N
ROME 12.29 E 41.54 N
TOKYO 139.5 E 35.42 N
SYDNEY 151.1 E 33.52 S
SPARTA 22.27 E 37.05 N
MADRID 3.14 W 40.24 N
This is much more than you need, and it does not have any weather information.
Yet these two tables, WEATHER and LOCATION, have a column in common which is City.
You can therefore put the information from the two tables together by joining them. You merely use the where clause to tell Oracle what the two tables have
select WEATHER.City, Condition, Temperature, Latitude,
NorthSouth, Longitude, EastWest
from WEATHER, LOCATION
where WEATHER.City = LOCATION.City;
CITY CONDITION TEMPERATURE LATITUDE N LONGITUDE E
----------- ----------- ----------- -------- - --------- -
ATHENS SUNNY 97 37.58 N 23.43 E
CHICAGO RAIN 66 41.53 N 87.38 W
LIMA RAIN 45 12.03 S 77.03 W
MANCHESTER FOG 66 53.3 N 2.15 W
PARIS CLOUDY 81 48.52 N 2.2 E
SPARTA CLOUDY 74 37.05 N 22.27 E
SYDNEY SNOW 29 33.52 S 151.1 E
Notice that the only rows in this combined table are those where the same city is in both tables.
The where clause is still executing your logic.
The logic you gave described the relationship between the two tables.
It says, "select those rows in the WEATHER table and the LOCATION table where the cities are equal".
If a city was only in one table, it would have nothing to be equal to in the other table. The notation used in the select statement is TABLE.ColumnName, which is WEATHER.City.
The select clause has chosen those columns from the two tables that you would like to see displayed; any columns in either table that you did not ask for are simply ignored. If the first line had simply said this:
select City, Condition, Temperature, Latitude
then Oracle would not have known to which City you were referring. Oracle would tell you that the column name City was ambiguous.
The correct wording in the select clause is WEATHER.City or LOCATION.City. In this example, it will not make a bit of difference which of these alternatives is used, but you will encounter cases where the choice of identically named columns from two or more tables will contain very