SELECT

The SELECT statement reference.

Syntax

[WITH expr_list|(subquery)]
SELECT [DISTINCT [ON (column1, column2, ...)]] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]
[HAVING expr]
[WINDOW window_expr_list]
[QUALIFY expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr] [INTERPOLATE [(expr_list)]]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[SETTINGS ...]
[UNION  ...]
[INTO OUTFILE filename [COMPRESSION type [LEVEL level]] ]
[FORMAT format]

Clauses

SELECT

The SELECT clause specifies the columns, expressions, or aggregations you want to retrieve.

-- Select specific columns
SELECT column1, column2
FROM table_name;

-- Select all columns
SELECT *
FROM table_name;

-- Select with expressions
SELECT
    first_name,
    last_name,
    price * quantity as total_value
FROM table_name;

FROM

The FROM clause specifies the source table(s), subquery, or table function to query from.

-- Query from a table
SELECT * FROM table_name;

-- Query from a specific database
SELECT * FROM database.table_name;

-- Query from a subquery
SELECT *
FROM (
    SELECT column1, column2
    FROM table_name
    WHERE condition
) AS subquery;

-- Query using a table function
SELECT *
FROM table_function(arg1, arg2);

WHERE

The WHERE clause filters rows based on specified conditions. It supports various comparison and logical operators.

SELECT column1, column2
FROM table_name
WHERE
    (price > 100 AND category = 'electronics')
    OR (price > 200 AND category = 'furniture');

GROUP BY

The GROUP BY clause groups rows by specified columns and is typically used with aggregate functions. Supports additional modifiers:

• WITH ROLLUP: Adds extra rows with subtotals
• WITH CUBE: Adds subtotals for all possible combinations
• WITH TOTALS: Adds a final row with totals

-- Basic grouping
SELECT
    category,
    COUNT(*) as count,
    AVG(price) as avg_price
FROM products
GROUP BY category;

-- Grouping with ROLLUP
SELECT
    category,
    subcategory,
    SUM(sales)
FROM sales
GROUP BY
    category,
    subcategory
WITH ROLLUP;

HAVING

The HAVING clause filters grouped results based on aggregate conditions.

SELECT
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price
FROM products
GROUP BY category
HAVING
    COUNT(*) > 10
    AND AVG(price) > 100;

ORDER BY

The ORDER BY clause sorts results by specified columns. Supports additional features:

• WITH FILL: Fills missing values in sorted results
• INTERPOLATE: Specifies how to interpolate missing values

-- Basic sorting
SELECT *
FROM table_name
ORDER BY
    column1 DESC,
    column2 ASC;

-- Sorting with fill
SELECT
    time,
    value
FROM metrics
ORDER BY time WITH FILL
    FROM '2024-01-01'
    TO '2024-01-02'
    STEP INTERVAL 1 HOUR;

JOIN

Propel supports various types of joins to combine rows from two or more tables based on a related column between them.

• LEFT JOIN

The LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

• RIGHT JOIN

The RIGHT JOIN returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side if there is no match.

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

• FULL JOIN

The FULL JOIN returns all records when there is a match in either left (table1) or right (table2) table records. If there is no match, the result contains NULL values for columns from the table without a match.

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

• INNER JOIN

The INNER JOIN returns all records when there is a match in both left (table1) and right (table2) table records.

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

DISTINCT, UNION, and ALL

• DISTINCT

The DISTINCT clause is used to return only unique values in the result set.

SELECT DISTINCT column1, column2, ...
FROM table_name;

• UNION

The UNION operator is used to combine the result sets of two or more SELECT statements.

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

• UNION ALL

The UNION ALL operator is used to combine the result sets of two or more SELECT statements, including duplicates.

SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;

WITH

The WITH clause is used to define a common table expression (CTE).

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name;

LIMIT

The LIMIT clause controls the number of rows returned. It supports several formats:

• Basic LIMIT with optional OFFSET
• LIMIT BY to limit rows per group
• WITH TIES to include rows that match the last row

-- Basic limit
SELECT *
FROM table_name
LIMIT 10;

-- Limit with offset
SELECT *
FROM table_name
LIMIT 10 OFFSET 20;

-- Limit BY
SELECT
    category,
    product_name,
    price
FROM products
LIMIT 3 BY category;  -- Return top 3 products per category

-- Limit with ties
SELECT *
FROM table_name
ORDER BY score
LIMIT 10 WITH TIES;  -- Include tied scores

WINDOW

The WINDOW clause defines named window specifications for window functions.

SELECT
    date,
    value,
    AVG(value) OVER w as moving_avg,
    RANK() OVER w as rank
FROM metrics
WINDOW w AS (
    PARTITION BY category
    ORDER BY date
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
);

QUALIFY

The QUALIFY clause filters results based on window functions.

SELECT
    date,
    category,
    value,
    RANK() OVER (PARTITION BY category ORDER BY value DESC) as rank
FROM metrics
QUALIFY rank <= 3;  -- Keep only top 3 values per category

SAMPLE

The SAMPLE clause allows you to query a random sample of rows.

-- Sample 10% of rows
SELECT *
FROM large_table
SAMPLE 0.1;

-- Sample with offset
SELECT *
FROM large_table
SAMPLE 0.1 OFFSET 0.5;

ARRAY JOIN

The ARRAY JOIN clause allows you to join with array or nested data structures.

SELECT
    user_id,
    tags.name as tag_name,
    tags.value as tag_value
FROM users
ARRAY JOIN tags;

FORMAT

The FORMAT clause specifies the output format of the query results.

-- Output as JSON
SELECT *
FROM table_name
FORMAT JSON;

-- Output as CSV
SELECT *
FROM table_name
FORMAT CSV;

INTO OUTFILE

The INTO OUTFILE clause writes query results to a file.

SELECT *
FROM table_name
INTO OUTFILE 'path/to/file.csv'
FORMAT CSV;

-- With compression
SELECT *
FROM table_name
INTO OUTFILE 'path/to/file.gz'
COMPRESSION 'gzip'
LEVEL 1
FORMAT CSV;

SETTINGS

The SETTINGS clause allows you to specify query-specific settings.

SELECT *
FROM table_name
SETTINGS
    max_execution_time=10,