本页尚未翻译

本页内容仍为英文原文，源自官方文档 sql/dql/select。中文翻译正在分批进行中。

SELECT

Projection, filtering, ordering, and composing queries out of other queries.

Applies to: Embedded ❌, Enterprise ✅

[WITH cte AS (...)]
SELECT [ALL | DISTINCT] select_expr [, ...]
FROM from_item [[JOIN ...] ON ...]
[WHERE predicate]
[GROUP BY expr [, ...]] [HAVING predicate]
[WINDOW name AS (...)]
[ORDER BY expr [ASC | DESC] [NULLS FIRST | LAST] [, ...]]
[LIMIT n] [OFFSET n]

Projection

SELECT id, title, identifier FROM documents

tbl.search().select(["id", "title", "identifier"]).to_arrow()

await tbl.query().select(["id", "title", "identifier"]).toArrow();

Name the columns you need instead of using SELECT *, particularly for tables containing images, video or large vectors. Projection lets the engine avoid loading unnecessary columns, although columns used by filters, joins or other expressions may still need to be read.

Row metadata is available as pseudo-columns (_rowid, _rowaddr, _rowoffset) but only when named, so they stay out of SELECT *.

DISTINCT deduplicates the projected rows.

Filtering

SELECT * FROM documents
WHERE identifier > 0 AND identifier < 1000000

tbl.search().where("identifier > 0 AND identifier < 1000000").to_arrow()

await tbl.query().where("identifier > 0 AND identifier < 1000000").toArrow();

The SDK where takes the same expression language, so a predicate moves between them unchanged. A supported predicate can use a scalar index; confirm the chosen plan with explain_plan. See Filtering for the operators and their pushdown behaviour.

Ordering and limiting

SELECT id, title FROM documents
ORDER BY created_at DESC NULLS LAST
LIMIT 100 OFFSET 200

ORDER BY on a large table is a sort over the whole result, so pair it with a LIMIT unless you genuinely need every row ordered.

Set operations

UNION, UNION ALL, INTERSECT and EXCEPT combine whole queries:

SELECT id FROM documents WHERE category = 'film'
UNION ALL
SELECT id FROM archived_documents WHERE category = 'film'

UNION deduplicates and UNION ALL does not, which makes UNION ALL the cheaper one when you already know the inputs are disjoint.

Common table expressions

WITH recent AS (
    SELECT * FROM documents WHERE created_at > now() - INTERVAL '7 days'
)
SELECT category, count(*) FROM recent GROUP BY category

A CTE is scoped to the statement. One you want to keep is a view; one whose rows you want stored is a materialized view.

Subqueries

Subqueries are allowed anywhere an expression or a relation is (in FROM, in WHERE with IN or EXISTS, and as a scalar in the select list):

SELECT title
FROM documents
WHERE category_id IN (SELECT id FROM categories WHERE active)

Namespaced tables

A table inside a namespace is written with its schema -- the $-joined namespace path, not a dotted one -- before the table name; see Schema for why:

SELECT * FROM `prod$events`.clicks

Reading the plan

EXPLAIN and EXPLAIN ANALYZE work as DataFusion defines them, and produce the same operators the SDK methods do:

EXPLAIN ANALYZE SELECT * FROM documents WHERE identifier < 1000

See Explain and Explain Analyze.