Lesson 1

Table Joins User Views

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^[1] and views^[2], which are features of relational database management systems that build on the table construction and normalization techniques you studied earlier in this course.

Learning Objectives for Table Joins Module

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.

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.

Table 1

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:

Table 2

select City, Longitude, EastWest, Latitude, NorthSouth
from LOCATION;
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 in common.

Virtual Table

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 different data.

[1]join: An operation that links table records based on data in common fields.

[2]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.

Data-Driven Science and Engineering