An inline view in MariaDB is implemented as a subquery in the FROM clause that acts as a virtual table. The key requirement is that every inline view must have an alias.
Basic Syntax
|
1 2 3 |
SELECT column_list FROM (SELECT ... FROM table) AS alias WHERE conditions; |
Key Features
Mandatory Alias
Unlike Oracle, MariaDB requires explicit aliases for inline views. For example:
|
1 2 3 4 5 6 |
SELECT A.LAST_NAME, A.SALARY, A.DEPARTMENT_ID, B.SAL_AVG FROM EMPLOYEES A, (SELECT DEPARTMENT_ID, ROUND(AVG(SALARY)) AS SAL_AVG FROM EMPLOYEES GROUP BY DEPARTMENT_ID) B WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID; |
Algorithm Options
MariaDB supports three view algorithms:
- MERGE: Combines view definition with the main query
- TEMPTABLE: Stores results in a temporary table
- UNDEFINED: Lets MariaDB choose the optimal algorithm
Limitations
MERGE Algorithm Restrictions
Inline views cannot use MERGE if they contain:
- HAVING clauses
- GROUP BY statements
- DISTINCT operations
- Aggregate functions (MAX, MIN, SUM, COUNT)
- Subqueries in the SELECT list
- UNION operations
View Updates
For an inline view to be updatable, it must:
- Contain all base table columns without default values
- Have simple columns without derived calculations
- Not contain multiple references to base table columns
