Skip to main content

SQL SELECT Statement reference

You can query Propel's Data Pools using the SELECT SQL statement. The SELECT statement has the following clauses:

Syntax​

SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...]
[LIMIT number] [OFFSET number]
[JOIN clause]
[WITH common_table_expression]

Clauses​

Filter rows using the WHERE clause​

The WHERE clause is used to filter records that meet certain conditions.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Sort rows using the ORDER BY clause​

The ORDER BY clause is used to sort the result set in ascending or descending order.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Select a subset of rows from a table using the LIMIT and OFFSET clauses​

The LIMIT clause is used to specify the number of records to return. The OFFSET clause is used to specify the number of records to skip before starting to return the records.

SELECT column1, column2, ...
FROM table_name
LIMIT number [OFFSET number];

Group rows into groups using the GROUP BY clause​

The GROUP BY clause is used to group rows that have the same values into summary rows.

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Join with other tables using joins​

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;

Perform set operations using 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;

Supports recursive selects with common table expressions (CTE) using WITH​

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

This documentation provides an overview of the SELECT statement and its clauses to help you effectively query Propel’s Data Pools.