MySQL - Views Part 3: Advanced Queries with Views

Views can include complex SQL queries like joins, aggregations, and subqueries.

Example 1: View with Joins

CREATE VIEW DepartmentInfo AS

SELECT e.name, d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.id;

The DepartmentInfo view combines data from employees and department tables.

Example 2: View with Aggregation

CREATE VIEW AverageSalary AS

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department;

Example 3: View with Subquery

CREATE VIEW TopEarners AS

SELECT name, salary

FROM employees

WHERE salary = (SELECT MAX(salary) FROM employees);

Explanation:

Advanced queries allow you to extract meaningful insights from multiple tables and complex data sets.