> For the complete documentation index, see [llms.txt](https://docs.duql.dev/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.duql.dev/language-comparisons/vs-sql.md).

# DUQL vs SQL

DUQL (Dangerously Usable Query Language) is a modern, YAML-based query language that aims to simplify and enhance data manipulation and analysis tasks. While SQL has been the standard for database querying for decades, DUQL offers several advantages in terms of readability, maintainability, and expressiveness. Let's explore how DUQL compares to SQL across various aspects of data querying.

## Syntax Overview

### SQL:

```sql
SELECT column1, column2
FROM table_name
WHERE condition
GROUP BY column
HAVING group_condition
ORDER BY column DESC
LIMIT 10;
```

### DUQL:

```yaml
dataset: table_name
steps:
- filter: condition
- group:
    by: column
    steps:
    - summarize: [aggregate_column: aggregate_function]
- filter: group_condition
- sort: -column
- take: 10
- select: [column1, column2]
```

## Key Differences and Advantages

1. **Readability and Structure**
   * SQL: Uses a declarative syntax with keywords that don't always reflect the order of execution.
   * DUQL: Employs a step-by-step pipeline approach, making the query flow more intuitive and easier to follow.
2. **Data Source Specification**
   * SQL: Uses the `FROM` clause, which can become complex with multiple joins.
   * DUQL: Uses the `dataset` key, simplifying the initial data source definition.
3. **Filtering**
   * SQL: Uses `WHERE` for row filtering and `HAVING` for group filtering.
   * DUQL: Uses a unified `filter` step for both row and group filtering, simplifying the mental model.
4. **Aggregations and Grouping**
   * SQL: Separates `GROUP BY` and aggregate functions in the `SELECT` clause.
   * DUQL: Combines grouping and aggregation in a single `group` step, making the relationship clearer.
5. **Sorting**
   * SQL: Uses `ORDER BY` with `ASC` or `DESC` keywords.
   * DUQL: Uses a `sort` step with a more intuitive `-` prefix for descending order.
6. **Limiting Results**
   * SQL: Uses `LIMIT` or `TOP` (depending on the SQL dialect).
   * DUQL: Uses a `take` step, which is more consistent across different data sources.
7. **Column Selection**
   * SQL: Column selection is typically at the beginning of the query in the `SELECT` clause.
   * DUQL: The `select` step is often at the end, allowing for selection of computed columns more naturally.
8. **Joins**
   * SQL: Uses various `JOIN` clauses with `ON` conditions.
   * DUQL: Simplifies joins with a more readable syntax, especially for common equality joins.
9. **Window Functions**
   * SQL: Requires complex `OVER` clauses for window functions.
   * DUQL: Provides a more structured and readable way to define window operations.
10. **Variable and Function Declaration**
    * SQL: Typically requires separate statements or CTEs for variable and function declarations.
    * DUQL: Offers a `declare` section for defining reusable components within the query structure.

## Examples: SQL vs DUQL

Let's compare SQL and DUQL across various common querying tasks:

### Example 1: Basic Filtering and Sorting

SQL:

```sql
SELECT name, age, salary
FROM employees
WHERE age > 30
ORDER BY salary;
```

DUQL:

```yaml
dataset: employees
steps:
- filter: age > 30
- sort: salary
- select: [name, age, salary]
```

### Example 2: Aggregation and Grouping

SQL:

```sql
SELECT product_category,
       SUM(sale_amount) AS total_revenue,
       COUNT(*) AS order_count
FROM orders
GROUP BY product_category
ORDER BY total_revenue DESC
LIMIT 5;
```

DUQL:

```yaml
dataset: orders
steps:
- group:
  by: product_category
  steps:
  - summarize:
      total_revenue: sum sale_amount
      order_count: count order_id
- sort: -total_revenue
- take: 5
```

### Example 3: Joins and Complex Conditions

SQL:

```sql
SELECT o.order_id, c.customer_name, p.product_name,
       o.quantity * p.price AS total_amount,
       CASE
         WHEN o.quantity * p.price > 1000 THEN 0.15
         WHEN o.quantity * p.price > 500 THEN 0.10
         WHEN o.quantity * p.price > 100 THEN 0.05
         ELSE 0
       END AS discount_rate
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE c.last_purchase > '2023-01-01'
ORDER BY total_amount DESC;
```

DUQL:

```yaml
dataset: orders
steps:
- join:
  dataset: customers
  where: orders.customer_id == customers.customer_id
- join:
    dataset: products
    where: orders.product_id == products.product_id
- filter: customers.last_purchase > @2023-01-01
- generate:
    total_amount: quantity * products.price
    discount_rate:
      case:
      - total_amount > 1000: 0.15
      - total_amount > 500: 0.10
      - total_amount > 100: 0.05
      - true: 0
- sort: -total_amount
- select: [order_id, customers.customer_name, products.product_name, total_amount, discount_rate]
```

## Conclusion

While SQL remains a powerful and widely-used language for database querying, DUQL offers several advantages:

1. **Improved Readability**: DUQL's YAML-based syntax and pipeline structure make queries easier to read and understand, especially for complex operations.
2. **Logical Flow**: The step-by-step approach in DUQL mirrors the logical flow of data transformation, making it easier to reason about and modify queries.
3. **Consistency**: DUQL provides a more consistent syntax across different operations, reducing the need to remember various SQL-specific clauses and keywords.
4. **Expressiveness**: Features like the `generate` step and improved `case` syntax allow for more expressive and concise data transformations.
5. **Reusability**: The `declare` section in DUQL enhances code reusability and modularity, which can be more challenging in SQL.
6. **Simplified Complex Operations**: DUQL simplifies traditionally complex SQL operations, such as window functions and multi-table joins.

By adopting DUQL, data analysts and developers can write more maintainable, readable, and efficient queries, potentially leading to faster development cycles and fewer errors in data analysis pipelines. However, it's important to note that SQL's widespread use and direct integration with many database systems still make it a crucial skill in the data world. DUQL serves as an excellent abstraction layer that can generate optimized SQL, bridging the gap between intuitive query design and efficient database execution.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.duql.dev/language-comparisons/vs-sql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.