How to Create Views in LibreOffice Base

Relational database views act as saved, virtual tables that streamline complex data retrieval by packaging intricate multi-table joins, calculations, and filters into a single reusable object. This guide explains how to create and manage views in LibreOffice Base using both the graphical interface and standard SQL commands, allowing you to simplify your query workflows, enhance performance, and maintain cleaner database structures.

What Is a Database View?

A view is a virtual table defined by an underlying SQL SELECT statement. Unlike standard tables, a view does not store data itself; instead, it retrieves data dynamically from the underlying base tables every time it is accessed. In LibreOffice Base, views appear in the Tables pane rather than the Queries pane, allowing you to query them just as you would standard physical tables.

Creating a View Using the Graphical Interface

LibreOffice Base provides a visual Query/View Designer to create views without writing raw SQL.

  1. Open your database file (.odb) in LibreOffice Base.
  2. In the left-hand navigation pane, click on the Tables icon.
  3. Under the Tasks section at the top, click Create View….
  4. In the Add Table or Query dialog, select the tables that contain the data you want to join and click Add, then click Close.
  5. Define the relationships by dragging matching key fields between the tables (e.g., CustomerID from the Customers table to CustomerID in the Orders table).
  6. Select the fields you want to include in your view by double-clicking them in the table boxes or selecting them from the drop-down menus in the table grid below.
  7. Add any necessary sorting, aliases, or filtering criteria in the lower grid.
  8. Click the Save icon (or press Ctrl + S), enter a descriptive name for your view (e.g., vw_CustomerOrderSummary), and click OK.
  9. Close the View Designer. The new view will now be listed under the Tables section.

Creating a View Using SQL

For advanced queries involving complex joins, subqueries, or aggregate functions, creating a view via SQL is often faster and more flexible.

  1. From the top menu, go to Tools > SQL….
  2. In the Command to execute box, enter your CREATE VIEW statement:
CREATE VIEW "vw_ActiveClientOrders" AS
SELECT 
    "Customers"."CustomerID",
    "Customers"."CustomerName",
    "Orders"."OrderID",
    "Orders"."OrderDate",
    "Orders"."TotalAmount"
FROM 
    "Customers"
INNER JOIN 
    "Orders" ON "Customers"."CustomerID" = "Orders"."CustomerID"
WHERE 
    "Orders"."Status" = 'Active';
  1. Click the Execute button. You should see a message confirming Command successfully executed.
  2. Close the SQL dialog.
  3. In the main Base window, select View > Refresh Tables to make the new view visible in your Tables list.

Using Views to Simplify Relational Queries

Once a view is created, you can query it like any standard table, eliminating the need to recreate complex joins across multiple tables repeatedly.

Example: Querying a View

Instead of writing a four-table join to generate an invoice report, you can query your predefined view directly:

SELECT 
    "CustomerName", 
    SUM("TotalAmount") AS "AnnualSpend"
FROM 
    "vw_ActiveClientOrders"
GROUP BY 
    "CustomerName";

You can also use the graphical Query Design View to combine a view with additional tables, further modularizing your relational database design.

Modifying and Deleting Views