Unit 4: Database Joins - Subjective Questions

A database join is an operation that combines related rows from two or more tables using a common column or a specified join condition.

Need for joins:

• Relational databases store data in separate tables to reduce redundancy through normalization.
• Joins reconstruct meaningful information distributed across these tables.
• They allow users to retrieve related data in a single result set.
• Joins help maintain data consistency because common information does not need to be duplicated.
• They support complex queries and reports involving multiple entities.

For example, if Customers contains customer details and Orders contains order details, both tables can be connected using customer_id:

SELECT Customers.name, Orders.order_id FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;

Thus, joins make normalized relational data useful for analysis and reporting.

A join condition specifies how rows from different tables are related. It usually compares a primary key in one table with a corresponding foreign key in another table.

Example:

Employees.department_id = Departments.department_id

Here, Departments.department_id may be the primary key, while Employees.department_id is its foreign key.

If the join condition is omitted or written incorrectly:

• Every row of the first table may be combined with every row of the second table.
• This produces a Cartesian product or cross join.
• If one table has rows and another has rows, the result may contain:

rows.

• The result generally contains unrelated and duplicate-looking data.
• Query execution may consume excessive memory and processing time.

Therefore, a correct join condition is essential for both logical accuracy and query performance.

An INNER JOIN returns only those rows for which the join condition is satisfied in both participating tables. Unmatched rows from either table are excluded.

General syntax:

SELECT columns FROM TableA INNER JOIN TableB ON TableA.key_column = TableB.key_column;

Suppose the following tables exist:

• Students(student_id, student_name, course_id)
• Courses(course_id, course_name)

The query is:

SELECT Students.student_name, Courses.course_name FROM Students INNER JOIN Courses ON Students.course_id = Courses.course_id;

Working:

• Each student row is compared with course rows using course_id.
• A result row is generated only when matching course_id values exist.
• A student without a valid course is omitted.
• A course with no enrolled student is also omitted.

An inner join is useful when the result should contain only complete and matching relationships.

A LEFT OUTER JOIN, commonly written as LEFT JOIN, returns all rows from the left table and matching rows from the right table. If no corresponding row exists in the right table, its selected columns contain NULL.

Syntax:

SELECT columns FROM TableA LEFT JOIN TableB ON TableA.key_column = TableB.key_column;

Example:

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

This query displays every customer, including customers who have not placed an order. For such customers, order_id is NULL.

Difference from INNER JOIN:

• INNER JOIN: Returns only matching rows from both tables.
• LEFT JOIN: Returns all left-table rows and matching right-table rows.
• An inner join discards unmatched left rows, whereas a left join preserves them.

A left join is especially useful for identifying missing relationships, such as customers without orders, using a condition such as WHERE Orders.order_id IS NULL.

A RIGHT OUTER JOIN, or RIGHT JOIN, returns all rows from the right table and the matching rows from the left table. When no left-table match exists, the selected left-table columns contain NULL.

Example:

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

The query returns every department, including departments that currently have no employees.

A right join can be rewritten as a left join by reversing the order of the tables:

SELECT Employees.employee_name, Departments.department_name FROM Departments LEFT JOIN Employees ON Departments.department_id = Employees.department_id;

Both queries produce equivalent logical results, although column order may differ depending on the SELECT list.

Many developers prefer LEFT JOIN because reading queries consistently from the preserved table on the left can improve clarity.

The three joins differ mainly in how they handle unmatched rows.

Key observations:

• Use an INNER JOIN when only complete matches are required.
• Use a LEFT JOIN when every row of the first table must be retained.
• Use a RIGHT JOIN when every row of the second table must be retained.
• Interchanging table order converts a left join into an equivalent right join.
• Outer joins can detect missing relationships by testing the non-preserved table's key with IS NULL.

The choice of join depends on which table's unmatched rows must appear in the final result.

A SELF JOIN joins a table with itself. It is useful when rows within the same table have hierarchical or comparative relationships. Since the same table participates more than once, aliases are required to distinguish its instances.

Suppose Employees has the columns employee_id, employee_name, and manager_id. The manager_id refers to another row's employee_id.

Query:

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

Explanation:

• E represents employees.
• M represents managers, although both refer to Employees.
• The join matches each employee's manager_id with the manager's employee_id.
• A left join retains top-level employees whose manager_id is NULL.

Self joins can also be used for category hierarchies, prerequisite relationships, comparing employees in the same department, and finding pairs of related records.

Assume the table Employees(employee_id, employee_name, department_id).

A suitable self-join query is:

SELECT E1.employee_name AS employee_1, E2.employee_name AS employee_2, E1.department_id FROM Employees E1 INNER JOIN Employees E2 ON E1.department_id = E2.department_id AND E1.employee_id < E2.employee_id;

Explanation:

• E1 and E2 are aliases representing two logical instances of the same table.
• E1.department_id = E2.department_id selects employees in the same department.
• E1.employee_id < E2.employee_id serves two purposes:
  • It prevents an employee from being paired with the same employee.
  • It prevents duplicate reversed pairs, such as both (A, B) and (B, A).
• An inner join is appropriate because both employees in a pair must satisfy the department match.

This technique is useful whenever records in one table must be compared with other records in that same table.

A multi-table join connects tables successively through their related keys. Assume these tables:

• Customers(customer_id, customer_name)
• Orders(order_id, customer_id)
• OrderItems(order_id, product_id, quantity)
• Products(product_id, product_name)

The query is:

SELECT C.customer_name, O.order_id, P.product_name, OI.quantity FROM Customers C INNER JOIN Orders O ON C.customer_id = O.customer_id INNER JOIN OrderItems OI ON O.order_id = OI.order_id INNER JOIN Products P ON OI.product_id = P.product_id;

Explanation:

1. Customers is joined with Orders through customer_id.
2. Orders is joined with OrderItems through order_id.
3. OrderItems is joined with Products through product_id.
4. Each result row identifies a customer, an order, a product, and the ordered quantity.

Important considerations:

• Use aliases to improve readability.
• Specify every join condition explicitly.
• Select only required columns.
• Ensure primary-key and foreign-key columns are indexed where appropriate.

This query demonstrates how normalized data can be reconstructed into a detailed business report.

In an outer join, the placement of a condition can change the result.

Consider:

SELECT C.customer_name, O.order_id FROM Customers C LEFT JOIN Orders O ON C.customer_id = O.customer_id AND O.status = 'Pending';

Here, every customer is preserved. Only pending orders are matched; customers without pending orders receive NULL in order columns.

Now consider:

SELECT C.customer_name, O.order_id FROM Customers C LEFT JOIN Orders O ON C.customer_id = O.customer_id WHERE O.status = 'Pending';

The left join first preserves all customers, but the WHERE clause then removes rows where O.status is NULL. Consequently, customers without pending orders disappear, and the result behaves similarly to an inner join for that condition.

General rule:

• A condition in ON controls which rows match while preserving required outer rows.
• A condition in WHERE filters the result after the join.
• Conditions involving the non-preserved table must be placed carefully to avoid unintentionally converting an outer join into an inner join.

A view is a named database object defined by a query. It presents data from one or more tables as a virtual table. In an ordinary view, the database generally stores the query definition rather than a separate copy of all result rows.

Example:

CREATE VIEW ActiveCustomers AS SELECT customer_id, customer_name FROM Customers WHERE status = 'Active';

Major characteristics:

• It can be queried with SELECT like a table.
• It may combine columns from multiple tables using joins.
• It can hide unnecessary rows or columns.
• It provides a stable interface even when underlying queries are complex.
• Its results normally reflect current data in the base tables.

Uses:

• Simplifying frequently used complex queries.
• Restricting access to sensitive data.
• Presenting customized data to different users.
• Supporting logical data independence.
• Standardizing calculations and reports.

A view improves abstraction and security, but complex nested views may make performance and maintenance more difficult.

A view is managed through data definition and query commands.

1. Create a view:

CREATE VIEW EmployeeDetails AS SELECT E.employee_id, E.employee_name, D.department_name FROM Employees E INNER JOIN Departments D ON E.department_id = D.department_id;

This stores the query definition under the name EmployeeDetails.

2. Query the view:

SELECT * FROM EmployeeDetails;

The database executes the underlying view query and returns its result.

3. Replace or alter the view:

In systems that support it:

CREATE OR REPLACE VIEW EmployeeDetails AS SELECT E.employee_id, E.employee_name, D.department_name FROM Employees E LEFT JOIN Departments D ON E.department_id = D.department_id;

Some DBMSs use ALTER VIEW or require the view to be dropped and recreated.

4. Delete the view:

DROP VIEW EmployeeDetails;

Dropping a view removes its definition but does not delete rows from the underlying tables. Dependent database objects may need to be checked before a view is altered or dropped.

A base table physically represents stored database data, whereas a view is usually a stored query that presents data virtually.

A view does not generally own the displayed data. Changes in the underlying tables are reflected when the view is queried. However, DBMS-specific materialized views may store query results physically and refresh them periodically.

An updatable view is a view through which INSERT, UPDATE, or DELETE operations can modify rows in the underlying base table.

A simple view is commonly updatable when:

• It is based on a single base table.
• Each view row corresponds unambiguously to one base-table row.
• It does not contain aggregate functions such as SUM, AVG, or COUNT.
• It does not use GROUP BY, HAVING, DISTINCT, or set operations such as UNION.
• It does not contain calculated columns that cannot be mapped directly to stored columns.
• Required base-table columns omitted from the view have defaults or permit NULL for insertion.

Views based on joins are often non-updatable or only partially updatable because the DBMS may not be able to determine which base table should be modified.

A WITH CHECK OPTION clause can require inserted or updated rows to remain visible through the view's condition. Exact updatability rules vary between database management systems.

A transaction is a logical unit of database work consisting of one or more operations that must be treated as a single consistent action. An example is transferring money between two bank accounts.

Transactions follow the ACID properties:

• Atomicity: Either all operations in the transaction complete successfully or none of them take effect.
• Consistency: The transaction moves the database from one valid state to another while preserving constraints and rules.
• Isolation: Concurrent transactions should not interfere in a way that produces incorrect results. Intermediate changes are controlled according to the isolation level.
• Durability: Once a transaction is committed, its changes remain permanent even after a system failure.

For a transfer, debiting one account and crediting the other must occur together. If the credit fails, atomicity requires the debit to be undone. ACID properties therefore protect the correctness and reliability of transactional data.

Transactional control commands determine whether database changes are made permanent or undone.

• COMMIT: Permanently saves all changes made by the current transaction. After a commit, those changes cannot normally be reversed using ROLLBACK.
• ROLLBACK: Cancels uncommitted changes and restores the database to its state at the start of the transaction or to a specified savepoint.
• SAVEPOINT: Creates a named intermediate point within a transaction. It enables partial rollback without cancelling the entire transaction.

Example:

START TRANSACTION;

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

SAVEPOINT after_debit;

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

COMMIT;

If a later operation fails, ROLLBACK TO after_debit; can undo changes made after that savepoint. However, a complete transfer would normally be fully rolled back if either account update failed, so that business consistency is preserved.

Assume Accounts(account_id, balance) and a transfer amount of 1,000 units from account 101 to account 202.

Transaction outline:

START TRANSACTION;

UPDATE Accounts SET balance = balance - 1000 WHERE account_id = 101 AND balance >= 1000;

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

COMMIT;

A robust application must also verify that:

• The source account exists.
• The destination account exists.
• The source account has sufficient balance.
• Each update affects exactly one expected row.
• No database constraint or system error occurs.

If any check fails, the application should issue:

ROLLBACK;

Why transactional control is necessary:

• Without a transaction, the debit might succeed while the credit fails.
• Atomicity ensures that both operations occur or neither occurs.
• Appropriate row locking or isolation prevents concurrent transactions from spending the same balance incorrectly.
• COMMIT should execute only after every validation and update succeeds.

In production systems, exception-handling logic and parameterized SQL should be used to perform the rollback automatically when an error occurs.

A transaction may pass through several conceptual states:

1. Active: The transaction is executing read or write operations.
2. Partially committed: Its final statement has executed, but the changes may not yet be guaranteed as durable.
3. Committed: All operations have succeeded, and the changes are permanently recorded.
4. Failed: The transaction cannot continue because of an error, deadlock, constraint violation, or system failure.
5. Aborted: Its changes have been undone through rollback, restoring the database to an earlier consistent state.
6. Terminated: The transaction has finished after either commit or abort.

A successful path is generally:

Active → Partially committed → Committed → Terminated

A failure path is generally:

Active → Failed → Aborted → Terminated

Depending on the application, an aborted transaction may be restarted. These states help explain how a DBMS maintains atomicity and durability even when errors occur.

When transactions execute concurrently, insufficient isolation may produce anomalies.

Common problems:

• Dirty read: A transaction reads data written by another transaction that has not yet committed. If the writer rolls back, the reader used invalid data.
• Non-repeatable read: A transaction reads the same row twice and obtains different values because another transaction updated and committed it.
• Phantom read: Repeating a range query returns a different set of rows because another transaction inserted or deleted matching rows.
• Lost update: Two transactions update the same item, and one update overwrites the other.

Isolation levels:

• Read Uncommitted: Provides minimal protection and may allow dirty reads.
• Read Committed: Prevents dirty reads but may allow non-repeatable and phantom reads.
• Repeatable Read: Protects rows already read, though phantom behavior depends on the DBMS.
• Serializable: Provides the strongest standard isolation by making concurrent execution behave like a serial order.

Stronger isolation improves consistency but can reduce concurrency through greater locking, waiting, or transaction retries. The appropriate level depends on application correctness and performance requirements.

Joins, views, and transactions serve complementary purposes in a database application.

Scenario: order processing

A database contains Customers, Orders, OrderItems, Products, and Inventory.

Use of joins:

• Joins combine customer, order, product, and inventory data.
• For example, an inner join can display product details for each order item.
• A left join can identify products that have never been ordered.

Use of views:

A view can simplify an order summary:

CREATE VIEW OrderSummary AS SELECT O.order_id, C.customer_name, P.product_name, OI.quantity FROM Orders O INNER JOIN Customers C ON O.customer_id = C.customer_id INNER JOIN OrderItems OI ON O.order_id = OI.order_id INNER JOIN Products P ON OI.product_id = P.product_id;

Users can query OrderSummary without repeatedly writing the joins. Permissions can also be granted on the view while sensitive customer columns remain hidden.

Use of transactions:

When an order is placed, one transaction may:

1. Insert the order.
2. Insert its order items.
3. Reduce inventory quantities.
4. Record payment information.
5. Commit only if all operations succeed.

If any step fails, ROLLBACK prevents a partial order. Thus, joins retrieve related data, views provide abstraction and security, and transactions preserve consistency during modifications.