SQL Subqueries in LibreOffice Base Query Designer

LibreOffice Base allows you to perform advanced data analysis by nesting SQL SELECT statements inside a main query, commonly known as subqueries. This guide covers how to write, structure, and execute custom SQL subqueries within the LibreOffice Base Query Designer, including the use of scalar, list-based, and correlated subqueries, along with essential tips for managing parser limitations.

Step 1: Open the Query in SQL Mode

While you can create simple queries using the graphical interface, custom subqueries are most reliably written in SQL View:

  1. Open your LibreOffice Base database (.odb).
  2. In the left navigation pane, click Queries.
  3. Under Tasks, select Create Query in SQL View… (or open an existing query in Edit mode and click the Switch Design View On/Off icon in the toolbar).

Step 2: Structure the Subquery

A subquery must always be enclosed in parentheses (...). In LibreOffice Base, subqueries can be placed in different parts of your main SQL statement depending on your goal.

1. Scalar Subquery in the SELECT Clause

Use this to return a single calculated value for each row:

SELECT 
    "ProductID", 
    "Price", 
    (SELECT AVG("Price") FROM "Products") AS "AveragePrice"
FROM "Products"

2. Subquery in the WHERE Clause (IN Operator)

Use this to filter records based on a list generated by another table or query:

SELECT "CustomerName", "City"
FROM "Customers"
WHERE "CustomerID" IN (
    SELECT "CustomerID" 
    FROM "Orders" 
    WHERE "TotalAmount" > 500
)

3. Subquery with Comparison Operators

Use this when comparing a column to a single aggregated value:

SELECT "EmployeeName", "Salary"
FROM "Employees"
WHERE "Salary" > (
    SELECT AVG("Salary") 
    FROM "Employees"
)

4. Correlated Subquery

A correlated subquery references columns from the outer query, executing once for every row evaluated:

SELECT "O"."OrderID", "O"."OrderDate", "O"."TotalAmount"
FROM "Orders" AS "O"
WHERE "O"."TotalAmount" > (
    SELECT AVG("Sub"."TotalAmount")
    FROM "Orders" AS "Sub"
    WHERE "Sub"."CustomerID" = "O"."CustomerID"
)

Step 3: Handle LibreOffice Base SQL Parser Limitations

LibreOffice Base uses an internal SQL parser to interpret queries before sending them to the underlying database engine (HSQLDB, Firebird, MySQL, PostgreSQL, etc.). Complex subqueries can sometimes trigger parser syntax errors.

To bypass the Base parser and execute raw SQL directly on the database engine:

  1. In the Query SQL Editor, look at the toolbar.
  2. Click the Run SQL command directly button (represented by an icon with the letters SQL).
  3. Alternatively, navigate to Edit > Run SQL command directly.
  4. Run the query by pressing F5 or clicking the Run Query button.

Note: When “Run SQL command directly” is enabled, graphical Design View will be disabled for that query, and parameters (e.g., :parameter_name) cannot be used.

Syntax and Formatting Rules