View with Join Condition in SQL

You can create a view based on a JOIN condition involving two or more tables. This is often used to combine data from multiple related tables into a single, queryable view.

Here’s how you can create a view using a JOIN between two tables.

Example

Suppose you have two tables: employees and departments.

CREATE TABLE employees (
emp_id NUMBER,
emp_name VARCHAR2(50),
department_id NUMBER,
salary NUMBER
);

CREATE TABLE departments (
department_id NUMBER,
department_name VARCHAR2(50)
);

-- Insert sample data
INSERT INTO employees VALUES (1, 'John Doe', 101, 50000);
INSERT INTO employees VALUES (2, 'Jane Smith', 102, 60000);
INSERT INTO employees VALUES (3, 'James Brown', 101, 70000);

INSERT INTO departments VALUES (101, 'IT');
INSERT INTO departments VALUES (102, 'HR');

Create a View with a JOIN

Now, we will create a view that combines the employees and departments tables using a JOIN to show the employee name, department name, and salary.

CREATE VIEW employee_department_view AS
SELECT e.emp_name, d.department_name, e.salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

Query the View

You can query the view just like a table:

SELECT * FROM employee_department_view;

Output:

Explanation

JOIN Condition: The view is created based on the JOIN between the employees and departments tables, where the department_id in the employees table matches the department_id in the departments table.

View Columns: The view contains the emp_name (from the employees table), department_name (from the departments table), and salary (from the employees table).

Updating Data Through the View

In most cases, you can still update, insert, or delete data through a view that joins multiple tables, but only under certain conditions, and it may require special handling. If the view involves complex joins, aggregations, or subqueries, updates may not be allowed.

For example, trying to update the employee_department_view to change an employee's salary is allowed:

UPDATE employee_department_view
SET salary = 55000
WHERE emp_name = 'John Doe';

This updates the salary in the employees table directly.

However, you can't update the department_name through this view because it comes from the departments table and would require a complex join update, which is generally not supported through views.

Conclusion

Yes, you can create views on join conditions involving two or more tables. This is a common and powerful technique to combine related data into a single view, simplifying query complexity and improving data organization. However, updating the underlying data through such views may be restricted depending on the complexity of the joins and the structure of the view.