ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/revoke.md)

Revokes privileges from users or roles.

## Syntax[​](#syntax "Direct link to Syntax")

**Revoking privileges from users**

```
REVOKE [ON CLUSTER cluster_name] privilege[(column_name [,...])] [,...] ON {db.table|db.*|*.*|table|*} FROM {user | CURRENT_USER} [,...] | ALL | ALL EXCEPT {user | CURRENT_USER} [,...]
```

**Revoking roles from users**

```
REVOKE [ON CLUSTER cluster_name] [ADMIN OPTION FOR] role [,...] FROM {user | role | CURRENT_USER} [,...] | ALL | ALL EXCEPT {user_name | role_name | CURRENT_USER} [,...]
```

## Description[​](#description "Direct link to Description")

To revoke some privilege you can use a privilege of a wider scope than you plan to revoke. For example, if a user has the `SELECT (x,y)` privilege, administrator can execute `REVOKE SELECT(x,y) ...`, or `REVOKE SELECT * ...`, or even `REVOKE ALL PRIVILEGES ...` query to revoke this privilege.

### Partial Revokes[​](#partial-revokes "Direct link to Partial Revokes")

You can revoke a part of a privilege. For example, if a user has the `SELECT *.*` privilege you can revoke from it a privilege to read data from some table or a database.

## Examples[​](#examples "Direct link to Examples")

Grant the `john` user account with a privilege to select from all the databases, excepting the `accounts` one:

```
GRANT SELECT ON *.* TO john;
REVOKE SELECT ON accounts.* FROM john;
```

Grant the `mira` user account with a privilege to select from all the columns of the `accounts.staff` table, excepting the `wage` one.

```
GRANT SELECT ON accounts.staff TO mira;
REVOKE SELECT(wage) ON accounts.staff FROM mira;
```

[Original article](/docs/operations/settings/settings)

- [Syntax](#syntax)- [Description](#description)
    - [Partial Revokes](#partial-revokes)- [Examples](#examples)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/all.md)

If there are multiple matching rows in a table, then `ALL` returns all of them. `SELECT ALL` is identical to `SELECT` without `DISTINCT`. If both `ALL` and `DISTINCT` are specified, then an exception will be thrown.

`ALL` can be specified inside aggregate functions, although it has no practical effect on the query's result.

For example:

```
SELECT sum(ALL number) FROM numbers(10);
```

Is equivalent to:

```
SELECT sum(number) FROM numbers(10);
```

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/apply_modifier.md)

> Allows you to invoke some function for each row returned by an outer table expression of a query.

## Syntax[​](#syntax "Direct link to Syntax")

```
SELECT <expr> APPLY( <func> ) FROM [db.]table_name
```

## Example[​](#example "Direct link to Example")

```
CREATE TABLE columns_transformers (i Int64, j Int16, k Int64) ENGINE = MergeTree ORDER by (i);
INSERT INTO columns_transformers VALUES (100, 10, 324), (120, 8, 23);
SELECT * APPLY(sum) FROM columns_transformers;
```

```
┌─sum(i)─┬─sum(j)─┬─sum(k)─┐
│    220 │     18 │    347 │
└────────┴────────┴────────┘
```

- [Syntax](#syntax)- [Example](#example)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/distinct.md)

If `SELECT DISTINCT` is specified, only unique rows will remain in a query result. Thus, only a single row will remain out of all the sets of fully matching rows in the result.

You can specify the list of columns that must have unique values: `SELECT DISTINCT ON (column1, column2,...)`. If the columns are not specified, all of them are taken into consideration.

Consider the table:

```
┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 2 │ 2 │ 2 │
│ 1 │ 1 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘
```

Using `DISTINCT` without specifying columns:

```
SELECT DISTINCT * FROM t1;
```

```
┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 1 │ 1 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘
```

Using `DISTINCT` with specified columns:

```
SELECT DISTINCT ON (a,b) * FROM t1;
```

```
┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘
```

## DISTINCT and ORDER BY[​](#distinct-and-order-by "Direct link to DISTINCT and ORDER BY")

ClickHouse supports using the `DISTINCT` and `ORDER BY` clauses for different columns in one query. The `DISTINCT` clause is executed before the `ORDER BY` clause.

Consider the table:

```
┌─a─┬─b─┐
│ 2 │ 1 │
│ 1 │ 2 │
│ 3 │ 3 │
│ 2 │ 4 │
└───┴───┘
```

Selecting data:

```
SELECT DISTINCT a FROM t1 ORDER BY b ASC;
```

```
┌─a─┐
│ 2 │
│ 1 │
│ 3 │
└───┘
```

Selecting data with the different sorting direction:

```
SELECT DISTINCT a FROM t1 ORDER BY b DESC;
```

```
┌─a─┐
│ 3 │
│ 1 │
│ 2 │
└───┘
```

Row `2, 4` was cut before sorting.

Take this implementation specificity into account when programming queries.

## Null Processing[​](#null-processing "Direct link to Null Processing")

`DISTINCT` works with [NULL](/docs/sql-reference/syntax#null) as if `NULL` were a specific value, and `NULL==NULL`. In other words, in the `DISTINCT` results, different combinations with `NULL` occur only once. It differs from `NULL` processing in most other contexts.

## Alternatives[​](#alternatives "Direct link to Alternatives")

It is possible to obtain the same result by applying [GROUP BY](/docs/sql-reference/statements/select/group-by) across the same set of values as specified as `SELECT` clause, without using any aggregate functions. But there are few differences from `GROUP BY` approach:

- `DISTINCT` can be applied together with `GROUP BY`.
- When [ORDER BY](/docs/sql-reference/statements/select/order-by) is omitted and [LIMIT](/docs/sql-reference/statements/select/limit) is defined, the query stops running immediately after the required number of different rows has been read.
- Data blocks are output as they are processed, without waiting for the entire query to finish running.

- [DISTINCT and ORDER BY](#distinct-and-order-by)- [Null Processing](#null-processing)- [Alternatives](#alternatives)

Was this page helpful?
title_wip

```
┌─crypto_name─┐
│ Bitcoin     │
│ Bitcoin     │
└─────────────┘
```

This means of the four cryptocurrencies we own, only Bitcoin has never dropped below $10 (based on the limited data we have here in this example).

### Using `EXCEPT DISTINCT`[​](#using-except-distinct "Direct link to using-except-distinct")

Notice in the previous query we had multiple Bitcoin holdings in the result. You can add `DISTINCT` to `EXCEPT` to eliminate duplicate rows from the result:

```
SELECT crypto_name FROM holdings
EXCEPT DISTINCT
SELECT crypto_name FROM crypto_prices
WHERE price < 10;
```

```
┌─crypto_name─┐
│ Bitcoin     │
└─────────────┘
```

**See Also**

- [UNION](/docs/sql-reference/statements/select/union)
- [INTERSECT](/docs/sql-reference/statements/select/intersect)

- [Syntax](#syntax)- [Examples](#examples)
    - [Filtering Numbers Using the `EXCEPT` Clause](#filtering-numbers-using-the-except-clause)- [Excluding Specific Columns Using `EXCEPT()`](#excluding-specific-columns-using-except)- [Using `EXCEPT` and `INTERSECT` with Cryptocurrency Data](#using-except-and-intersect-with-cryptocurrency-data)- [Using `EXCEPT DISTINCT`](#using-except-distinct)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/except_modifier.md)

> Specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output.

## Syntax[​](#syntax "Direct link to Syntax")

```
SELECT <expr> EXCEPT ( col_name1 [, col_name2, col_name3, ...] ) FROM [db.]table_name
```

## Examples[​](#examples "Direct link to Examples")

```
SELECT * EXCEPT (i) from columns_transformers;
```

```
┌──j─┬───k─┐
│ 10 │ 324 │
│  8 │  23 │
└────┴─────┘
```

- [Syntax](#syntax)- [Examples](#examples)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/having.md)

Allows filtering the aggregation results produced by [GROUP BY](/docs/sql-reference/statements/select/group-by). It is similar to the [WHERE](/docs/sql-reference/statements/select/where) clause, but the difference is that `WHERE` is performed before aggregation, while `HAVING` is performed after it.

It is possible to reference aggregation results from `SELECT` clause in `HAVING` clause by their alias. Alternatively, `HAVING` clause can filter on results of additional aggregates that are not returned in query results.

## Example[​](#example "Direct link to Example")

If you have a `sales` table as follows:

```
CREATE TABLE sales
(
    region String,
    salesperson String,
    amount Float64
)
ORDER BY (region, salesperson);
```

You can query it like so:

```
SELECT
    region,
    salesperson,
    sum(amount) AS total_sales
FROM sales
GROUP BY
    region,
    salesperson
HAVING total_sales > 10000
ORDER BY total_sales DESC;
```

This will list sales people with greater than 10,000 in total sales in their region.

## Limitations[​](#limitations "Direct link to Limitations")

`HAVING` can't be used if aggregation is not performed. Use `WHERE` instead.

- [Example](#example)- [Limitations](#limitations)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/offset.md)

`OFFSET` and `FETCH` allow you to retrieve data by portions. They specify a row block which you want to get by a single query.

```
-- SQL Standard style:
[OFFSET offset_row_count {ROW | ROWS}] [FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} {ONLY | WITH TIES}]

-- MySQL/PostgreSQL style:
[LIMIT [n, ]m] [OFFSET offset_row_count]
```

The `offset_row_count` or `fetch_row_count` value can be a number or a literal constant. You can omit `fetch_row_count`; by default, it equals to 1.

`OFFSET` specifies the number of rows to skip before starting to return rows from the query result set. `OFFSET n` skips the first `n` rows from the result.

Negative OFFSET is supported: `OFFSET -n` skips the last `n` rows from the result.

Fractional OFFSET is also supported: `OFFSET n` - if 0 < n < 1, then the first n \* 100% of the result is skipped.

Example:  
• `OFFSET 0.1` - skips the first 10% of the result.

> **Note**  
> • The fraction must be a [Float64](/docs/sql-reference/data-types/float) number less than 1 and greater than zero.  
> • If a fractional number of rows results from the calculation, it is rounded up to the next whole number.

The `FETCH` specifies the maximum number of rows that can be in the result of a query.

The `ONLY` option is used to return rows that immediately follow the rows omitted by the `OFFSET`. In this case the `FETCH` is an alternative to the [LIMIT](/docs/sql-reference/statements/select/limit) clause. For example, the following query

```
SELECT * FROM test_fetch ORDER BY a OFFSET 1 ROW FETCH FIRST 3 ROWS ONLY;
```

is identical to the query

```
SELECT * FROM test_fetch ORDER BY a LIMIT 3 OFFSET 1;
```

The `WITH TIES` option is used to return any additional rows that tie for the last place in the result set according to the `ORDER BY` clause. For example, if `fetch_row_count` is set to 5 but two additional rows match the values of the `ORDER BY` columns in the fifth row, the result set will contain seven rows.

Note

According to the standard, the `OFFSET` clause must come before the `FETCH` clause if both are present.

Note

The real offset can also depend on the [offset](/docs/operations/settings/settings#offset) setting.

## Examples[​](#examples "Direct link to Examples")

Input table:

```
┌─a─┬─b─┐
│ 1 │ 1 │
│ 2 │ 1 │
│ 3 │ 4 │
│ 1 │ 3 │
│ 5 │ 4 │
│ 0 │ 6 │
│ 5 │ 7 │
└───┴───┘
```

Usage of the `ONLY` option:

```
SELECT * FROM test_fetch ORDER BY a OFFSET 3 ROW FETCH FIRST 3 ROWS ONLY;
```

Result:

```
┌─a─┬─b─┐
│ 2 │ 1 │
│ 3 │ 4 │
│ 5 │ 4 │
└───┴───┘
```

Usage of the `WITH TIES` option:

```
SELECT * FROM test_fetch ORDER BY a OFFSET 3 ROW FETCH FIRST 3 ROWS WITH TIES;
```

Result:

```
┌─a─┬─b─┐
│ 2 │ 1 │
│ 3 │ 4 │
│ 5 │ 4 │
│ 5 │ 7 │
└───┴───┘
```

- [Examples](#examples)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/qualify.md)

Allows filtering window functions results. It is similar to the [WHERE](/docs/sql-reference/statements/select/where) clause, but the difference is that `WHERE` is performed before window functions evaluation, while `QUALIFY` is performed after it.

It is possible to reference window functions results from `SELECT` clause in `QUALIFY` clause by their alias. Alternatively, `QUALIFY` clause can filter on results of additional window functions that are not returned in query results.

## Limitations[​](#limitations "Direct link to Limitations")

`QUALIFY` can't be used if there are no window functions to evaluate. Use `WHERE` instead.

## Examples[​](#examples "Direct link to Examples")

Example:

```
SELECT number, COUNT() OVER (PARTITION BY number % 3) AS partition_count
FROM numbers(10)
QUALIFY partition_count = 4
ORDER BY number;
```

```
┌─number─┬─partition_count─┐
│      0 │               4 │
│      3 │               4 │
│      6 │               4 │
│      9 │               4 │
└────────┴─────────────────┘
```

- [Limitations](#limitations)- [Examples](#examples)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/replace_modifier.md)

> Allows you to specify one or more [expression aliases](/docs/sql-reference/syntax#expression-aliases).

Each alias must match a column name from the `SELECT *` statement. In the output column list, the column that matches
the alias is replaced by the expression in that `REPLACE`.

This modifier does not change the names or order of columns. However, it can change the value and the value type.

**Syntax:**

```
SELECT <expr> REPLACE( <expr> AS col_name) from [db.]table_name
```

**Example:**

```
SELECT * REPLACE(i + 1 AS i) from columns_transformers;
```

```
┌───i─┬──j─┬───k─┐
│ 101 │ 10 │ 324 │
│ 121 │  8 │  23 │
└─────┴────┴─────┘
```

Was this page helpful?
title_wip

```
-- Different price thresholds by category
SELECT * FROM products
WHERE if(category = 'Electronics', price < 500, price < 200);
-- Result: Mouse, Chair, Monitor
-- (Electronics under $500 OR Furniture under $200)

-- Filter based on stock status
SELECT * FROM products
WHERE if(in_stock, price > 100, true);
-- Result: Laptop, Chair, Monitor, Desk, Lamp
-- (In stock items over $100 OR all out-of-stock items)
```

#### multiIf examples[​](#multiif-examples "Direct link to multiIf examples")

```
-- Multiple category-based conditions
SELECT * FROM products
WHERE multiIf(
    category = 'Electronics', price < 600,
    category = 'Furniture', in_stock = true,
    false
);
-- Result: Mouse, Monitor, Chair
-- (Electronics < $600 OR in-stock Furniture)

-- Tiered filtering
SELECT * FROM products
WHERE multiIf(
    price > 500, category = 'Electronics',
    price > 100, in_stock = true,
    true
);
-- Result: Laptop, Chair, Monitor, Lamp
```

#### CASE examples[​](#case-examples "Direct link to CASE examples")

**Simple CASE:**

```
-- Different rules per category
SELECT * FROM products
WHERE CASE category
    WHEN 'Electronics' THEN price < 400
    WHEN 'Furniture' THEN in_stock = true
    ELSE false
END;
-- Result: Mouse, Monitor, Chair
```

**Searched CASE:**

```
-- Price-based tiered logic
SELECT * FROM products
WHERE CASE
    WHEN price > 500 THEN in_stock = true
    WHEN price > 100 THEN category = 'Electronics'
    ELSE true
END;
-- Result: Laptop, Monitor, Mouse, Lamp
```

- [Testing for `NULL`](#testing-for-null)- [Filtering data with logical operators](#filtering-data-with-logical-operators)- [Using UInt8 columns as a condition](#using-uint8-columns-as-a-condition)- [Using comparison operators](#using-comparison-operators)- [Pattern matching and conditional expressions](#pattern-matching-and-conditional-expressions)- [Expression with literals, columns or subqueries](#expressions-with-literals-columns-subqueries)- [Examples](#examples)
              - [Testing for `NULL`](#examples-testing-for-null)- [Filtering data with logical operators](#example-filtering-with-logical-operators)- [Using UInt8 columns as a condition](#example-uint8-column-as-condition)- [Using comparison operators](#example-using-comparison-operators)- [Pattern matching and conditional expressions](#examples-pattern-matching-and-conditional-expressions)

Was this page helpful?
SET Statement

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/set.md)

```
SET param = value
```

Assigns `value` to the `param` [setting](/docs/operations/settings/overview) for the current session. You cannot change [server settings](/docs/operations/server-configuration-parameters/settings) this way.

You can also set all the values from the specified settings profile in a single query.

```
SET profile = 'profile-name-from-the-settings-file'
```

For boolean settings set to true, you can use a shorthand syntax by omitting the value assignment. When only the setting name is specified, it is automatically set to `1` (true).

```
-- These are equivalent:
SET force_index_by_date = 1
SET force_index_by_date
```

## SET TIME ZONE[​](#set-time-zone "Direct link to SET TIME ZONE")

```
SET TIME ZONE [=] 'timezone'
```

Sets the session time zone. This is an alias for `SET session_timezone = 'timezone'`, provided for compatibility with PostgreSQL and other SQL databases.

Many SQL clients, ORMs, and JDBC drivers automatically issue `SET TIME ZONE` when connecting. This syntax allows such tools to work with ClickHouse without custom workarounds.

```
SET TIME ZONE 'UTC';
SET TIME ZONE 'Europe/Amsterdam';
SET TIME ZONE 'America/New_York';

-- Verify the current session time zone
SELECT getSetting('session_timezone');
```

The timezone value must be a valid name from the [IANA Time Zone Database](https://www.iana.org/time-zones). An invalid timezone name will result in an error.

For more information about the `session_timezone` setting, see [session\_timezone](/docs/operations/settings/settings#session_timezone).

## Setting query parameters[​](#setting-query-parameters "Direct link to Setting query parameters")

The `SET` statement can also be used to define query parameters by prefixing the parameter name with `param_`.
Query parameters allow you to write generic queries with placeholders that are replaced with actual values at execution time.

```
SET param_name = value
```

To use a query parameter in your query, reference it with the syntax `{name: datatype}`:

```
SET param_id = 42;
SET param_name = 'John';

SELECT * FROM users
WHERE id = {id: UInt32}
AND name = {name: String};
```

Query parameters are particularly useful when the same query needs to be executed multiple times with different values.

For more detailed information about query parameters, including usage with the `Identifier` type, see [Defining and Using Query Parameters](/docs/sql-reference/syntax#defining-and-using-query-parameters).

For more information, see [Settings](/docs/operations/settings/settings).

- [SET TIME ZONE](#set-time-zone)- [Setting query parameters](#setting-query-parameters)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/set-role.md)

Activates roles for the current user.

```
SET ROLE {DEFAULT | NONE | role [,...] | ALL | ALL EXCEPT role [,...]}
```

## SET DEFAULT ROLE[​](#set-default-role "Direct link to SET DEFAULT ROLE")

Sets default roles to a user.

Default roles are automatically activated at user login. You can set as default only the previously granted roles. If the role isn't granted to a user, ClickHouse throws an exception.

```
SET DEFAULT ROLE {NONE | role [,...] | ALL | ALL EXCEPT role [,...]} TO {user|CURRENT_USER} [,...]
```

## Examples[​](#examples "Direct link to Examples")

Set multiple default roles to a user:

```
SET DEFAULT ROLE role1, role2, ... TO user
```

Set all the granted roles as default to a user:

```
SET DEFAULT ROLE ALL TO user
```

Purge default roles from a user:

```
SET DEFAULT ROLE NONE TO user
```

Set all the granted roles as default except for specific roles `role1` and `role2`:

```
SET DEFAULT ROLE ALL EXCEPT role1, role2 TO user
```

- [SET DEFAULT ROLE](#set-default-role)- [Examples](#examples)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/undrop.md)

Cancels the dropping of the table.

Beginning with ClickHouse version 23.3 it is possible to UNDROP a table in an Atomic database
within `database_atomic_delay_before_drop_table_sec` (8 minutes by default) of issuing the DROP TABLE statement. Dropped tables are listed in
a system table called `system.dropped_tables`.

If you have a materialized view without a `TO` clause associated with the dropped table, then you will also have to UNDROP the inner table of that view.

Tip

Also see [DROP TABLE](/docs/sql-reference/statements/drop)

Syntax:

```
UNDROP TABLE [db.]name [UUID '<uuid>'] [ON CLUSTER cluster]
```

**Example**

```
CREATE TABLE tab
(
    `id` UInt8
)
ENGINE = MergeTree
ORDER BY id;

DROP TABLE tab;

SELECT *
FROM system.dropped_tables
FORMAT Vertical;
```

```
Row 1:
──────
index:                 0
database:              default
table:                 tab
uuid:                  aa696a1a-1d70-4e60-a841-4c80827706cc
engine:                MergeTree
metadata_dropped_path: /var/lib/clickhouse/metadata_dropped/default.tab.aa696a1a-1d70-4e60-a841-4c80827706cc.sql
table_dropped_time:    2023-04-05 14:12:12

1 row in set. Elapsed: 0.001 sec.
```

```
UNDROP TABLE tab;

SELECT *
FROM system.dropped_tables
FORMAT Vertical;

```response
Ok.

0 rows in set. Elapsed: 0.001 sec.
```

```
DESCRIBE TABLE tab
FORMAT Vertical;
```

```
Row 1:
──────
name:               id
type:               UInt8
default_type:       
default_expression: 
comment:            
codec_expression:   
ttl_expression:
```

Was this page helpful?