🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 89 (from laksa118)

2. Crawled Status Check

Query:

curl -X POST \
  'http://laksa089.int.ahrefs:8124/' \
  -H 'Content-Type: text/plain' \
  -H 'X-ClickHouse-Database: crawler3' \
  -H 'Authorization: Basic YXBpOg==' \
  -d 'SELECT getAhrefsURLFromUnparsed(src_unparsed) AS found_url, ifNull(toUnixTimestamp(download_stamp), 0) AS crawl_time, ifNull(toUnixTimestamp(props_url_first_seen), 0) AS first_indexed_time, download_http_code AS http_code, src_unparsed AS src_unparsed, src_root_hash AS src_root_hash, history_drop_reason AS history_drop_reason, meta_title AS meta_title, meta_descriptions AS meta_descriptions, attrs_boilerpipe_text AS attrs_boilerpipe_text, attrs_markdown AS attrs_markdown, attrs_readable_markdown AS attrs_readable_markdown, meta_canonical AS meta_canonical FROM crawler3.page_info_local FINAL PREWHERE (src_root_hash, src_unparsed) IN ((getAhrefsRootHashFromUnparsed(getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/sql-reference/statements/select\')), getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/sql-reference/statements/select\'))) FORMAT JSONEachRow'

Response:

{"found_url":"https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select","crawl_time":1776121463,"first_indexed_time":1740098482,"http_code":200,"src_unparsed":"com,clickhouse!\/docs\/sql-reference\/statements\/select s443","src_root_hash":"12633450985039531489","history_drop_reason":null,"meta_title":"SELECT Query | ClickHouse Docs","meta_descriptions":["Documentation for SELECT Query"],"attrs_boilerpipe_text":"SELECT\nqueries perform data retrieval. By default, the requested data is returned to the client, while in conjunction with\nINSERT INTO\nit can be forwarded to a different table.\nSyntax\n\n[\nWITH\nexpr_list\n(\nsubquery\n)\n]\nSELECT\n[\nDISTINCT\n[\nON\n(\ncolumn1\n,\ncolumn2\n,\n.\n.\n.\n)\n]\n]\nexpr_list\n[\nFROM\n[\ndb\n.\n]\ntable\n|\n(\nsubquery\n)\n|\ntable_function\n]\n[\nFINAL\n]\n[\nSAMPLE sample_coeff\n]\n[\nARRAY\nJOIN\n.\n.\n.\n]\n[\nGLOBAL\n]\n[\nANY\n|\nALL\n|\nASOF\n]\n[\nINNER\n|\nLEFT\n|\nRIGHT\n|\nFULL\n|\nCROSS\n]\n[\nOUTER\n|\nSEMI\n|\nANTI\n]\nJOIN\n(\nsubquery\n)\n|\ntable\n[\n(\nalias1\n[\n,\nalias2\n.\n.\n.\n]\n)\n]\n(\nON\n<\nexpr_list\n>\n)\n|\n(\nUSING\n<\ncolumn_list\n>\n)\n[\nPREWHERE expr\n]\n[\nWHERE\nexpr\n]\n[\nGROUP\nBY\nexpr_list\n]\n[\nWITH ROLLUP\n|\nWITH\nCUBE\n]\n[\nWITH\nTOTALS\n]\n[\nHAVING\nexpr\n]\n[\nWINDOW window_expr_list\n]\n[\nQUALIFY expr\n]\n[\nORDER\nBY\nexpr_list\n]\n[\nWITH\nFILL\n]\n[\nFROM\nexpr\n]\n[\nTO\nexpr\n]\n[\nSTEP expr\n]\n[\nINTERPOLATE\n[\n(\nexpr_list\n)\n]\n]\n[\nLIMIT\n[\noffset_value\n,\n]\nn\nBY\ncolumns\n]\n[\nLIMIT\n[\nn\n,\n]\nm\n]\n[\nWITH\nTIES\n]\n[\nSETTINGS\n.\n.\n.\n]\n[\nUNION\n.\n.\n.\n]\n[\nINTO\nOUTFILE\nfilename\n[\nTRUNCATE\n]\n[\nCOMPRESSION\ntype\n[\nLEVEL\nlevel\n]\n]\n]\n[\nFORMAT format\n]\nAll clauses are optional, except for the required list of expressions immediately after\nSELECT\nwhich is covered in more detail\nbelow\n.\nSpecifics of each optional clause are covered in separate sections, which are listed in the same order as they are executed:\nWITH clause\nSELECT clause\nDISTINCT clause\nFROM clause\nSAMPLE clause\nJOIN clause\nPREWHERE clause\nWHERE clause\nWINDOW clause\nGROUP BY clause\nLIMIT BY clause\nHAVING clause\nQUALIFY clause\nLIMIT clause\nOFFSET clause\nUNION clause\nINTERSECT clause\nEXCEPT clause\nINTO OUTFILE clause\nFORMAT clause\nSELECT Clause\n\nExpressions\nspecified in the\nSELECT\nclause are calculated after all the operations in the clauses described above are finished. These expressions work as if they apply to separate rows in the result. If expressions in the\nSELECT\nclause contain aggregate functions, then ClickHouse processes aggregate functions and expressions used as their arguments during the\nGROUP BY\naggregation.\nIf you want to include all columns in the result, use the asterisk (\n*\n) symbol. For example,\nSELECT * FROM ...\n.\nDynamic column selection\n\nDynamic column selection (also known as a COLUMNS expression) allows you to match some columns in a result with a\nre2\nregular expression.\nCOLUMNS\n(\n'regexp'\n)\nFor example, consider the table:\nCREATE\nTABLE\ndefault\n.\ncol_names\n(\naa Int8\n,\nab Int8\n,\nbc Int8\n)\nENGINE\n=\nTinyLog\nThe following query selects data from all the columns containing the\na\nsymbol in their name.\nSELECT\nCOLUMNS\n(\n'a'\n)\nFROM\ncol_names\n┌─aa─┬─ab─┐\n│  1 │  1 │\n└────┴────┘\nThe selected columns are returned not in the alphabetical order.\nYou can use multiple\nCOLUMNS\nexpressions in a query and apply functions to them.\nFor example:\nSELECT\nCOLUMNS\n(\n'a'\n)\n,\nCOLUMNS\n(\n'c'\n)\n,\ntoTypeName\n(\nCOLUMNS\n(\n'c'\n)\n)\nFROM\ncol_names\n┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐\n│  1 │  1 │  1 │ Int8           │\n└────┴────┴────┴────────────────┘\nEach column returned by the\nCOLUMNS\nexpression is passed to the function as a separate argument. Also you can pass other arguments to the function if it supports them. Be careful when using functions. If a function does not support the number of arguments you have passed to it, ClickHouse throws an exception.\nFor example:\nSELECT\nCOLUMNS\n(\n'a'\n)\n+\nCOLUMNS\n(\n'c'\n)\nFROM\ncol_names\nReceived exception from server (version 19.14.1):\nCode: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus does not match: passed 3, should be 2.\nIn this example,\nCOLUMNS('a')\nreturns two columns:\naa\nand\nab\n.\nCOLUMNS('c')\nreturns the\nbc\ncolumn. The\n+\noperator can't apply to 3 arguments, so ClickHouse throws an exception with the relevant message.\nColumns that matched the\nCOLUMNS\nexpression can have different data types. If\nCOLUMNS\ndoes not match any columns and is the only expression in\nSELECT\n, ClickHouse throws an exception.\nAsterisk\n\nYou can put an asterisk in any part of a query instead of an expression. When the query is analyzed, the asterisk is expanded to a list of all table columns (excluding the\nMATERIALIZED\nand\nALIAS\ncolumns). There are only a few cases when using an asterisk is justified:\nWhen creating a table dump.\nFor tables containing just a few columns, such as system tables.\nFor getting information about what columns are in a table. In this case, set\nLIMIT 1\n. But it is better to use the\nDESC TABLE\nquery.\nWhen there is strong filtration on a small number of columns using\nPREWHERE\n.\nIn subqueries (since columns that aren't needed for the external query are excluded from subqueries).\nIn all other cases, we do not recommend using the asterisk, since it only gives you the drawbacks of a columnar DBMS instead of the advantages. In other words using the asterisk is not recommended.\nExtreme Values\n\nIn addition to results, you can also get minimum and maximum values for the results columns. To do this, set the\nextremes\nsetting to 1. Minimums and maximums are calculated for numeric types, dates, and dates with times. For other columns, the default values are output.\nAn extra two rows are calculated – the minimums and maximums, respectively. These extra two rows are output in\nXML\n,\nJSON*\n,\nTabSeparated*\n,\nCSV*\n,\nVertical\n,\nTemplate\nand\nPretty*\nformats\n, separate from the other rows. They are not output for other formats.\nIn\nJSON*\nand\nXML\nformats, the extreme values are output in a separate 'extremes' field. In\nTabSeparated*\n,\nCSV*\nand\nVertical\nformats, the row comes after the main result, and after 'totals' if present. It is preceded by an empty row (after the other data). In\nPretty*\nformats, the row is output as a separate table after the main result, and after\ntotals\nif present. In\nTemplate\nformat the extreme values are output according to specified template.\nExtreme values are calculated for rows before\nLIMIT\n, but after\nLIMIT BY\n. However, when using\nLIMIT offset, size\n, the rows before\noffset\nare included in\nextremes\n. In stream requests, the result may also include a small number of rows that passed through\nLIMIT\n.\nNotes\n\nYou can use synonyms (\nAS\naliases) in any part of a query.\nThe\nGROUP BY\n,\nORDER BY\n, and\nLIMIT BY\nclauses can support positional arguments. To enable this, switch on the\nenable_positional_arguments\nsetting. Then, for example,\nORDER BY 1,2\nwill be sorting rows in the table on the first and then the second column.\nImplementation Details\n\nIf the query omits the\nDISTINCT\n,\nGROUP BY\nand\nORDER BY\nclauses and the\nIN\nand\nJOIN\nsubqueries, the query will be completely stream processed, using O(1) amount of RAM. Otherwise, the query might consume a lot of RAM if the appropriate restrictions are not specified:\nmax_memory_usage\nmax_rows_to_group_by\nmax_rows_to_sort\nmax_rows_in_distinct\nmax_bytes_in_distinct\nmax_rows_in_set\nmax_bytes_in_set\nmax_rows_in_join\nmax_bytes_in_join\nmax_bytes_before_external_sort\nmax_bytes_ratio_before_external_sort\nmax_bytes_before_external_group_by\nmax_bytes_ratio_before_external_group_by\nFor more information, see the section \"Settings\". It is possible to use external sorting (saving temporary tables to a disk) and external aggregation.\nSELECT modifiers\n\nYou can use the following modifiers in\nSELECT\nqueries.\nModifier\nDescription\nAPPLY\nAllows you to invoke some function for each row returned by an outer table expression of a query.\nEXCEPT\nSpecifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output.\nREPLACE\nSpecifies one or more\nexpression aliases\n. Each alias must match a column name from the\nSELECT *\nstatement. In the output column list, the column that matches the alias is replaced by the expression in that\nREPLACE\n. This modifier does not change the names or order of columns. However, it can change the value and the value type.\nModifier Combinations\n\nYou can use each modifier separately or combine them.\nExamples:\nUsing the same modifier multiple times.\nSELECT\nCOLUMNS\n(\n'[jk]'\n)\nAPPLY\n(\ntoString\n)\nAPPLY\n(\nlength\n)\nAPPLY\n(\nmax\n)\nFROM\ncolumns_transformers\n;\n┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐\n│                        2 │                        3 │\n└──────────────────────────┴──────────────────────────┘\nUsing multiple modifiers in a single query.\nSELECT\n*\nREPLACE\n(\ni\n+\n1\nAS\ni\n)\nEXCEPT\n(\nj\n)\nAPPLY\n(\nsum\n)\nfrom\ncolumns_transformers\n;\n┌─sum(plus(i, 1))─┬─sum(k)─┐\n│             222 │    347 │\n└─────────────────┴────────┘\nSETTINGS in SELECT Query\n\nYou can specify the necessary settings right in the\nSELECT\nquery. The setting value is applied only to this query and is reset to default or previous value after the query is executed.\nOther ways to make settings see\nhere\n.\nFor 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\n1\n(true).\nExample\nSELECT\n*\nFROM\nsome_table SETTINGS optimize_read_in_order\n=\n1\n,\ncast_keep_nullable\n=\n1\n;","attrs_markdown":"[Skip to main content](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.9k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=9b44e39d-c50a-4388-8882-41a3391300c1&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&utm_ga=GA1.1.13172778.1776121465)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=9b44e39d-c50a-4388-8882-41a3391300c1&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&utm_ga=GA1.1.13172778.1776121465)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n\n- [English](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/sql-reference\/statements\/select)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/sql-reference\/statements\/select)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/sql-reference\/statements\/select)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/sql-reference\/statements\/select)\n\n[Skip to main content](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.9k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=9b44e39d-c50a-4388-8882-41a3391300c1&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&utm_ga=GA1.1.13172778.1776121465)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=9b44e39d-c50a-4388-8882-41a3391300c1&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&utm_ga=GA1.1.13172778.1776121465)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n\n- [English](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/sql-reference\/statements\/select)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/sql-reference\/statements\/select)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/sql-reference\/statements\/select)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/sql-reference\/statements\/select)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n- [Introduction](https:\/\/clickhouse.com\/docs\/sql-reference)\n  - [Syntax](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax)\n  - [Input and Output Formats](https:\/\/clickhouse.com\/docs\/sql-reference\/formats)\n  - [Data types](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types)\n  - [Statements](https:\/\/clickhouse.com\/docs\/sql-reference\/statements)\n    - [SELECT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n      - [ALL](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/all)\n      - [APPLY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/apply-modifier)\n      - [ARRAY JOIN](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/array-join)\n      - [DISTINCT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/distinct)\n      - [EXCEPT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/except)\n      - [EXCEPT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/except-modifier)\n      - [FORMAT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/format)\n      - [FROM](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/from)\n      - [GROUP BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/group-by)\n      - [HAVING](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/having)\n      - [INTERSECT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/intersect)\n      - [INTO OUTFILE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/into-outfile)\n      - [JOIN](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/join)\n      - [LIMIT BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n      - [LIMIT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit)\n      - [OFFSET](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/offset)\n      - [ORDER BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/order-by)\n      - [PREWHERE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/prewhere)\n      - [QUALIFY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/qualify)\n      - [REPLACE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/replace-modifier)\n      - [SAMPLE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/sample)\n      - [UNION](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/union)\n      - [WHERE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/where)\n      - [WITH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/with)\n    - [INSERT INTO](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/insert-into)\n    - [CREATE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/create)\n    - [ALTER](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/alter)\n    - [DELETE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/delete)\n    - [SYSTEM](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/system)\n    - [SHOW](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/show)\n    - [GRANT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/grant)\n    - [EXPLAIN](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/explain)\n    - [REVOKE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/revoke)\n    - [UPDATE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/update)\n    - [ATTACH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/attach)\n    - [CHECK TABLE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/check-table)\n    - [DESCRIBE TABLE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/describe-table)\n    - [DETACH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/detach)\n    - [DROP](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/drop)\n    - [EXISTS](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/exists)\n    - [KILL](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/kill)\n    - [OPTIMIZE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/optimize)\n    - [RENAME](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/rename)\n    - [EXCHANGE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/exchange)\n    - [SET](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/set)\n    - [SET ROLE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/set-role)\n    - [TRUNCATE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/truncate)\n    - [EXECUTE AS](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/execute_as)\n    - [PARALLEL WITH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/parallel_with)\n    - [USE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/use)\n    - [WATCH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/watch)\n    - [MOVE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/move)\n    - [CHECK GRANT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/check-grant)\n    - [UNDROP](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/undrop)\n  - [Operators](https:\/\/clickhouse.com\/docs\/sql-reference\/operators)\n- [Engines](https:\/\/clickhouse.com\/docs\/engines)\n  - [Database Engines](https:\/\/clickhouse.com\/docs\/engines\/database-engines)\n  - [Table Engines](https:\/\/clickhouse.com\/docs\/engines\/table-engines)\n- [Functions](https:\/\/clickhouse.com\/docs\/sql-reference\/functions)\n  - [Regular functions](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/regular-functions)\n  - [Aggregate functions](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions)\n  - [Table functions](https:\/\/clickhouse.com\/docs\/sql-reference\/table-functions)\n  - [Window functions](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n- [Formats](https:\/\/clickhouse.com\/docs\/interfaces\/formats)\n- [Data Lakes](https:\/\/clickhouse.com\/docs\/sql-reference\/datalakes)\n\n- [Introduction](https:\/\/clickhouse.com\/docs\/sql-reference)\n- [Statements](https:\/\/clickhouse.com\/docs\/sql-reference\/statements)\n- SELECT\n\n[Edit this page](https:\/\/github.com\/ClickHouse\/ClickHouse\/tree\/master\/docs\/en\/sql-reference\/statements\/select\/index.md)\n\n# SELECT Query\n`SELECT` queries perform data retrieval. By default, the requested data is returned to the client, while in conjunction with [INSERT INTO](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/insert-into) it can be forwarded to a different table.\n\n## Syntax[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#syntax \"Direct link to Syntax\")\n```\n[WITH expr_list(subquery)]\nSELECT [DISTINCT [ON (column1, column2, ...)]] expr_list\n[FROM [db.]table | (subquery) | table_function] [FINAL]\n[SAMPLE sample_coeff]\n[ARRAY JOIN ...]\n[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table [(alias1 [, alias2 ...])] (ON <expr_list>)|(USING <column_list>)\n[PREWHERE expr]\n[WHERE expr]\n[GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]\n[HAVING expr]\n[WINDOW window_expr_list]\n[QUALIFY expr]\n[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr] [INTERPOLATE [(expr_list)]]\n[LIMIT [offset_value, ]n BY columns]\n[LIMIT [n, ]m] [WITH TIES]\n[SETTINGS ...]\n[UNION  ...]\n[INTO OUTFILE filename [TRUNCATE] [COMPRESSION type [LEVEL level]] ]\n[FORMAT format]\n```\nAll clauses are optional, except for the required list of expressions immediately after `SELECT` which is covered in more detail [below](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#select-clause).\n\nSpecifics of each optional clause are covered in separate sections, which are listed in the same order as they are executed:\n\n- [WITH clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/with)\n- [SELECT clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#select-clause)\n- [DISTINCT clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/distinct)\n- [FROM clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/from)\n- [SAMPLE clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/sample)\n- [JOIN clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/join)\n- [PREWHERE clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/prewhere)\n- [WHERE clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/where)\n- [WINDOW clause](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n- [GROUP BY clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/group-by)\n- [LIMIT BY clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n- [HAVING clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/having)\n- [QUALIFY clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/qualify)\n- [LIMIT clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit)\n- [OFFSET clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/offset)\n- [UNION clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/union)\n- [INTERSECT clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/intersect)\n- [EXCEPT clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/except)\n- [INTO OUTFILE clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/into-outfile)\n- [FORMAT clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/format)\n## SELECT Clause[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#select-clause \"Direct link to SELECT Clause\")\n[Expressions](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax#expressions) specified in the `SELECT` clause are calculated after all the operations in the clauses described above are finished. These expressions work as if they apply to separate rows in the result. If expressions in the `SELECT` clause contain aggregate functions, then ClickHouse processes aggregate functions and expressions used as their arguments during the [GROUP BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/group-by) aggregation.\n\nIf you want to include all columns in the result, use the asterisk (`*`) symbol. For example, `SELECT * FROM ...`.\n\n### Dynamic column selection[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#dynamic-column-selection \"Direct link to Dynamic column selection\")\nDynamic column selection (also known as a COLUMNS expression) allows you to match some columns in a result with a [re2](https:\/\/en.wikipedia.org\/wiki\/RE2_\$software\$) regular expression.\n```\nCOLUMNS('regexp')\n```\nFor example, consider the table:\n```\nCREATE TABLE default.col_names (aa Int8, ab Int8, bc Int8) ENGINE = TinyLog\n```\nThe following query selects data from all the columns containing the `a` symbol in their name.\n```\nSELECT COLUMNS('a') FROM col_names\n```\n```\n┌─aa─┬─ab─┐\n│  1 │  1 │\n└────┴────┘\n```\nThe selected columns are returned not in the alphabetical order.\n\nYou can use multiple `COLUMNS` expressions in a query and apply functions to them.\n\nFor example:\n```\nSELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM col_names\n```\n```\n┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐\n│  1 │  1 │  1 │ Int8           │\n└────┴────┴────┴────────────────┘\n```\nEach column returned by the `COLUMNS` expression is passed to the function as a separate argument. Also you can pass other arguments to the function if it supports them. Be careful when using functions. If a function does not support the number of arguments you have passed to it, ClickHouse throws an exception.\n\nFor example:\n```\nSELECT COLUMNS('a') + COLUMNS('c') FROM col_names\n```\n```\nReceived exception from server (version 19.14.1):\nCode: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus does not match: passed 3, should be 2.\n```\nIn this example, `COLUMNS('a')` returns two columns: `aa` and `ab`. `COLUMNS('c')` returns the `bc` column. The `+` operator can't apply to 3 arguments, so ClickHouse throws an exception with the relevant message.\n\nColumns that matched the `COLUMNS` expression can have different data types. If `COLUMNS` does not match any columns and is the only expression in `SELECT`, ClickHouse throws an exception.\n\n### Asterisk[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#asterisk \"Direct link to Asterisk\")\nYou can put an asterisk in any part of a query instead of an expression. When the query is analyzed, the asterisk is expanded to a list of all table columns (excluding the `MATERIALIZED` and `ALIAS` columns). There are only a few cases when using an asterisk is justified:\n\n- When creating a table dump.\n- For tables containing just a few columns, such as system tables.\n- For getting information about what columns are in a table. In this case, set `LIMIT 1`. But it is better to use the `DESC TABLE` query.\n- When there is strong filtration on a small number of columns using `PREWHERE`.\n- In subqueries (since columns that aren't needed for the external query are excluded from subqueries).\n\nIn all other cases, we do not recommend using the asterisk, since it only gives you the drawbacks of a columnar DBMS instead of the advantages. In other words using the asterisk is not recommended.\n\n### Extreme Values[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#extreme-values \"Direct link to Extreme Values\")\nIn addition to results, you can also get minimum and maximum values for the results columns. To do this, set the **extremes** setting to 1. Minimums and maximums are calculated for numeric types, dates, and dates with times. For other columns, the default values are output.\n\nAn extra two rows are calculated – the minimums and maximums, respectively. These extra two rows are output in `XML`, `JSON*`, `TabSeparated*`, `CSV*`, `Vertical`, `Template` and `Pretty*` [formats](https:\/\/clickhouse.com\/docs\/interfaces\/formats), separate from the other rows. They are not output for other formats.\n\nIn `JSON*` and `XML` formats, the extreme values are output in a separate 'extremes' field. In `TabSeparated*`, `CSV*` and `Vertical` formats, the row comes after the main result, and after 'totals' if present. It is preceded by an empty row (after the other data). In `Pretty*` formats, the row is output as a separate table after the main result, and after `totals` if present. In `Template` format the extreme values are output according to specified template.\n\nExtreme values are calculated for rows before `LIMIT`, but after `LIMIT BY`. However, when using `LIMIT offset, size`, the rows before `offset` are included in `extremes`. In stream requests, the result may also include a small number of rows that passed through `LIMIT`.\n\n### Notes[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#notes \"Direct link to Notes\")\nYou can use synonyms (`AS` aliases) in any part of a query.\n\nThe `GROUP BY`, `ORDER BY`, and `LIMIT BY` clauses can support positional arguments. To enable this, switch on the [enable\\_positional\\_arguments](https:\/\/clickhouse.com\/docs\/operations\/settings\/settings#enable_positional_arguments) setting. Then, for example, `ORDER BY 1,2` will be sorting rows in the table on the first and then the second column.\n\n## Implementation Details[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#implementation-details \"Direct link to Implementation Details\")\nIf the query omits the `DISTINCT`, `GROUP BY` and `ORDER BY` clauses and the `IN` and `JOIN` subqueries, the query will be completely stream processed, using O(1) amount of RAM. Otherwise, the query might consume a lot of RAM if the appropriate restrictions are not specified:\n\n- `max_memory_usage`\n- `max_rows_to_group_by`\n- `max_rows_to_sort`\n- `max_rows_in_distinct`\n- `max_bytes_in_distinct`\n- `max_rows_in_set`\n- `max_bytes_in_set`\n- `max_rows_in_join`\n- `max_bytes_in_join`\n- `max_bytes_before_external_sort`\n- `max_bytes_ratio_before_external_sort`\n- `max_bytes_before_external_group_by`\n- `max_bytes_ratio_before_external_group_by`\n\nFor more information, see the section \"Settings\". It is possible to use external sorting (saving temporary tables to a disk) and external aggregation.\n\n## SELECT modifiers[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#select-modifiers \"Direct link to SELECT modifiers\")\nYou can use the following modifiers in `SELECT` queries.\n\n| Modifier | Description |\n|---|---|\n| [`APPLY`](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/apply-modifier) | Allows you to invoke some function for each row returned by an outer table expression of a query. |\n| [`EXCEPT`](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/except-modifier) | Specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output. |\n| [`REPLACE`](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/replace-modifier) | Specifies one or more [expression aliases](https:\/\/clickhouse.com\/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. |\n### Modifier Combinations[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#modifier-combinations \"Direct link to Modifier Combinations\")\nYou can use each modifier separately or combine them.\n\n**Examples:**\n\nUsing the same modifier multiple times.\n```\nSELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) FROM columns_transformers;\n```\n```\n┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐\n│                        2 │                        3 │\n└──────────────────────────┴──────────────────────────┘\n```\nUsing multiple modifiers in a single query.\n```\nSELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers;\n```\n```\n┌─sum(plus(i, 1))─┬─sum(k)─┐\n│             222 │    347 │\n└─────────────────┴────────┘\n```\n## SETTINGS in SELECT Query[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#settings-in-select-query \"Direct link to SETTINGS in SELECT Query\")\nYou can specify the necessary settings right in the `SELECT` query. The setting value is applied only to this query and is reset to default or previous value after the query is executed.\n\nOther ways to make settings see [here](https:\/\/clickhouse.com\/docs\/operations\/settings\/overview).\n\nFor 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).\n\n**Example**\n```\nSELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1;\n```\n\n[Previous List of statements](https:\/\/clickhouse.com\/docs\/sql-reference\/statements)\n\n[Next ALL](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/all)\n\n- [Syntax](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#syntax)\n- [SELECT Clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#select-clause)\n  - [Dynamic column selection](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#dynamic-column-selection)\n  - [Asterisk](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#asterisk)\n  - [Extreme Values](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#extreme-values)\n  - [Notes](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#notes)\n- [Implementation Details](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#implementation-details)\n- [SELECT modifiers](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#select-modifiers)\n  - [Modifier Combinations](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#modifier-combinations)\n- [SETTINGS in SELECT Query](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#settings-in-select-query)\n\nWas this page helpful?\n\n###### Try ClickHouse Cloud for FREE\nSeparation of storage and compute, automatic scaling, built-in SQL console, and lots more. \\$300 in free credits when signing up.\n\n[Try it for Free](https:\/\/console.clickhouse.cloud\/signUp?loc=doc-card-banner&glxid=9b44e39d-c50a-4388-8882-41a3391300c1&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&utm_ga=GA1.1.13172778.1776121465)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\nEN\n\n- Get started▼\n- Cloud▼\n- Manage data▼\n- Server admin▼\n- Reference▼\n- Integrations▼\n- ClickStack▼\n- chDB▼\n- About▼\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\nEN\n\nmain-menu\n\n- Introduction▼\n  - [Syntax](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax)\n  - [Input and Output Formats](https:\/\/clickhouse.com\/docs\/sql-reference\/formats)\n  - Data types▼\n  - Statements▼\n  - Operators▼\n- Engines▼\n- Functions▼\n- Formats▼\n- [Data Lakes](https:\/\/clickhouse.com\/docs\/sql-reference\/datalakes)","attrs_readable_markdown":"`SELECT` queries perform data retrieval. By default, the requested data is returned to the client, while in conjunction with [INSERT INTO](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/insert-into) it can be forwarded to a different table.\n\n## Syntax[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#syntax \"Direct link to Syntax\")\n```\n[WITH expr_list(subquery)]\nSELECT [DISTINCT [ON (column1, column2, ...)]] expr_list\n[FROM [db.]table | (subquery) | table_function] [FINAL]\n[SAMPLE sample_coeff]\n[ARRAY JOIN ...]\n[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table [(alias1 [, alias2 ...])] (ON <expr_list>)|(USING <column_list>)\n[PREWHERE expr]\n[WHERE expr]\n[GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]\n[HAVING expr]\n[WINDOW window_expr_list]\n[QUALIFY expr]\n[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr] [INTERPOLATE [(expr_list)]]\n[LIMIT [offset_value, ]n BY columns]\n[LIMIT [n, ]m] [WITH TIES]\n[SETTINGS ...]\n[UNION  ...]\n[INTO OUTFILE filename [TRUNCATE] [COMPRESSION type [LEVEL level]] ]\n[FORMAT format]\n```\nAll clauses are optional, except for the required list of expressions immediately after `SELECT` which is covered in more detail [below](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#select-clause).\n\nSpecifics of each optional clause are covered in separate sections, which are listed in the same order as they are executed:\n\n- [WITH clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/with)\n- [SELECT clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#select-clause)\n- [DISTINCT clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/distinct)\n- [FROM clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/from)\n- [SAMPLE clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/sample)\n- [JOIN clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/join)\n- [PREWHERE clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/prewhere)\n- [WHERE clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/where)\n- [WINDOW clause](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n- [GROUP BY clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/group-by)\n- [LIMIT BY clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n- [HAVING clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/having)\n- [QUALIFY clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/qualify)\n- [LIMIT clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit)\n- [OFFSET clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/offset)\n- [UNION clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/union)\n- [INTERSECT clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/intersect)\n- [EXCEPT clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/except)\n- [INTO OUTFILE clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/into-outfile)\n- [FORMAT clause](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/format)\n## SELECT Clause[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#select-clause \"Direct link to SELECT Clause\")\n[Expressions](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax#expressions) specified in the `SELECT` clause are calculated after all the operations in the clauses described above are finished. These expressions work as if they apply to separate rows in the result. If expressions in the `SELECT` clause contain aggregate functions, then ClickHouse processes aggregate functions and expressions used as their arguments during the [GROUP BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/group-by) aggregation.\n\nIf you want to include all columns in the result, use the asterisk (`*`) symbol. For example, `SELECT * FROM ...`.\n\n### Dynamic column selection[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#dynamic-column-selection \"Direct link to Dynamic column selection\")\nDynamic column selection (also known as a COLUMNS expression) allows you to match some columns in a result with a [re2](https:\/\/en.wikipedia.org\/wiki\/RE2_\$software\$) regular expression.\n```\nCOLUMNS('regexp')\n```\nFor example, consider the table:\n```\nCREATE TABLE default.col_names (aa Int8, ab Int8, bc Int8) ENGINE = TinyLog\n```\nThe following query selects data from all the columns containing the `a` symbol in their name.\n```\nSELECT COLUMNS('a') FROM col_names\n```\n```\n┌─aa─┬─ab─┐\n│  1 │  1 │\n└────┴────┘\n```\nThe selected columns are returned not in the alphabetical order.\n\nYou can use multiple `COLUMNS` expressions in a query and apply functions to them.\n\nFor example:\n```\nSELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM col_names\n```\n```\n┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐\n│  1 │  1 │  1 │ Int8           │\n└────┴────┴────┴────────────────┘\n```\nEach column returned by the `COLUMNS` expression is passed to the function as a separate argument. Also you can pass other arguments to the function if it supports them. Be careful when using functions. If a function does not support the number of arguments you have passed to it, ClickHouse throws an exception.\n\nFor example:\n```\nSELECT COLUMNS('a') + COLUMNS('c') FROM col_names\n```\n```\nReceived exception from server (version 19.14.1):\nCode: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus does not match: passed 3, should be 2.\n```\nIn this example, `COLUMNS('a')` returns two columns: `aa` and `ab`. `COLUMNS('c')` returns the `bc` column. The `+` operator can't apply to 3 arguments, so ClickHouse throws an exception with the relevant message.\n\nColumns that matched the `COLUMNS` expression can have different data types. If `COLUMNS` does not match any columns and is the only expression in `SELECT`, ClickHouse throws an exception.\n\n### Asterisk[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#asterisk \"Direct link to Asterisk\")\nYou can put an asterisk in any part of a query instead of an expression. When the query is analyzed, the asterisk is expanded to a list of all table columns (excluding the `MATERIALIZED` and `ALIAS` columns). There are only a few cases when using an asterisk is justified:\n\n- When creating a table dump.\n- For tables containing just a few columns, such as system tables.\n- For getting information about what columns are in a table. In this case, set `LIMIT 1`. But it is better to use the `DESC TABLE` query.\n- When there is strong filtration on a small number of columns using `PREWHERE`.\n- In subqueries (since columns that aren't needed for the external query are excluded from subqueries).\n\nIn all other cases, we do not recommend using the asterisk, since it only gives you the drawbacks of a columnar DBMS instead of the advantages. In other words using the asterisk is not recommended.\n\n### Extreme Values[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#extreme-values \"Direct link to Extreme Values\")\nIn addition to results, you can also get minimum and maximum values for the results columns. To do this, set the **extremes** setting to 1. Minimums and maximums are calculated for numeric types, dates, and dates with times. For other columns, the default values are output.\n\nAn extra two rows are calculated – the minimums and maximums, respectively. These extra two rows are output in `XML`, `JSON*`, `TabSeparated*`, `CSV*`, `Vertical`, `Template` and `Pretty*` [formats](https:\/\/clickhouse.com\/docs\/interfaces\/formats), separate from the other rows. They are not output for other formats.\n\nIn `JSON*` and `XML` formats, the extreme values are output in a separate 'extremes' field. In `TabSeparated*`, `CSV*` and `Vertical` formats, the row comes after the main result, and after 'totals' if present. It is preceded by an empty row (after the other data). In `Pretty*` formats, the row is output as a separate table after the main result, and after `totals` if present. In `Template` format the extreme values are output according to specified template.\n\nExtreme values are calculated for rows before `LIMIT`, but after `LIMIT BY`. However, when using `LIMIT offset, size`, the rows before `offset` are included in `extremes`. In stream requests, the result may also include a small number of rows that passed through `LIMIT`.\n\n### Notes[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#notes \"Direct link to Notes\")\nYou can use synonyms (`AS` aliases) in any part of a query.\n\nThe `GROUP BY`, `ORDER BY`, and `LIMIT BY` clauses can support positional arguments. To enable this, switch on the [enable\\_positional\\_arguments](https:\/\/clickhouse.com\/docs\/operations\/settings\/settings#enable_positional_arguments) setting. Then, for example, `ORDER BY 1,2` will be sorting rows in the table on the first and then the second column.\n\n## Implementation Details[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#implementation-details \"Direct link to Implementation Details\")\nIf the query omits the `DISTINCT`, `GROUP BY` and `ORDER BY` clauses and the `IN` and `JOIN` subqueries, the query will be completely stream processed, using O(1) amount of RAM. Otherwise, the query might consume a lot of RAM if the appropriate restrictions are not specified:\n\n- `max_memory_usage`\n- `max_rows_to_group_by`\n- `max_rows_to_sort`\n- `max_rows_in_distinct`\n- `max_bytes_in_distinct`\n- `max_rows_in_set`\n- `max_bytes_in_set`\n- `max_rows_in_join`\n- `max_bytes_in_join`\n- `max_bytes_before_external_sort`\n- `max_bytes_ratio_before_external_sort`\n- `max_bytes_before_external_group_by`\n- `max_bytes_ratio_before_external_group_by`\n\nFor more information, see the section \"Settings\". It is possible to use external sorting (saving temporary tables to a disk) and external aggregation.\n\n## SELECT modifiers[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#select-modifiers \"Direct link to SELECT modifiers\")\nYou can use the following modifiers in `SELECT` queries.\n\n| Modifier | Description |\n|---|---|\n| [`APPLY`](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/apply-modifier) | Allows you to invoke some function for each row returned by an outer table expression of a query. |\n| [`EXCEPT`](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/except-modifier) | Specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output. |\n| [`REPLACE`](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/replace-modifier) | Specifies one or more [expression aliases](https:\/\/clickhouse.com\/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. |\n### Modifier Combinations[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#modifier-combinations \"Direct link to Modifier Combinations\")\nYou can use each modifier separately or combine them.\n\n**Examples:**\n\nUsing the same modifier multiple times.\n```\nSELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) FROM columns_transformers;\n```\n```\n┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐\n│                        2 │                        3 │\n└──────────────────────────┴──────────────────────────┘\n```\nUsing multiple modifiers in a single query.\n```\nSELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers;\n```\n```\n┌─sum(plus(i, 1))─┬─sum(k)─┐\n│             222 │    347 │\n└─────────────────┴────────┘\n```\n## SETTINGS in SELECT Query[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select#settings-in-select-query \"Direct link to SETTINGS in SELECT Query\")\nYou can specify the necessary settings right in the `SELECT` query. The setting value is applied only to this query and is reset to default or previous value after the query is executed.\n\nOther ways to make settings see [here](https:\/\/clickhouse.com\/docs\/operations\/settings\/overview).\n\nFor 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).\n\n**Example**\n```\nSELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1;\n```","meta_canonical":null}

3. Robots.txt Check

Query:

Response:

4. Spam/Ban Check

Query:

Response:

5. Seen Status Check

ℹ️ Skipped - page is already crawled

📄

INDEXABLE

✅

CRAWLED

3 days ago

🤖

ROBOTS ALLOWED

Page Info Filters

Filter	Status	Condition	Details
HTTP status	PASS	`download_http_code = 200`	HTTP 200
Age cutoff	PASS	`download_stamp > now() - 6 MONTH`	0.1 months ago
History drop	PASS	`isNull(history_drop_reason)`	No drop reason
Spam/ban	PASS	`fh_dont_index != 1 AND ml_spam_score = 0`	ml_spam_score=0
Canonical	PASS	`meta_canonical IS NULL OR = '' OR = src_unparsed`	Not set

Page Details

Property	Value
URL	https://clickhouse.com/docs/sql-reference/statements/select
Last Crawled	2026-04-13 23:04:23 (3 days ago)
First Indexed	2025-02-21 00:41:22 (1 year ago)
HTTP Status Code	200
Meta Title	SELECT Query \| ClickHouse Docs
Meta Description	Documentation for SELECT Query
Meta Canonical	null
Boilerpipe Text	SELECT queries perform data retrieval. By default, the requested data is returned to the client, while in conjunction with INSERT INTO it can be forwarded to a different table. Syntax [ WITH expr_list ( subquery ) ] SELECT [ DISTINCT [ ON ( column1 , column2 , . . . ) ] ] expr_list [ FROM [ db . ] table \| ( subquery ) \| table_function ] [ FINAL ] [ SAMPLE sample_coeff ] [ ARRAY JOIN . . . ] [ GLOBAL ] [ ANY \| ALL \| ASOF ] [ INNER \| LEFT \| RIGHT \| FULL \| CROSS ] [ OUTER \| SEMI \| ANTI ] JOIN ( subquery ) \| table [ ( alias1 [ , alias2 . . . ] ) ] ( ON < expr_list > ) \| ( USING < column_list > ) [ PREWHERE expr ] [ WHERE expr ] [ GROUP BY expr_list ] [ WITH ROLLUP \| WITH CUBE ] [ WITH TOTALS ] [ HAVING expr ] [ WINDOW window_expr_list ] [ QUALIFY expr ] [ ORDER BY expr_list ] [ WITH FILL ] [ FROM expr ] [ TO expr ] [ STEP expr ] [ INTERPOLATE [ ( expr_list ) ] ] [ LIMIT [ offset_value , ] n BY columns ] [ LIMIT [ n , ] m ] [ WITH TIES ] [ SETTINGS . . . ] [ UNION . . . ] [ INTO OUTFILE filename [ TRUNCATE ] [ COMPRESSION type [ LEVEL level ] ] ] [ FORMAT format ] All clauses are optional, except for the required list of expressions immediately after SELECT which is covered in more detail below . Specifics of each optional clause are covered in separate sections, which are listed in the same order as they are executed: WITH clause SELECT clause DISTINCT clause FROM clause SAMPLE clause JOIN clause PREWHERE clause WHERE clause WINDOW clause GROUP BY clause LIMIT BY clause HAVING clause QUALIFY clause LIMIT clause OFFSET clause UNION clause INTERSECT clause EXCEPT clause INTO OUTFILE clause FORMAT clause SELECT Clause Expressions specified in the SELECT clause are calculated after all the operations in the clauses described above are finished. These expressions work as if they apply to separate rows in the result. If expressions in the SELECT clause contain aggregate functions, then ClickHouse processes aggregate functions and expressions used as their arguments during the GROUP BY aggregation. If you want to include all columns in the result, use the asterisk ( * ) symbol. For example, SELECT * FROM ... . Dynamic column selection Dynamic column selection (also known as a COLUMNS expression) allows you to match some columns in a result with a re2 regular expression. COLUMNS ( 'regexp' ) For example, consider the table: CREATE TABLE default . col_names ( aa Int8 , ab Int8 , bc Int8 ) ENGINE = TinyLog The following query selects data from all the columns containing the a symbol in their name. SELECT COLUMNS ( 'a' ) FROM col_names ┌─aa─┬─ab─┐ │ 1 │ 1 │ └────┴────┘ The selected columns are returned not in the alphabetical order. You can use multiple COLUMNS expressions in a query and apply functions to them. For example: SELECT COLUMNS ( 'a' ) , COLUMNS ( 'c' ) , toTypeName ( COLUMNS ( 'c' ) ) FROM col_names ┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐ │ 1 │ 1 │ 1 │ Int8 │ └────┴────┴────┴────────────────┘ Each column returned by the COLUMNS expression is passed to the function as a separate argument. Also you can pass other arguments to the function if it supports them. Be careful when using functions. If a function does not support the number of arguments you have passed to it, ClickHouse throws an exception. For example: SELECT COLUMNS ( 'a' ) + COLUMNS ( 'c' ) FROM col_names Received exception from server (version 19.14.1): Code: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus does not match: passed 3, should be 2. In this example, COLUMNS('a') returns two columns: aa and ab . COLUMNS('c') returns the bc column. The + operator can't apply to 3 arguments, so ClickHouse throws an exception with the relevant message. Columns that matched the COLUMNS expression can have different data types. If COLUMNS does not match any columns and is the only expression in SELECT , ClickHouse throws an exception. Asterisk You can put an asterisk in any part of a query instead of an expression. When the query is analyzed, the asterisk is expanded to a list of all table columns (excluding the MATERIALIZED and ALIAS columns). There are only a few cases when using an asterisk is justified: When creating a table dump. For tables containing just a few columns, such as system tables. For getting information about what columns are in a table. In this case, set LIMIT 1 . But it is better to use the DESC TABLE query. When there is strong filtration on a small number of columns using PREWHERE . In subqueries (since columns that aren't needed for the external query are excluded from subqueries). In all other cases, we do not recommend using the asterisk, since it only gives you the drawbacks of a columnar DBMS instead of the advantages. In other words using the asterisk is not recommended. Extreme Values In addition to results, you can also get minimum and maximum values for the results columns. To do this, set the extremes setting to 1. Minimums and maximums are calculated for numeric types, dates, and dates with times. For other columns, the default values are output. An extra two rows are calculated – the minimums and maximums, respectively. These extra two rows are output in XML , JSON* , TabSeparated* , CSV* , Vertical , Template and Pretty* formats , separate from the other rows. They are not output for other formats. In JSON* and XML formats, the extreme values are output in a separate 'extremes' field. In TabSeparated* , CSV* and Vertical formats, the row comes after the main result, and after 'totals' if present. It is preceded by an empty row (after the other data). In Pretty* formats, the row is output as a separate table after the main result, and after totals if present. In Template format the extreme values are output according to specified template. Extreme values are calculated for rows before LIMIT , but after LIMIT BY . However, when using LIMIT offset, size , the rows before offset are included in extremes . In stream requests, the result may also include a small number of rows that passed through LIMIT . Notes You can use synonyms ( AS aliases) in any part of a query. The GROUP BY , ORDER BY , and LIMIT BY clauses can support positional arguments. To enable this, switch on the enable_positional_arguments setting. Then, for example, ORDER BY 1,2 will be sorting rows in the table on the first and then the second column. Implementation Details If the query omits the DISTINCT , GROUP BY and ORDER BY clauses and the IN and JOIN subqueries, the query will be completely stream processed, using O(1) amount of RAM. Otherwise, the query might consume a lot of RAM if the appropriate restrictions are not specified: max_memory_usage max_rows_to_group_by max_rows_to_sort max_rows_in_distinct max_bytes_in_distinct max_rows_in_set max_bytes_in_set max_rows_in_join max_bytes_in_join max_bytes_before_external_sort max_bytes_ratio_before_external_sort max_bytes_before_external_group_by max_bytes_ratio_before_external_group_by For more information, see the section "Settings". It is possible to use external sorting (saving temporary tables to a disk) and external aggregation. SELECT modifiers You can use the following modifiers in SELECT queries. Modifier Description APPLY Allows you to invoke some function for each row returned by an outer table expression of a query. EXCEPT Specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output. REPLACE Specifies one or more 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. Modifier Combinations You can use each modifier separately or combine them. Examples: Using the same modifier multiple times. SELECT COLUMNS ( '[jk]' ) APPLY ( toString ) APPLY ( length ) APPLY ( max ) FROM columns_transformers ; ┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐ │ 2 │ 3 │ └──────────────────────────┴──────────────────────────┘ Using multiple modifiers in a single query. SELECT * REPLACE ( i + 1 AS i ) EXCEPT ( j ) APPLY ( sum ) from columns_transformers ; ┌─sum(plus(i, 1))─┬─sum(k)─┐ │ 222 │ 347 │ └─────────────────┴────────┘ SETTINGS in SELECT Query You can specify the necessary settings right in the SELECT query. The setting value is applied only to this query and is reset to default or previous value after the query is executed. Other ways to make settings see here . 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). Example SELECT * FROM some_table SETTINGS optimize_read_in_order = 1 , cast_keep_nullable = 1 ;
Markdown	[Skip to main content](https://clickhouse.com/docs/sql-reference/statements/select#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/sql-reference/statements/select) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/sql-reference/statements/select) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/sql-reference/statements/select) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/sql-reference/statements/select) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.9k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=9b44e39d-c50a-4388-8882-41a3391300c1&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&utm_ga=GA1.1.13172778.1776121465) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=9b44e39d-c50a-4388-8882-41a3391300c1&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&utm_ga=GA1.1.13172778.1776121465) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/sql-reference/statements/select) - [English](https://clickhouse.com/docs/sql-reference/statements/select) - [日本語](https://clickhouse.com/docs/jp/sql-reference/statements/select) - [中文](https://clickhouse.com/docs/zh/sql-reference/statements/select) - [Русский](https://clickhouse.com/docs/ru/sql-reference/statements/select) - [한국어](https://clickhouse.com/docs/ko/sql-reference/statements/select) [Skip to main content](https://clickhouse.com/docs/sql-reference/statements/select#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/sql-reference/statements/select) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/sql-reference/statements/select) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/sql-reference/statements/select) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/sql-reference/statements/select) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.9k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=9b44e39d-c50a-4388-8882-41a3391300c1&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&utm_ga=GA1.1.13172778.1776121465) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=9b44e39d-c50a-4388-8882-41a3391300c1&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&utm_ga=GA1.1.13172778.1776121465) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/sql-reference/statements/select) - [English](https://clickhouse.com/docs/sql-reference/statements/select) - [日本語](https://clickhouse.com/docs/jp/sql-reference/statements/select) - [中文](https://clickhouse.com/docs/zh/sql-reference/statements/select) - [Русский](https://clickhouse.com/docs/ru/sql-reference/statements/select) - [한국어](https://clickhouse.com/docs/ko/sql-reference/statements/select) [Search`Ctrl``K`](https://clickhouse.com/docs/search) - [Introduction](https://clickhouse.com/docs/sql-reference) - [Syntax](https://clickhouse.com/docs/sql-reference/syntax) - [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats) - [Data types](https://clickhouse.com/docs/sql-reference/data-types) - [Statements](https://clickhouse.com/docs/sql-reference/statements) - [SELECT](https://clickhouse.com/docs/sql-reference/statements/select) - [ALL](https://clickhouse.com/docs/sql-reference/statements/select/all) - [APPLY](https://clickhouse.com/docs/sql-reference/statements/select/apply-modifier) - [ARRAY JOIN](https://clickhouse.com/docs/sql-reference/statements/select/array-join) - [DISTINCT](https://clickhouse.com/docs/sql-reference/statements/select/distinct) - [EXCEPT](https://clickhouse.com/docs/sql-reference/statements/select/except) - [EXCEPT](https://clickhouse.com/docs/sql-reference/statements/select/except-modifier) - [FORMAT](https://clickhouse.com/docs/sql-reference/statements/select/format) - [FROM](https://clickhouse.com/docs/sql-reference/statements/select/from) - [GROUP BY](https://clickhouse.com/docs/sql-reference/statements/select/group-by) - [HAVING](https://clickhouse.com/docs/sql-reference/statements/select/having) - [INTERSECT](https://clickhouse.com/docs/sql-reference/statements/select/intersect) - [INTO OUTFILE](https://clickhouse.com/docs/sql-reference/statements/select/into-outfile) - [JOIN](https://clickhouse.com/docs/sql-reference/statements/select/join) - [LIMIT BY](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [LIMIT](https://clickhouse.com/docs/sql-reference/statements/select/limit) - [OFFSET](https://clickhouse.com/docs/sql-reference/statements/select/offset) - [ORDER BY](https://clickhouse.com/docs/sql-reference/statements/select/order-by) - [PREWHERE](https://clickhouse.com/docs/sql-reference/statements/select/prewhere) - [QUALIFY](https://clickhouse.com/docs/sql-reference/statements/select/qualify) - [REPLACE](https://clickhouse.com/docs/sql-reference/statements/select/replace-modifier) - [SAMPLE](https://clickhouse.com/docs/sql-reference/statements/select/sample) - [UNION](https://clickhouse.com/docs/sql-reference/statements/select/union) - [WHERE](https://clickhouse.com/docs/sql-reference/statements/select/where) - [WITH](https://clickhouse.com/docs/sql-reference/statements/select/with) - [INSERT INTO](https://clickhouse.com/docs/sql-reference/statements/insert-into) - [CREATE](https://clickhouse.com/docs/sql-reference/statements/create) - [ALTER](https://clickhouse.com/docs/sql-reference/statements/alter) - [DELETE](https://clickhouse.com/docs/sql-reference/statements/delete) - [SYSTEM](https://clickhouse.com/docs/sql-reference/statements/system) - [SHOW](https://clickhouse.com/docs/sql-reference/statements/show) - [GRANT](https://clickhouse.com/docs/sql-reference/statements/grant) - [EXPLAIN](https://clickhouse.com/docs/sql-reference/statements/explain) - [REVOKE](https://clickhouse.com/docs/sql-reference/statements/revoke) - [UPDATE](https://clickhouse.com/docs/sql-reference/statements/update) - [ATTACH](https://clickhouse.com/docs/sql-reference/statements/attach) - [CHECK TABLE](https://clickhouse.com/docs/sql-reference/statements/check-table) - [DESCRIBE TABLE](https://clickhouse.com/docs/sql-reference/statements/describe-table) - [DETACH](https://clickhouse.com/docs/sql-reference/statements/detach) - [DROP](https://clickhouse.com/docs/sql-reference/statements/drop) - [EXISTS](https://clickhouse.com/docs/sql-reference/statements/exists) - [KILL](https://clickhouse.com/docs/sql-reference/statements/kill) - [OPTIMIZE](https://clickhouse.com/docs/sql-reference/statements/optimize) - [RENAME](https://clickhouse.com/docs/sql-reference/statements/rename) - [EXCHANGE](https://clickhouse.com/docs/sql-reference/statements/exchange) - [SET](https://clickhouse.com/docs/sql-reference/statements/set) - [SET ROLE](https://clickhouse.com/docs/sql-reference/statements/set-role) - [TRUNCATE](https://clickhouse.com/docs/sql-reference/statements/truncate) - [EXECUTE AS](https://clickhouse.com/docs/sql-reference/statements/execute_as) - [PARALLEL WITH](https://clickhouse.com/docs/sql-reference/statements/parallel_with) - [USE](https://clickhouse.com/docs/sql-reference/statements/use) - [WATCH](https://clickhouse.com/docs/sql-reference/statements/watch) - [MOVE](https://clickhouse.com/docs/sql-reference/statements/move) - [CHECK GRANT](https://clickhouse.com/docs/sql-reference/statements/check-grant) - [UNDROP](https://clickhouse.com/docs/sql-reference/statements/undrop) - [Operators](https://clickhouse.com/docs/sql-reference/operators) - [Engines](https://clickhouse.com/docs/engines) - [Database Engines](https://clickhouse.com/docs/engines/database-engines) - [Table Engines](https://clickhouse.com/docs/engines/table-engines) - [Functions](https://clickhouse.com/docs/sql-reference/functions) - [Regular functions](https://clickhouse.com/docs/sql-reference/functions/regular-functions) - [Aggregate functions](https://clickhouse.com/docs/sql-reference/aggregate-functions) - [Table functions](https://clickhouse.com/docs/sql-reference/table-functions) - [Window functions](https://clickhouse.com/docs/sql-reference/window-functions) - [Formats](https://clickhouse.com/docs/interfaces/formats) - [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes) - [Introduction](https://clickhouse.com/docs/sql-reference) - [Statements](https://clickhouse.com/docs/sql-reference/statements) - SELECT [Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/index.md) # SELECT Query `SELECT` queries perform data retrieval. By default, the requested data is returned to the client, while in conjunction with [INSERT INTO](https://clickhouse.com/docs/sql-reference/statements/insert-into) it can be forwarded to a different table. ## Syntax[](https://clickhouse.com/docs/sql-reference/statements/select#syntax "Direct link to Syntax") ``` [WITH expr_list(subquery)] SELECT [DISTINCT [ON (column1, column2, ...)]] expr_list [FROM [db.]table \| (subquery) \| table_function] [FINAL] [SAMPLE sample_coeff] [ARRAY JOIN ...] [GLOBAL] [ANY\|ALL\|ASOF] [INNER\|LEFT\|RIGHT\|FULL\|CROSS] [OUTER\|SEMI\|ANTI] JOIN (subquery)\|table [(alias1 [, alias2 ...])] (ON <expr_list>)\|(USING <column_list>) [PREWHERE expr] [WHERE expr] [GROUP BY expr_list] [WITH ROLLUP\|WITH CUBE] [WITH TOTALS] [HAVING expr] [WINDOW window_expr_list] [QUALIFY expr] [ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr] [INTERPOLATE [(expr_list)]] [LIMIT [offset_value, ]n BY columns] [LIMIT [n, ]m] [WITH TIES] [SETTINGS ...] [UNION ...] [INTO OUTFILE filename [TRUNCATE] [COMPRESSION type [LEVEL level]] ] [FORMAT format] ``` All clauses are optional, except for the required list of expressions immediately after `SELECT` which is covered in more detail [below](https://clickhouse.com/docs/sql-reference/statements/select#select-clause). Specifics of each optional clause are covered in separate sections, which are listed in the same order as they are executed: - [WITH clause](https://clickhouse.com/docs/sql-reference/statements/select/with) - [SELECT clause](https://clickhouse.com/docs/sql-reference/statements/select#select-clause) - [DISTINCT clause](https://clickhouse.com/docs/sql-reference/statements/select/distinct) - [FROM clause](https://clickhouse.com/docs/sql-reference/statements/select/from) - [SAMPLE clause](https://clickhouse.com/docs/sql-reference/statements/select/sample) - [JOIN clause](https://clickhouse.com/docs/sql-reference/statements/select/join) - [PREWHERE clause](https://clickhouse.com/docs/sql-reference/statements/select/prewhere) - [WHERE clause](https://clickhouse.com/docs/sql-reference/statements/select/where) - [WINDOW clause](https://clickhouse.com/docs/sql-reference/window-functions) - [GROUP BY clause](https://clickhouse.com/docs/sql-reference/statements/select/group-by) - [LIMIT BY clause](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [HAVING clause](https://clickhouse.com/docs/sql-reference/statements/select/having) - [QUALIFY clause](https://clickhouse.com/docs/sql-reference/statements/select/qualify) - [LIMIT clause](https://clickhouse.com/docs/sql-reference/statements/select/limit) - [OFFSET clause](https://clickhouse.com/docs/sql-reference/statements/select/offset) - [UNION clause](https://clickhouse.com/docs/sql-reference/statements/select/union) - [INTERSECT clause](https://clickhouse.com/docs/sql-reference/statements/select/intersect) - [EXCEPT clause](https://clickhouse.com/docs/sql-reference/statements/select/except) - [INTO OUTFILE clause](https://clickhouse.com/docs/sql-reference/statements/select/into-outfile) - [FORMAT clause](https://clickhouse.com/docs/sql-reference/statements/select/format) ## SELECT Clause[](https://clickhouse.com/docs/sql-reference/statements/select#select-clause "Direct link to SELECT Clause") [Expressions](https://clickhouse.com/docs/sql-reference/syntax#expressions) specified in the `SELECT` clause are calculated after all the operations in the clauses described above are finished. These expressions work as if they apply to separate rows in the result. If expressions in the `SELECT` clause contain aggregate functions, then ClickHouse processes aggregate functions and expressions used as their arguments during the [GROUP BY](https://clickhouse.com/docs/sql-reference/statements/select/group-by) aggregation. If you want to include all columns in the result, use the asterisk (``) symbol. For example, `SELECT FROM ...`. ### Dynamic column selection[](https://clickhouse.com/docs/sql-reference/statements/select#dynamic-column-selection "Direct link to Dynamic column selection") Dynamic column selection (also known as a COLUMNS expression) allows you to match some columns in a result with a [re2](https://en.wikipedia.org/wiki/RE2_$software$) regular expression. ``` COLUMNS('regexp') ``` For example, consider the table: ``` CREATE TABLE default.col_names (aa Int8, ab Int8, bc Int8) ENGINE = TinyLog ``` The following query selects data from all the columns containing the `a` symbol in their name. ``` SELECT COLUMNS('a') FROM col_names ``` ``` ┌─aa─┬─ab─┐ │ 1 │ 1 │ └────┴────┘ ``` The selected columns are returned not in the alphabetical order. You can use multiple `COLUMNS` expressions in a query and apply functions to them. For example: ``` SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM col_names ``` ``` ┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐ │ 1 │ 1 │ 1 │ Int8 │ └────┴────┴────┴────────────────┘ ``` Each column returned by the `COLUMNS` expression is passed to the function as a separate argument. Also you can pass other arguments to the function if it supports them. Be careful when using functions. If a function does not support the number of arguments you have passed to it, ClickHouse throws an exception. For example: ``` SELECT COLUMNS('a') + COLUMNS('c') FROM col_names ``` ``` Received exception from server (version 19.14.1): Code: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus does not match: passed 3, should be 2. ``` In this example, `COLUMNS('a')` returns two columns: `aa` and `ab`. `COLUMNS('c')` returns the `bc` column. The `+` operator can't apply to 3 arguments, so ClickHouse throws an exception with the relevant message. Columns that matched the `COLUMNS` expression can have different data types. If `COLUMNS` does not match any columns and is the only expression in `SELECT`, ClickHouse throws an exception. ### Asterisk[](https://clickhouse.com/docs/sql-reference/statements/select#asterisk "Direct link to Asterisk") You can put an asterisk in any part of a query instead of an expression. When the query is analyzed, the asterisk is expanded to a list of all table columns (excluding the `MATERIALIZED` and `ALIAS` columns). There are only a few cases when using an asterisk is justified: - When creating a table dump. - For tables containing just a few columns, such as system tables. - For getting information about what columns are in a table. In this case, set `LIMIT 1`. But it is better to use the `DESC TABLE` query. - When there is strong filtration on a small number of columns using `PREWHERE`. - In subqueries (since columns that aren't needed for the external query are excluded from subqueries). In all other cases, we do not recommend using the asterisk, since it only gives you the drawbacks of a columnar DBMS instead of the advantages. In other words using the asterisk is not recommended. ### Extreme Values[](https://clickhouse.com/docs/sql-reference/statements/select#extreme-values "Direct link to Extreme Values") In addition to results, you can also get minimum and maximum values for the results columns. To do this, set the extremes setting to 1. Minimums and maximums are calculated for numeric types, dates, and dates with times. For other columns, the default values are output. An extra two rows are calculated – the minimums and maximums, respectively. These extra two rows are output in `XML`, `JSON`, `TabSeparated`, `CSV`, `Vertical`, `Template` and `Pretty` [formats](https://clickhouse.com/docs/interfaces/formats), separate from the other rows. They are not output for other formats. In `JSON` and `XML` formats, the extreme values are output in a separate 'extremes' field. In `TabSeparated`, `CSV` and `Vertical` formats, the row comes after the main result, and after 'totals' if present. It is preceded by an empty row (after the other data). In `Pretty` formats, the row is output as a separate table after the main result, and after `totals` if present. In `Template` format the extreme values are output according to specified template. Extreme values are calculated for rows before `LIMIT`, but after `LIMIT BY`. However, when using `LIMIT offset, size`, the rows before `offset` are included in `extremes`. In stream requests, the result may also include a small number of rows that passed through `LIMIT`. ### Notes[](https://clickhouse.com/docs/sql-reference/statements/select#notes "Direct link to Notes") You can use synonyms (`AS` aliases) in any part of a query. The `GROUP BY`, `ORDER BY`, and `LIMIT BY` clauses can support positional arguments. To enable this, switch on the [enable\_positional\_arguments](https://clickhouse.com/docs/operations/settings/settings#enable_positional_arguments) setting. Then, for example, `ORDER BY 1,2` will be sorting rows in the table on the first and then the second column. ## Implementation Details[](https://clickhouse.com/docs/sql-reference/statements/select#implementation-details "Direct link to Implementation Details") If the query omits the `DISTINCT`, `GROUP BY` and `ORDER BY` clauses and the `IN` and `JOIN` subqueries, the query will be completely stream processed, using O(1) amount of RAM. Otherwise, the query might consume a lot of RAM if the appropriate restrictions are not specified: - `max_memory_usage` - `max_rows_to_group_by` - `max_rows_to_sort` - `max_rows_in_distinct` - `max_bytes_in_distinct` - `max_rows_in_set` - `max_bytes_in_set` - `max_rows_in_join` - `max_bytes_in_join` - `max_bytes_before_external_sort` - `max_bytes_ratio_before_external_sort` - `max_bytes_before_external_group_by` - `max_bytes_ratio_before_external_group_by` For more information, see the section "Settings". It is possible to use external sorting (saving temporary tables to a disk) and external aggregation. ## SELECT modifiers[](https://clickhouse.com/docs/sql-reference/statements/select#select-modifiers "Direct link to SELECT modifiers") You can use the following modifiers in `SELECT` queries. \| Modifier \| Description \| \|---\|---\| \| [`APPLY`](https://clickhouse.com/docs/sql-reference/statements/select/apply-modifier) \| Allows you to invoke some function for each row returned by an outer table expression of a query. \| \| [`EXCEPT`](https://clickhouse.com/docs/sql-reference/statements/select/except-modifier) \| Specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output. \| \| [`REPLACE`](https://clickhouse.com/docs/sql-reference/statements/select/replace-modifier) \| Specifies one or more [expression aliases](https://clickhouse.com/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. \| ### Modifier Combinations[](https://clickhouse.com/docs/sql-reference/statements/select#modifier-combinations "Direct link to Modifier Combinations") You can use each modifier separately or combine them. Examples:* Using the same modifier multiple times. ``` SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) FROM columns_transformers; ``` ``` ┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐ │ 2 │ 3 │ └──────────────────────────┴──────────────────────────┘ ``` Using multiple modifiers in a single query. ``` SELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers; ``` ``` ┌─sum(plus(i, 1))─┬─sum(k)─┐ │ 222 │ 347 │ └─────────────────┴────────┘ ``` ## SETTINGS in SELECT Query[](https://clickhouse.com/docs/sql-reference/statements/select#settings-in-select-query "Direct link to SETTINGS in SELECT Query") You can specify the necessary settings right in the `SELECT` query. The setting value is applied only to this query and is reset to default or previous value after the query is executed. Other ways to make settings see [here](https://clickhouse.com/docs/operations/settings/overview). 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). Example ``` SELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1; ``` [Previous List of statements](https://clickhouse.com/docs/sql-reference/statements) [Next ALL](https://clickhouse.com/docs/sql-reference/statements/select/all) - [Syntax](https://clickhouse.com/docs/sql-reference/statements/select#syntax) - [SELECT Clause](https://clickhouse.com/docs/sql-reference/statements/select#select-clause) - [Dynamic column selection](https://clickhouse.com/docs/sql-reference/statements/select#dynamic-column-selection) - [Asterisk](https://clickhouse.com/docs/sql-reference/statements/select#asterisk) - [Extreme Values](https://clickhouse.com/docs/sql-reference/statements/select#extreme-values) - [Notes](https://clickhouse.com/docs/sql-reference/statements/select#notes) - [Implementation Details](https://clickhouse.com/docs/sql-reference/statements/select#implementation-details) - [SELECT modifiers](https://clickhouse.com/docs/sql-reference/statements/select#select-modifiers) - [Modifier Combinations](https://clickhouse.com/docs/sql-reference/statements/select#modifier-combinations) - [SETTINGS in SELECT Query](https://clickhouse.com/docs/sql-reference/statements/select#settings-in-select-query) Was this page helpful? ###### Try ClickHouse Cloud for FREE Separation of storage and compute, automatic scaling, built-in SQL console, and lots more. \$300 in free credits when signing up. [Try it for Free](https://console.clickhouse.cloud/signUp?loc=doc-card-banner&glxid=9b44e39d-c50a-4388-8882-41a3391300c1&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect&utm_ga=GA1.1.13172778.1776121465) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) EN - Get started▼ - Cloud▼ - Manage data▼ - Server admin▼ - Reference▼ - Integrations▼ - ClickStack▼ - chDB▼ - About▼ [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) EN main-menu - Introduction▼ - [Syntax](https://clickhouse.com/docs/sql-reference/syntax) - [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats) - Data types▼ - Statements▼ - Operators▼ - Engines▼ - Functions▼ - Formats▼ - [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes)
Readable Markdown	`SELECT` queries perform data retrieval. By default, the requested data is returned to the client, while in conjunction with [INSERT INTO](https://clickhouse.com/docs/sql-reference/statements/insert-into) it can be forwarded to a different table. ## Syntax[](https://clickhouse.com/docs/sql-reference/statements/select#syntax "Direct link to Syntax") ``` [WITH expr_list(subquery)] SELECT [DISTINCT [ON (column1, column2, ...)]] expr_list [FROM [db.]table \| (subquery) \| table_function] [FINAL] [SAMPLE sample_coeff] [ARRAY JOIN ...] [GLOBAL] [ANY\|ALL\|ASOF] [INNER\|LEFT\|RIGHT\|FULL\|CROSS] [OUTER\|SEMI\|ANTI] JOIN (subquery)\|table [(alias1 [, alias2 ...])] (ON <expr_list>)\|(USING <column_list>) [PREWHERE expr] [WHERE expr] [GROUP BY expr_list] [WITH ROLLUP\|WITH CUBE] [WITH TOTALS] [HAVING expr] [WINDOW window_expr_list] [QUALIFY expr] [ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr] [INTERPOLATE [(expr_list)]] [LIMIT [offset_value, ]n BY columns] [LIMIT [n, ]m] [WITH TIES] [SETTINGS ...] [UNION ...] [INTO OUTFILE filename [TRUNCATE] [COMPRESSION type [LEVEL level]] ] [FORMAT format] ``` All clauses are optional, except for the required list of expressions immediately after `SELECT` which is covered in more detail [below](https://clickhouse.com/docs/sql-reference/statements/select#select-clause). Specifics of each optional clause are covered in separate sections, which are listed in the same order as they are executed: - [WITH clause](https://clickhouse.com/docs/sql-reference/statements/select/with) - [SELECT clause](https://clickhouse.com/docs/sql-reference/statements/select#select-clause) - [DISTINCT clause](https://clickhouse.com/docs/sql-reference/statements/select/distinct) - [FROM clause](https://clickhouse.com/docs/sql-reference/statements/select/from) - [SAMPLE clause](https://clickhouse.com/docs/sql-reference/statements/select/sample) - [JOIN clause](https://clickhouse.com/docs/sql-reference/statements/select/join) - [PREWHERE clause](https://clickhouse.com/docs/sql-reference/statements/select/prewhere) - [WHERE clause](https://clickhouse.com/docs/sql-reference/statements/select/where) - [WINDOW clause](https://clickhouse.com/docs/sql-reference/window-functions) - [GROUP BY clause](https://clickhouse.com/docs/sql-reference/statements/select/group-by) - [LIMIT BY clause](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [HAVING clause](https://clickhouse.com/docs/sql-reference/statements/select/having) - [QUALIFY clause](https://clickhouse.com/docs/sql-reference/statements/select/qualify) - [LIMIT clause](https://clickhouse.com/docs/sql-reference/statements/select/limit) - [OFFSET clause](https://clickhouse.com/docs/sql-reference/statements/select/offset) - [UNION clause](https://clickhouse.com/docs/sql-reference/statements/select/union) - [INTERSECT clause](https://clickhouse.com/docs/sql-reference/statements/select/intersect) - [EXCEPT clause](https://clickhouse.com/docs/sql-reference/statements/select/except) - [INTO OUTFILE clause](https://clickhouse.com/docs/sql-reference/statements/select/into-outfile) - [FORMAT clause](https://clickhouse.com/docs/sql-reference/statements/select/format) ## SELECT Clause[](https://clickhouse.com/docs/sql-reference/statements/select#select-clause "Direct link to SELECT Clause") [Expressions](https://clickhouse.com/docs/sql-reference/syntax#expressions) specified in the `SELECT` clause are calculated after all the operations in the clauses described above are finished. These expressions work as if they apply to separate rows in the result. If expressions in the `SELECT` clause contain aggregate functions, then ClickHouse processes aggregate functions and expressions used as their arguments during the [GROUP BY](https://clickhouse.com/docs/sql-reference/statements/select/group-by) aggregation. If you want to include all columns in the result, use the asterisk (``) symbol. For example, `SELECT FROM ...`. ### Dynamic column selection[](https://clickhouse.com/docs/sql-reference/statements/select#dynamic-column-selection "Direct link to Dynamic column selection") Dynamic column selection (also known as a COLUMNS expression) allows you to match some columns in a result with a [re2](https://en.wikipedia.org/wiki/RE2_$software$) regular expression. ``` COLUMNS('regexp') ``` For example, consider the table: ``` CREATE TABLE default.col_names (aa Int8, ab Int8, bc Int8) ENGINE = TinyLog ``` The following query selects data from all the columns containing the `a` symbol in their name. ``` SELECT COLUMNS('a') FROM col_names ``` ``` ┌─aa─┬─ab─┐ │ 1 │ 1 │ └────┴────┘ ``` The selected columns are returned not in the alphabetical order. You can use multiple `COLUMNS` expressions in a query and apply functions to them. For example: ``` SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM col_names ``` ``` ┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐ │ 1 │ 1 │ 1 │ Int8 │ └────┴────┴────┴────────────────┘ ``` Each column returned by the `COLUMNS` expression is passed to the function as a separate argument. Also you can pass other arguments to the function if it supports them. Be careful when using functions. If a function does not support the number of arguments you have passed to it, ClickHouse throws an exception. For example: ``` SELECT COLUMNS('a') + COLUMNS('c') FROM col_names ``` ``` Received exception from server (version 19.14.1): Code: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus does not match: passed 3, should be 2. ``` In this example, `COLUMNS('a')` returns two columns: `aa` and `ab`. `COLUMNS('c')` returns the `bc` column. The `+` operator can't apply to 3 arguments, so ClickHouse throws an exception with the relevant message. Columns that matched the `COLUMNS` expression can have different data types. If `COLUMNS` does not match any columns and is the only expression in `SELECT`, ClickHouse throws an exception. ### Asterisk[](https://clickhouse.com/docs/sql-reference/statements/select#asterisk "Direct link to Asterisk") You can put an asterisk in any part of a query instead of an expression. When the query is analyzed, the asterisk is expanded to a list of all table columns (excluding the `MATERIALIZED` and `ALIAS` columns). There are only a few cases when using an asterisk is justified: - When creating a table dump. - For tables containing just a few columns, such as system tables. - For getting information about what columns are in a table. In this case, set `LIMIT 1`. But it is better to use the `DESC TABLE` query. - When there is strong filtration on a small number of columns using `PREWHERE`. - In subqueries (since columns that aren't needed for the external query are excluded from subqueries). In all other cases, we do not recommend using the asterisk, since it only gives you the drawbacks of a columnar DBMS instead of the advantages. In other words using the asterisk is not recommended. ### Extreme Values[](https://clickhouse.com/docs/sql-reference/statements/select#extreme-values "Direct link to Extreme Values") In addition to results, you can also get minimum and maximum values for the results columns. To do this, set the extremes setting to 1. Minimums and maximums are calculated for numeric types, dates, and dates with times. For other columns, the default values are output. An extra two rows are calculated – the minimums and maximums, respectively. These extra two rows are output in `XML`, `JSON`, `TabSeparated`, `CSV`, `Vertical`, `Template` and `Pretty` [formats](https://clickhouse.com/docs/interfaces/formats), separate from the other rows. They are not output for other formats. In `JSON` and `XML` formats, the extreme values are output in a separate 'extremes' field. In `TabSeparated`, `CSV` and `Vertical` formats, the row comes after the main result, and after 'totals' if present. It is preceded by an empty row (after the other data). In `Pretty` formats, the row is output as a separate table after the main result, and after `totals` if present. In `Template` format the extreme values are output according to specified template. Extreme values are calculated for rows before `LIMIT`, but after `LIMIT BY`. However, when using `LIMIT offset, size`, the rows before `offset` are included in `extremes`. In stream requests, the result may also include a small number of rows that passed through `LIMIT`. ### Notes[](https://clickhouse.com/docs/sql-reference/statements/select#notes "Direct link to Notes") You can use synonyms (`AS` aliases) in any part of a query. The `GROUP BY`, `ORDER BY`, and `LIMIT BY` clauses can support positional arguments. To enable this, switch on the [enable\_positional\_arguments](https://clickhouse.com/docs/operations/settings/settings#enable_positional_arguments) setting. Then, for example, `ORDER BY 1,2` will be sorting rows in the table on the first and then the second column. ## Implementation Details[](https://clickhouse.com/docs/sql-reference/statements/select#implementation-details "Direct link to Implementation Details") If the query omits the `DISTINCT`, `GROUP BY` and `ORDER BY` clauses and the `IN` and `JOIN` subqueries, the query will be completely stream processed, using O(1) amount of RAM. Otherwise, the query might consume a lot of RAM if the appropriate restrictions are not specified: - `max_memory_usage` - `max_rows_to_group_by` - `max_rows_to_sort` - `max_rows_in_distinct` - `max_bytes_in_distinct` - `max_rows_in_set` - `max_bytes_in_set` - `max_rows_in_join` - `max_bytes_in_join` - `max_bytes_before_external_sort` - `max_bytes_ratio_before_external_sort` - `max_bytes_before_external_group_by` - `max_bytes_ratio_before_external_group_by` For more information, see the section "Settings". It is possible to use external sorting (saving temporary tables to a disk) and external aggregation. ## SELECT modifiers[](https://clickhouse.com/docs/sql-reference/statements/select#select-modifiers "Direct link to SELECT modifiers") You can use the following modifiers in `SELECT` queries. \| Modifier \| Description \| \|---\|---\| \| [`APPLY`](https://clickhouse.com/docs/sql-reference/statements/select/apply-modifier) \| Allows you to invoke some function for each row returned by an outer table expression of a query. \| \| [`EXCEPT`](https://clickhouse.com/docs/sql-reference/statements/select/except-modifier) \| Specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output. \| \| [`REPLACE`](https://clickhouse.com/docs/sql-reference/statements/select/replace-modifier) \| Specifies one or more [expression aliases](https://clickhouse.com/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. \| ### Modifier Combinations[](https://clickhouse.com/docs/sql-reference/statements/select#modifier-combinations "Direct link to Modifier Combinations") You can use each modifier separately or combine them. Examples:* Using the same modifier multiple times. ``` SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) FROM columns_transformers; ``` ``` ┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐ │ 2 │ 3 │ └──────────────────────────┴──────────────────────────┘ ``` Using multiple modifiers in a single query. ``` SELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers; ``` ``` ┌─sum(plus(i, 1))─┬─sum(k)─┐ │ 222 │ 347 │ └─────────────────┴────────┘ ``` ## SETTINGS in SELECT Query[](https://clickhouse.com/docs/sql-reference/statements/select#settings-in-select-query "Direct link to SETTINGS in SELECT Query") You can specify the necessary settings right in the `SELECT` query. The setting value is applied only to this query and is reset to default or previous value after the query is executed. Other ways to make settings see [here](https://clickhouse.com/docs/operations/settings/overview). 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). Example ``` SELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1; ```
Shard	89 (laksa)
Root Hash	12633450985039531489
Unparsed URL	com,clickhouse!/docs/sql-reference/statements/select s443