From Table‑Centric to Object‑Centric: How DQL Redefines Join Queries

Many developers dread writing multiple LEFT JOINs in SQL, worrying about remembering table structures just to analyze sales data. The article introduces Dimensional Query Language (DQL) as a way to shift focus from "how tables connect" to "what data is needed".

Core Principle: Foreign‑Key Attributeization

DQL abstracts foreign‑key relationships using a dot operator. Instead of remembering that the employee table joins department and then manager, a DQL query accesses department.manager.nationality directly. This requires that foreign‑key columns reference primary keys, ensuring a one‑to‑many relationship is uniquely defined.

-- Traditional SQL
SELECT A.*
FROM employee A
JOIN department B ON A.department = B.id
JOIN employee C ON B.manager = C.id
WHERE A.nationality = 'USA' AND C.nationality = 'CHN';

-- DQL equivalent
SELECT * FROM employee
WHERE nationality = 'USA' AND department.manager.nationality = 'CHN';

Dimension Alignment: The "United Nations" Model

DQL allows independent tables to be aggregated on a common dimension without explicit joins. For daily contract, payment, and invoice totals, traditional SQL needs nested subqueries and multiple FULL JOINs, while DQL expresses the same intent with a concise alignment syntax.

-- Traditional SQL (simplified)
SELECT COALESCE(A.date, B.date, C.date) AS stat_date,
       A.contract_amount, B.payment_amount, C.invoice_amount
FROM (SELECT date, SUM(price) AS contract_amount FROM Contract GROUP BY date) A
FULL JOIN (SELECT date, SUM(amount) AS payment_amount FROM Payment GROUP BY date) B ON A.date = B.date
FULL JOIN (SELECT date, SUM(amount) AS invoice_amount FROM Invoice GROUP BY date) C ON COALESCE(A.date, B.date) = C.date;

-- DQL version
SELECT Contract.SUM(price), Payment.SUM(amount), Invoice.SUM(amount) ON date
FROM Contract BY date UNION Payment BY date UNION Invoice BY date;

Each table is treated as an independent "object" that performs its own aggregation.

All objects align on a shared dimension such as date.

No need to specify explicit join conditions; only the alignment dimension is required.

A more complex example counts salespeople per region and sums contract amounts, mixing foreign‑key attributeization with dimension alignment:

SELECT Sales.COUNT(1), Contract.SUM(price) ON area
FROM Sales BY area UNION Contract BY customer.area;

Mindset Shift

Traditional SQL forces the query writer to know which tables to join, which columns to use, the join type, and how to avoid Cartesian products. DQL moves these concerns to metadata design, letting users specify only the desired data and filter conditions.

What data is needed?

What conditions must it satisfy?

This shift simplifies analysis of star schemas, snowflake schemas, and multi‑fact‑table scenarios.

BI Application

DQL’s object‑centric approach makes real‑time BI queries straightforward. For example, to retrieve call records from Beijing to Shanghai, a user selects the "call record" entity, checks fields such as caller.city.name, callee.city.name, duration, and tariff, and sets the filter

caller.city.name = 'Beijing' AND callee.city.name = 'Shanghai'

. No JOIN logic is required; DQL resolves the relationships automatically.

Real‑World Implementation

The Runqian Report engine has engineered DQL, translating DQL statements into native SQL for databases like Oracle, MySQL, and SQL Server. This allows existing database environments to adopt DQL without changing infrastructure, and the engine is bundled free in Runqian’s open‑source BI platform.

DQL is not intended to replace SQL entirely but to provide a more human‑centric way of expressing relational logic, based on the insight that business relationships are essentially foreign‑key and dimension‑alignment patterns.

Provides a friendlier query interface for business users.

Reduces development and maintenance effort for complex queries.

Improves efficiency and accuracy of data analysis.