Outer Joins and Views

While the INNER JOIN is essential for combining matching data, Outer Joins are crucial when you need to include all records from one table, even if they have no matching data in the second table. Views abstract complex query logic for organizational purposes.

1. The Need for Outer Joins

Recall that an INNER JOIN only returns rows where the join condition is met in both tables.

• Scenario: You want a list of all employees and their assigned department. If a few new employees haven’t been assigned a department yet (meaning their department_id is NULL), an INNER JOIN would completely exclude them from the result set.
• Solution: An Outer Join allows you to preserve the records from one side of the join, regardless of a match on the other side.

2. Types of Outer Joins

Execution Example: Left Join

To list all employees, including those without an assigned department, and their department name if one exists:

SELECT
    E.first_name,
    D.department_name 
FROM Employees AS E            -- This is the LEFT table, all rows will be kept
LEFT JOIN Departments AS D     -- This is the RIGHT table
  ON E.department_id = D.department_id;

Result: Any employee without a matching department_id will still appear in the result, but their department_name will show NULL.

3. Database Views

A View is a virtual table based on the result-set of an SQL query. A View contains rows and columns just like a real table, but its data is not physically stored. Instead, the data is dynamically retrieved from the underlying base tables every time the View is queried.

Purpose of Views:

• Simplification: Hiding complex join logic or calculations behind a simple, single table name.
• Security: Restricting user access to only the specific rows or columns exposed by the View, without granting access to the underlying tables.

Creating a View (CREATE VIEW)

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Execution Example: Creating a High-Salary View

Let’s create a view that only exposes the names and salaries of employees making over $100,000.

-- Create the virtual table
CREATE VIEW High_Earners AS 
SELECT
    first_name,
    last_name,
    salary
FROM Employees
WHERE salary > 100000;

Querying the View

Once created, you query the view just like a regular table.

-- This executes the underlying SELECT statement
SELECT * FROM High_Earners; 

Removing a View

DROP VIEW High_Earners;