SQL for investigation and data analysis

SELECT, JOIN, WHERE, GROUP BY, HAVING and window basics, used safely and read-only to investigate Odoo data.

In this lesson

SELECT, JOIN and aggregates
Investigation patterns
Safety rules

Key points

SELECT … FROM … JOIN … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT
LEFT JOIN finds orphans; GROUP BY + HAVING finds duplicates
EXPLAIN ANALYZE for slow queries
Read-only by default; fix data through the ORM on a tested copy

Real-world example

The CFO asked why receivables in the balance sheet didn't match the aged receivable report. A SQL sum of amount_residual on receivable account lines grouped by partner revealed one 'unknown' partner with unposted opening entries. Posting them correctly through the UI reconciled both reports.

Questions and answers

What does LEFT JOIN … WHERE right.id IS NULL find?

Rows with no matching row on the right (orphans).

Which command shows a query plan with timings?

EXPLAIN ANALYZE.

Read the full lesson, worked example and quiz