SELECT

The SELECT statement retrieves data from one or more tables. ScramDB supports standard SQL SELECT syntax for querying both internal and external tables.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, ...]
[HAVING condition]
[ORDER BY column1 [ASC|DESC], ...]
[LIMIT count];

SELECT column1, ...: Specifies the columns to retrieve. Use * to select all columns.
FROM table_name: Specifies the table to query.
WHERE condition: (Optional) Filters rows based on a specified condition (e.g., price > 100, username = 'alice').
GROUP BY column1, ...: (Optional) Groups rows that have the same values in specified columns into a summary row. Often used with aggregate functions (COUNT, SUM, AVG, MIN, MAX).
HAVING condition: (Optional) Filters groups based on a specified condition after GROUP BY has been applied.
ORDER BY column1 [ASC|DESC], ...: (Optional) Sorts the result set based on specified columns. ASC for ascending (default), DESC for descending.
LIMIT count: (Optional) Restricts the number of rows returned.

Examples

-- Select all columns from the 'users' table
SELECT * FROM users;

-- Select specific columns from 'products' where stock is low
SELECT name, price, stock_count
FROM products
WHERE stock_count < 10;

-- Count the number of orders per customer
SELECT customer_id, COUNT(*) AS order_count
FROM orders_parquet -- Querying an external table
GROUP BY customer_id;

-- Find the average order total for orders placed today
SELECT AVG(total_amount)
FROM orders_parquet
WHERE order_date >= CURRENT_DATE;

-- Select the top 5 most expensive products
SELECT product_id, name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- Select departments with more than 10 employees
SELECT department, COUNT(employee_id) as num_employees
FROM employees
GROUP BY department
HAVING COUNT(employee_id) > 10;

Joins

ScramDB supports standard SQL joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN) to combine rows from two or more tables based on a related column.

SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date > '2024-01-01';

Note: Join performance and functionality are under active development. While standard syntax is supported, complex joins might encounter performance issues or limitations in the current version. Refer to the latest release notes.