Query Data in Databend

Databend supports standard SQL with ANSI SQL:2003 analytics extensions. This guide covers essential query techniques from basic to advanced, organized by learning path for optimal understanding.

Learning Path

📚 New to SQL? Start with Basic Queries
🔗 Joining data? Go to Combining Data
⚡ Need custom logic? Check Advanced Features
🚀 Performance issues? Visit Query Optimization

---

📚 Basic Queries

Master fundamental SQL operations for data selection and aggregation.

Filtering & Selection

-- Select and filter data
SELECT name, salary FROM employees 
WHERE department = 'Engineering' 
ORDER BY salary DESC;

Aggregating Data

-- Group and summarize data
SELECT department, 
       COUNT(*) as emp_count,
       AVG(salary) as avg_salary
FROM employees 
GROUP BY department;

Advanced Grouping

Multi-dimensional analysis with CUBE, ROLLUP, and GROUPING SETS

---

🔗 Combining Data

Connect data from multiple sources using JOINs and CTEs.

JOINs

-- Combine related tables
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

Common Table Expressions (CTE)

-- Structure complex queries
WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 75000
)
SELECT department, COUNT(*) as count
FROM high_earners GROUP BY department;

---

⚡ Advanced Features

Extend capabilities with custom functions and external integrations.

User-Defined Functions

-- Create reusable functions
CREATE FUNCTION calculate_bonus(salary FLOAT, rating FLOAT)
RETURNS FLOAT AS $$ salary * rating * 0.1 $$;

More Advanced Features

• External Functions - Cloud ML integration
• Stored Procedures - Multi-step operations
• Sequences - Unique ID generation

---

🚀 Query Optimization

Analyze and improve query performance with profiling tools.

Query Profile

Visual execution plan analysis (Databend Cloud: Monitor → SQL History)

Performance Analysis

-- Analyze query execution
EXPLAIN SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2023-01-01';

---

Quick Reference

Most Common Patterns

-- Top N query
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

-- Filter and aggregate  
SELECT department, AVG(salary) 
FROM employees 
WHERE hire_date >= '2023-01-01'
GROUP BY department
HAVING AVG(salary) > 70000;

-- Join with CTE
WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date >= '2023-01-01'
)
SELECT c.name, COUNT(*) as order_count
FROM customers c
JOIN recent_orders o ON c.id = o.customer_id
GROUP BY c.name;