Lesson 1

Introduction to Table Joins and User Views

The majority of the work in creating a database happens during the design phase. As a database designer, your job is to create tables that store data efficiently, enforce business rules, and prevent users from inadvertently deleting or corrupting important information.
In the previous module you focused on normalization—shaping tables into clean, well-defined relations. In this module, you will learn how to:

Combine those tables at query time using joins^[1], and
Expose customized “windows” into the data using user views^[2].

Joins and views are core features of every modern relational database (Oracle Database, SQL Server, PostgreSQL, MySQL, and others) and are essential for building secure, maintainable applications.

Learning Objectives for the Table Joins and User Views Module

After completing the lessons in this module, you should be able to:

Explain how to form table joins using common key columns.
Describe the major join types (inner, outer, cross, and self joins).
Define user views and describe how they relate to base tables.
Explain how views are stored and managed in an RDBMS.
List the benefits of creating views for security, simplicity, and abstraction.
Describe how permissions interact with joins and views.
Determine which permissions individual users require to run specific queries and views.

The next lessons will introduce table joins in more detail and then build up to user views based on those joins.

Why Joins Are Needed

Normalized designs intentionally separate data into multiple related tables. This removes redundancy, but it also means that no single table contains the full picture a user often needs.

Joins solve this problem. A join tells the database how to combine rows from two (or more) tables by using a column they have in common. Conceptually, the database creates a virtual table at query time; no new physical table is stored on disk, but the result looks and behaves like a single combined table.

The following example uses two tables, WEATHER and LOCATION, to demonstrate a simple join that you could write in Oracle SQL or any modern relational database.

First, query the WEATHER table:

Table 1 – 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

This table shows current weather conditions by city, but it does not tell you where those cities are located on the globe.

Next, query the LOCATION table:

Table 2 – LOCATION

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 second table contains geographic coordinates but no weather information. However, both tables share a common column: City. That shared column allows you to join the tables and view weather and location information together in a single result set.

To do this, you instruct the database to return rows from both tables where the city names match.

Joined Result (Virtual Table)

The following query joins the two tables based on the shared City column and produces a virtual table at query time:

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 result are those cities that appear in both tables. Cities that exist in LOCATION but not in WEATHER (such as ROME or MADRID) do not appear in the joined result. This is an example of an inner join.

The WHERE clause encodes the relationship between the two tables: it tells the database to keep only those row combinations where WEATHER.City = LOCATION.City. When you write join conditions, you are describing how rows in one table relate to rows in another.

Dealing with Ambiguous Column Names

When two or more tables have columns with the same name, you must qualify the column with the table (or alias) so the database knows which one you mean.

select City, Condition, Temperature, Latitude

If you used this SELECT list in a query that joins WEATHER and LOCATION, the database would return an error stating that the column City (and possibly Latitude) is ambiguous: it exists in more than one table.

The correct approach is to qualify the column with its table name, for example: WEATHER.City or LOCATION.City. In our join query, either choice returns the same values because the join condition ensures the City values are equal in both tables. In more complex joins, however, identically named columns can contain very different data, so choosing the correct table-qualified column becomes critical.

From Joins to User Views

In later lessons, you will build on this example to create user views based on joins. A view can:

Hide unnecessary columns and complex joins behind a simple name.
Expose only the rows and columns a particular user or role is allowed to see.
Provide a stable interface to the data even if the underlying tables change.

By the end of this module, you will be comfortable writing joins like the examples above and wrapping them in views that implement security and simplify application code.

[1]join: An operation that combines rows from two or more tables based on a related column, typically a key or foreign key, producing a single virtual result set.

[2]view: A named, stored SQL query that presents data from one or more tables as a virtual table. Views can limit which rows and columns users see and are often used to simplify queries and enforce security.