Creating a View That Joins Two Tables

Whether a view supports INSERT, UPDATE, and DELETE, or only ever supports SELECT, matters more once a view starts joining tables together than it did for the single-table views covered so far. For pure reporting, a read-only view is exactly what's wanted anyway, since the goal is reviewing results, not writing back to the underlying tables. It's worth flagging early in this lesson specifically because most views built on a join between two or more tables fall into the read-only category by default, a point this lesson returns to once an actual join view is on the table.

This isn't a limitation unique to views, either; it's a direct consequence of what a join actually does. A join combines rows from separate tables into a single apparent row, and that combination is often not reversible in an unambiguous way, exactly the problem this lesson demonstrates concretely a bit further on.

A view remains, underneath all of this, a stored SELECT statement with a name attached, a predefined query the database re-runs on demand. It can draw from one table or many, and regardless of how many tables sit behind it, a view lets you:

• Structure data the way a particular user or group of users naturally thinks about it.
• Restrict access so a user sees and modifies exactly what they need, nothing more.
• Summarize data drawn from multiple tables for reporting purposes.

Before extending to joins, a quick refresher on the syntax already covered:

CREATE VIEW MyView
AS SELECT * FROM Authors
WHERE Au_State = 'AZ';

SELECT * FROM MyView;

Querying MyView returns every Authors column for authors based in Arizona, without the SELECT statement needing to mention Au_State at all; that condition lives entirely in the view. (If you're working against a web-hosted version of the pubs database for this course, note that CREATE VIEW statements like this one may not be permitted against it.)

The actual objective of this lesson is a view built on a join, not a single table. Suppose two tables:

orders    (order_id, customer_id, order_date)
customers (customer_id, customer_name, city)

A view combining them looks like this:

CREATE VIEW order_customer
    (order_id, order_date, customer_id, customer_name, city)
AS
    SELECT o.order_id,
           o.order_date,
           c.customer_id,
           c.customer_name,
           c.city
    FROM orders AS o
    INNER JOIN customers AS c
        ON o.customer_id = c.customer_id;

This is a standard, non-materialized view: no rows are stored, and each query against order_customer re-runs the join fresh. Once created, it's queried exactly like any single-table view:

SELECT * FROM order_customer
WHERE city = 'London';

ORDER_ID  ORDER_DATE   CUSTOMER_ID  CUSTOMER_NAME   CITY
1001      2026-03-04   45           Aiko Tanaka     London
1008      2026-03-11   45           Aiko Tanaka     London
1014      2026-03-19   62           Marcus Webb     London

Every column in this result came from either orders or customers, but the SELECT statement that produced it never mentioned either table by name; it only referenced order_customer. The join happened once, when the view was defined, not once per query against it.

A few practical details worth internalizing while building a view like this:

• Qualify column names with a table alias (o.customer_id, c.customer_id) whenever the same column name could plausibly come from either table, so there's no ambiguity about which one a reference means. This matters even when the columns aren't literally named the same in this particular pair of tables, since it's a habit that keeps working as tables get added or renamed later.
• Give the view an explicit column list when the SELECT list would otherwise produce duplicate or unclear names, exactly what order_customer's column list above accomplishes. Both orders and customers have a customer_id column, and without the explicit list, a query against the view would have no clean way to distinguish which table's version of that value it was looking at, even though only one physical value exists in this case since the join condition guarantees they match.
• Creating the view requires both CREATE privilege in the target schema and SELECT privilege on every table the view's query references, orders and customers both in this case. Missing the privilege on either table fails the CREATE VIEW statement outright, not just later queries against the resulting view.

Every standard join type works inside a view's defining query exactly as it would in a standalone SELECT. A left outer join, keeping every order even when it has no matching customer:

CREATE VIEW order_customer AS
    SELECT o.order_id, o.order_date, c.customer_name
    FROM orders AS o
    LEFT OUTER JOIN customers AS c
        ON o.customer_id = c.customer_id;

A join combined with a filter, narrowing the joined result down further:

CREATE VIEW london_orders AS
    SELECT o.order_id, o.order_date, c.customer_name
    FROM orders AS o
    INNER JOIN customers AS c
        ON o.customer_id = c.customer_id
    WHERE c.city = 'London';

Nothing about the WHERE clause here is different from any single-table view already covered; it filters the joined result exactly the way it would filter a plain table.

A full outer join, keeping every row from both tables regardless of whether a match exists on either side, works the same way too:

CREATE VIEW all_orders_and_customers AS
    SELECT o.order_id, c.customer_name
    FROM orders AS o
    FULL OUTER JOIN customers AS c
        ON o.customer_id = c.customer_id;

This surfaces orders with no matching customer record and customers with no orders on file, side by side, something neither an inner join nor a one-sided outer join alone would show. RIGHT OUTER JOIN works as the mirror image of LEFT OUTER JOIN, keeping every row from the second table listed regardless of a match on the first; which of the two to reach for is purely a matter of which table's unmatched rows the reporting question actually needs to see, not a difference in how either behaves inside a view. Which join type belongs in a given view depends entirely on the reporting question being asked, whether missing matches on one side, the other, or both need to stay visible, not on any restriction specific to views themselves.

This is where the read-only/updatable distinction from the opening of this lesson becomes concrete. WITH CHECK OPTION only makes sense on a view that's already updatable, and most two-table (or more) join views simply aren't, under the standard's rules: updatability generally requires one key-preserving table and no row-collapsing operations like aggregation. Treat a join view as read-only by default unless you've specifically confirmed it meets those conditions; order_customer, london_orders, and the outer-join variant above are all reporting views, not targets for INSERT or UPDATE.

It's worth seeing concretely why an attempt to write through order_customer runs into trouble. Suppose an update tried to change a customer's name through the view:

-- Ambiguous: which table does customer_name actually belong to?
UPDATE order_customer
SET customer_name = 'Aiko T.'
WHERE order_id = 1001;

customer_name genuinely belongs to customers, not orders, but the view has flattened both tables into one apparent row, and a single order_customer row can correspond to one orders row joined against one customers row, fine so far, except that same customer might also appear in a dozen other rows of order_customer, one per order they've placed. Should the update change that customer's name everywhere they appear in the view, or only in the one row referenced by order_id = 1001? The standard has no answer for a join shaped like this one, which is exactly why it simply disallows the write rather than guessing.

The same join mechanics scale to three or more tables. Consider a database with EMPLOYEE, PROJECT, and WORKS_ON tables, and a view showing which employees work on which projects:

CREATE VIEW WORKS_ON1 AS
SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE
JOIN WORKS_ON ON Ssn = Essn
JOIN PROJECT ON Pno = Pnumber;

This uses explicit JOIN ... ON syntax throughout. Older SQL code often expresses the identical join using a comma-separated FROM list with the join conditions moved into WHERE instead:

-- Older style, same result, join conditions folded into WHERE
CREATE VIEW WORKS_ON1 AS
SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn = Essn AND Pno = Pnumber;

Both produce the same view. The explicit JOIN ... ON form is generally preferred in current practice, since it separates join logic from filtering logic clearly, ON states what makes rows match, WHERE states what to keep, rather than mixing both purposes into a single list of conditions that becomes harder to read as more tables are added.

A view can combine a join with aggregation just as easily as a single table can:

CREATE VIEW DEPT_INFO (Dept_name, No_of_emps, Total_sal)
AS
SELECT Dname, COUNT(*), SUM(Salary)
FROM DEPARTMENT
JOIN EMPLOYEE ON Dnumber = Dno
GROUP BY Dname;

WORKS_ON1 didn't need an explicit column list, since every selected column already had a clear, unique name inherited directly from its source table. DEPT_INFO does need one, since COUNT(*) and SUM(Salary) have no column name to inherit on their own. This is the same rule already covered for order_customer: name the columns explicitly whenever the query itself wouldn't produce clear names automatically.

Querying either view returns exactly what its name suggests:

SELECT * FROM DEPT_INFO
ORDER BY Total_sal DESC;

DEPT_NAME    NO_OF_EMPS  TOTAL_SAL
Research          8         62400.00
Sales              5         41250.00
Headquarters       3         29800.00

DEPT_INFO is a clean illustration of why a view like this can never be updatable: Total_sal is a sum across however many employees belong to each department, and there's no single EMPLOYEE row an update to that total could sensibly map back to.

Once created, a join view is queried exactly like any other view, the same principle covered in an earlier lesson on selecting from a view to refine results:

SELECT Fname, Lname
FROM WORKS_ON1
WHERE Pname = 'ProductX';

This retrieves the first and last names of every employee working on the ProductX project, without the query needing to repeat any of the join logic that made WORKS_ON1 possible in the first place. That join happened once, inside the view's own definition; every query against WORKS_ON1 afterward benefits from it without re-specifying it.

A handful of errors account for most of the trouble writing a join view for the first time.

Ambiguous column references. If both orders and customers happened to have a column named status, writing SELECT status in the view's defining query without qualifying it would fail outright, since the database has no way to know which table's status was intended. This is exactly why qualifying columns with a table alias, o.status or c.status, matters more inside a join than it did for any single-table view covered earlier in this course.

Forgetting the column list on an aggregate view. Omitting the explicit column list on something shaped like DEPT_INFO doesn't cause an error outright in every product, but it does leave the aggregate columns with unclear, implementation-generated names, exactly the kind of ambiguity the column list exists to prevent.

Assuming a join view can be written to just because a single-table view can. As covered above, most join views fail the standard's updatability test by default. Confirm a specific view actually qualifies before designing an application around writing through it, rather than discovering the restriction only when an INSERT unexpectedly fails.

Picking the wrong join type for the reporting question. An inner join silently drops rows with no match on either side. If the actual question requires seeing orders with no customer record, or customers with no orders, an inner join produces a result that looks complete but has quietly excluded exactly the rows most worth investigating.

Looking Ahead

This lesson covered several points worth carrying forward:

• A view built on a join is still just a stored query; it re-runs the join fresh on every access, exactly like a single-table view re-runs its own simpler query.
• Qualifying column names and supplying an explicit column list matter more once a join is involved, since a single-table view rarely has the ambiguity a join introduces between two tables' worth of columns.
• Every standard join type, inner, left, right, full outer, works inside a view's defining query exactly as it would in a standalone SELECT, and the right choice depends entirely on the reporting question, not on any restriction specific to views.
• Most join views are read-only by default under the standard's updatability rules, since a combined row often has no single, unambiguous underlying row to write a change back to.
• Explicit JOIN ... ON syntax is generally preferable to older comma-separated FROM lists with join conditions folded into WHERE, even though both produce identical results, since separating match logic from filter logic stays readable as more tables get added.

Complete the exercise below to practice building a view that joins two tables.
Create View - Exercise