Unit 4: Database Joins

I. Orientation

A relational database stores related facts in separate tables and combines them through common attributes. Database joins express relationships between rows, while views provide controlled virtual representations of data. Transactional control ensures that groups of database operations remain reliable even when errors, concurrent users, or system failures occur.

• Relational foundation: Tables consist of rows and columns; a primary key uniquely identifies a row, and a foreign key refers to a key in another table.
• Matching principle: A join combines rows according to a condition, commonly equality between a foreign key and a primary key.
• Three-valued logic: SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN when NULL is involved.
• Set-oriented processing: SQL describes the required result rather than the exact execution algorithm; the DBMS may choose nested-loop, hash, or merge joins.
• Data reliability: Transactions follow the ACID properties—atomicity, consistency, isolation, and durability.
• Schema abstraction: A view stores a query definition rather than normally storing a separate copy of its result.

II. Database Joins — Combining Related Relations

A database join is a relational operation that produces a result by pairing rows from two or more tables according to a join predicate. The predicate may compare keys, ranges, or other expressions.

A. Concept and Need of Database Joins

The concept of a join is to reconstruct meaningful information distributed across normalized tables without duplicating the same facts.

• Purpose: A join combines attributes from related tables. For example, Orders.customer_id can be matched with Customers.customer_id to display the customer’s name with each order.
• Normalization support: Separate Customers and Orders tables reduce repetition. A join reconstructs the combined report when needed.
• Join predicate: The condition identifies which rows correspond.

  SELECT c.customer_name, o.order_id, o.order_date
  FROM Customers AS c
  JOIN Orders AS o
    ON c.customer_id = o.customer_id;

Here, c and o are table aliases, and customer_id is the matching attribute.

• Cardinality effects: One customer may match many orders, producing multiple result rows. A one-to-one relationship usually produces at most one paired row per entity.
• Qualified columns: If both tables contain customer_id, writing c.customer_id and o.customer_id prevents ambiguity.
• Filtering stages: ON determines row pairing; WHERE filters the joined result. Moving a condition from ON to WHERE can change the result of an outer join.
• Cartesian product risk: Omitting a suitable join condition can pair every row in one table with every row in another. For tables with m and n rows, this can produce m × n combinations.

B. Database Joins (Inner, Left, Right, Self)

The principal join types differ in how they treat rows that do not find a match.

• General syntax: The basic form is:

  SELECT column_list
  FROM table_a AS a
  [JOIN_TYPE] table_b AS b
    ON a.key = b.foreign_key;

JOIN_TYPE specifies the matching behavior; key and foreign_key identify related columns.

1. Inner join: Returns only rows satisfying the join condition.

   SELECT e.employee_name, d.department_name
   FROM Employees AS e
   INNER JOIN Departments AS d
     ON e.department_id = d.department_id;

• Matching rows: An employee whose department_id has no corresponding department is excluded.
• Default form: JOIN generally means INNER JOIN.
• Use: Suitable when the report must contain complete relationships, such as products that have matching categories.

2. Left join: Returns every row from the left table and matching rows from the right table; unmatched right-side columns become NULL.

   SELECT c.customer_name, o.order_id
   FROM Customers AS c
   LEFT JOIN Orders AS o
     ON c.customer_id = o.customer_id;

• Preserved table: Customers is preserved even when a customer has made no order.
• Finding nonmatches: WHERE o.order_id IS NULL identifies customers without orders.
• Condition placement: A filter such as o.status = 'Shipped' in WHERE may remove unmatched customers; placing it in ON can preserve them.

3. Right join: Returns every row from the right table and matching rows from the left table.

   SELECT e.employee_name, d.department_name
   FROM Employees AS e
   RIGHT JOIN Departments AS d
     ON e.department_id = d.department_id;

• Preserved table: Every department appears, including departments with no employees.
• Equivalent rewriting: Most right joins can be rewritten as a left join by reversing table order, often improving readability.
• Unmatched values: Missing employee columns appear as NULL.

4. Self join: Joins a table to itself using different aliases, commonly to represent hierarchical or comparative relationships.

   SELECT e.employee_name AS employee,
          m.employee_name AS manager
   FROM Employees AS e
   LEFT JOIN Employees AS m
     ON e.manager_id = m.employee_id;

• Two logical roles: e represents the employee and m represents the manager, although both refer to Employees.
• Outer behavior: LEFT JOIN includes top-level employees whose manager_id is NULL.
• Alias requirement: Aliases are essential because the same table occurs twice.

• Worked example: If Customers contains IDs 1 and 2, while Orders contains an order for customer 1 only, an inner join returns customer 1; a left join returns both customers, with NULL for customer 2’s order.
• Performance considerations: Indexes on join columns can reduce search cost. The optimizer may select a hash join for large unsorted inputs or a merge join when indexed or sorted data is available.

C. Applications and Limitations

Joins are powerful for reporting and analysis, but their correctness depends on keys, cardinality, and predicates.

• Applications: They support customer-order reports, employee-manager hierarchies, inventory-category analysis, and multi-table dashboards.
• Duplicate results: Joining a parent to multiple child rows intentionally repeats parent attributes; aggregation may be needed to produce one row per parent.
• Null handling: NULL = NULL is not TRUE in ordinary SQL comparison, so null-valued keys do not match through =.
• Ambiguous relationships: Joining on a non-unique column, such as a person’s name, can create incorrect many-to-many combinations.
• Efficiency: Select only needed columns, restrict rows early where appropriate, and index frequently joined primary and foreign keys.

III. Views — Virtualized Query Results

A view is a named, stored query that behaves like a virtual table when referenced. It can simplify complex joins, hide sensitive columns, and provide a stable interface to changing base tables.

A. Views

The purpose of a view is to present selected data through a reusable and controlled relational interface.

• Definition: A view is created with CREATE VIEW:

  CREATE VIEW CustomerOrders AS
  SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
  FROM Customers AS c
  INNER JOIN Orders AS o
    ON c.customer_id = o.customer_id;

CustomerOrders is the view name; the defining SELECT specifies its columns and rows.

• Querying: Users can query the view like a table:

  SELECT customer_name, order_id
  FROM CustomerOrders
  WHERE order_date >= DATE '2025-01-01';

• Data abstraction: Users need not know the underlying table names or join conditions, reducing repeated SQL and accidental inconsistencies.
• Security: A view can expose customer_name and order_id while hiding sensitive columns such as payment details. Permissions can be granted on the view rather than the base tables.
• Logical independence: Applications may continue using a view even when the underlying query is reorganized, provided the view’s exposed columns remain compatible.
• Updatability: Simple views based on one table may permit INSERT, UPDATE, or DELETE; views containing joins, grouping, DISTINCT, or calculated columns are often non-updatable or restricted by the DBMS.
• Materialized distinction: An ordinary view computes its query when accessed. A materialized view stores results physically and requires refresh management, trading storage and freshness for faster reads.
• Limitations: A view does not automatically improve performance, and nested or complex views can make optimization and debugging difficult.

IV. Transactional Control — Managing Reliable Operations

Transactional control groups SQL statements into logical units so that related changes are either completed together or undone together. It is essential for preserving consistency in multi-step operations.

A. Transactional Control

Transactional control uses commands such as COMMIT, ROLLBACK, and SAVEPOINT to manage the boundary and outcome of a transaction.

• Transaction definition: A transaction is a sequence of operations treated as one unit. A bank transfer may debit one account and credit another.
• Atomicity: All operations succeed or none take effect. If the debit succeeds but the credit fails, ROLLBACK removes the debit.
• Consistency: A committed transaction must preserve database constraints, such as a foreign key or a balance rule.
• Isolation: Concurrent transactions should not improperly expose intermediate changes to one another. Isolation levels regulate phenomena such as dirty reads, non-repeatable reads, and phantom reads.
• Durability: After COMMIT, the DBMS must preserve changes despite a later crash, normally through logging and recovery mechanisms.
• COMMIT: Permanently makes the current transaction’s changes visible according to the DBMS’s isolation and visibility rules.
• ROLLBACK: Reverses uncommitted changes since the transaction began or since the relevant savepoint.
• SAVEPOINT: Establishes a partial rollback position:

  START TRANSACTION;

  UPDATE Accounts
  SET balance = balance - 500
  WHERE account_id = 101;

  SAVEPOINT after_debit;

  UPDATE Accounts
  SET balance = balance + 500
  WHERE account_id = 202;

  COMMIT;

START TRANSACTION begins the unit; 500 is the transferred amount; the two UPDATE statements modify the source and destination accounts. If the second operation fails, ROLLBACK TO after_debit can undo later work, although the exact command syntax varies by DBMS.

• Failure handling: If an error occurs before commitment, the application should roll back rather than leave only part of a business operation applied.
• Autocommit: In autocommit mode, each individual statement may be committed automatically. Multi-statement operations should explicitly control the transaction boundary.
• DDL behavior: Commands such as CREATE, ALTER, or DROP may cause implicit commits in some DBMSs, so transactional behavior must be checked for the specific system.
• Concurrency and joins: A transaction that reads joined tables may see different data depending on isolation level; a concurrent update can change which rows satisfy the join unless the DBMS provides an appropriate consistency guarantee.
• Practical limitations: Long transactions hold locks or versions longer, increasing contention. Transactions should therefore be kept focused, committed promptly, and designed to handle deadlocks through retry or rollback logic.