SQL REFERENCE

SELECT Queries

The SELECT statement retrieves data from one or more tables. This page covers the fundamental query patterns.

Basic SELECT

Retrieve all columns or specific columns from a table.

-- All columns
SELECT * FROM employees;

-- Specific columns
SELECT name, department, salary FROM employees;

-- Single column
SELECT name FROM employees;

Column Aliases

Use AS to rename columns in the result set. Aliases can be used in ORDER BY but not in WHERE.

SELECT
  name AS employee_name,
  salary AS annual_salary,
  salary / 12 AS monthly_salary
FROM employees;

-- AS keyword is optional
SELECT name employee_name, salary annual_salary
FROM employees;

DISTINCT

Removes duplicate rows from the result set.

-- Unique departments
SELECT DISTINCT department FROM employees;

-- Unique combinations
SELECT DISTINCT department, active FROM employees;

SELECT Without FROM

Evaluate constant expressions without referencing a table.

-- Arithmetic
SELECT 2 + 3;
-- Result: 5

-- String
SELECT 'Hello, ' || 'world!';
-- Result: Hello, world!

-- Multiple expressions
SELECT 42 AS answer, CAST('2024-06-15' AS DATE) AS today;

-- Function calls
SELECT ABS(-10), UPPER('hello'), ROUND(3.14159, 2);

Counting Rows

-- Total row count
SELECT COUNT(*) FROM employees;

-- Count non-NULL values in a column
SELECT COUNT(department) FROM employees;

-- Count distinct values
SELECT COUNT(DISTINCT department) FROM employees;

ORDER BY with Expressions

Sort results by column names, aliases, expressions, or ordinal positions.

-- By column name
SELECT name, salary FROM employees
ORDER BY salary DESC;

-- By alias
SELECT name, salary * 12 AS annual
FROM employees
ORDER BY annual DESC;

-- By expression
SELECT name, salary FROM employees
ORDER BY salary * 0.7 + 10000 DESC;

-- By ordinal position (1-based)
SELECT name, department, salary FROM employees
ORDER BY 2, 3 DESC;

-- Multiple keys
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Complete SELECT Syntax

The full SELECT statement follows this evaluation order:

SELECT [DISTINCT] expr1 [AS alias1], expr2 [AS alias2], ...
FROM table_name
[WHERE condition]
[GROUP BY col1, col2, ...]
[HAVING condition]
[ORDER BY col1 [ASC|DESC], ...]
[LIMIT count]
[OFFSET skip];

Logical evaluation order:

  1. FROM — source table(s)
  2. WHERE — row-level filtering
  3. GROUP BY — grouping
  4. HAVING — group-level filtering
  5. SELECT — expression evaluation and aliases
  6. DISTINCT — deduplication
  7. ORDER BY — sorting
  8. LIMIT / OFFSET — pagination

Using Queries from Node.js

import { Context } from 'teide-js';

const ctx = new Context();
ctx.sqlSync(`CREATE TABLE sales AS SELECT * FROM read_csv('sales.csv')`);

// Synchronous query
const top5 = ctx.sqlSync(`
  SELECT product, SUM(amount) AS total
  FROM sales
  GROUP BY product
  ORDER BY total DESC
  LIMIT 5
`);

// Access results
for (let i = 0; i < top5.height; i++) {
  const product = top5.column('product').get(i);
  const total = top5.column('total').get(i);
  console.log(`${product}: ${total}`);
}

// Async query
const result = await ctx.sql(`SELECT DISTINCT region FROM sales`);

ctx[Symbol.dispose]();
← DML Filtering →