SELECT

Purpose

This statement is used to query data from one or more tables.

This section describes the general SELECT syntax. For more information about other SELECT statements, see:

• JOIN clause
• WINDOW clause
• UNION clause
• HYBRID_SEARCH (hybrid search)
• SELECT INTO

Privilege requirements

To execute the SELECT statement, the current user must have the SELECT privilege. For more information about the privileges of OceanBase Database, see Privilege types in MySQL mode.

Syntax

select_stmt:
    SELECT [hint_options]
        [{DISTINCT | UNIQUE} | ALL]
        [SQL_CALC_FOUND_ROWS]
        [STRAIGHT_JOIN]
        select_expr_list
        [FROM from_list]
        [WHERE where_condition]
        [GROUP BY group_by_condition_list [WITH ROLLUP]
         | GROUP BY [group_by_condition_list] group_by_summary_option (expression_list)]
        [HAVING having_condition]
        [window_clause]
        [ORDER BY order_by_condition_list]
        [LIMIT limit_clause]
        [lock_option];

select_expr_list:
    select_expr [, select_expr ...]

select_expr:
    *
    | table_name.{* | column_name}
    | table_alias_name.{* | column_name}
    | expr [[AS] column_alias_name]

from_list:
    DUAL
    | table_references
    | { location_url | table_function }

location_url:
  'file_path'
  (
  {FORMAT = (
      TYPE = 'CSV',
      LINE_DELIMITER = '<string>' | <expr>,
      FIELD_DELIMITER = '<string>' | <expr>,
      ESCAPE = '<character>' | <expr>,
      FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
      ENCODING = 'charset',
      NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
      SKIP_HEADER = <int>,
      SKIP_BLANK_LINES = { TRUE | FALSE },
      PARSE_HEADER = { TRUE | FALSE },
      TRIM_SPACE = { TRUE | FALSE },
      EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
    )
   | FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
  }
  [PATTERN = '<regex_pattern>']
  )

table_function:
  {
  FILES (
    LOCATION = {'file_path' | @location_name['/path']},
    {
      FORMAT = (
        TYPE = 'CSV',
        LINE_DELIMITER = '<string>' | <expr>,
        FIELD_DELIMITER = '<string>' | <expr>,
        ESCAPE = '<character>' | <expr>,
        FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
        ENCODING = 'charset',
        NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
        SKIP_HEADER = <int>,
        SKIP_BLANK_LINES = { TRUE | FALSE },
        PARSE_HEADER = { TRUE | FALSE },
        TRIM_SPACE = { TRUE | FALSE },
        EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
      )
      | FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
    },
    [PATTERN = '<regex_pattern>']
  )
  | SOURCE (
      TYPE = 'ODPS',
      ACCESSID = '<string>',
      ACCESSKEY = '<string>',
      ENDPOINT = '<string>',
      TUNNEL_ENDPOINT = '<string>',
      PROJECT_NAME = '<string>',
      SCHEMA_NAME = '<string>',
      TABLE_NAME = '<string>',
      QUOTA_NAME = '<string>',
      COMPRESSION_CODE = '<string>'
    )
  }

where_condition:
    expression

group_by_condition_list:
    group_by_condition [, group_by_condition ...]

group_by_condition:
    expression [ASC | DESC]

group_by_summary_option:
    GROUPING SETS
    | ROLLUP
    | CUBE

expression_list:
    expression [, expression ...]

having_condition:
    expression

order_by_condition_list:
    order_by_condition [, order_by_condition ...]

order_by_condition:
    expression [ASC | DESC]

limit_clause:
    [offset,] row_count
    | row_count OFFSET offset

lock_option:
    FOR UPDATE [opt_for_update_wait]
    | LOCK IN SHARE MODE

opt_for_update_wait:
    WAIT {decimal | intnum}
    | NOWAIT
    | NO_WAIT
    | SKIP LOCKED

Parameters

select_expr

select_expr specifies the columns or expressions to be included in the query result. Multiple expressions or column names are separated by commas (,). The values are as follows:

• *: selects all columns.

  Example:

  1. Create tables tbl1 and tbl2.

     CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT)
       PARTITION BY HASH(col1)
       PARTITIONS 5;
     

     CREATE TABLE tbl2(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
     

  2. Insert test data into table tbl1.

     INSERT INTO tbl1 VALUES(1, 'A1', 1),(2, 'A2', 2),(3, 'A3', 3);
     

     INSERT INTO tbl2 VALUES(1, 'A1', 1),(2, 'A2', 22),(3, 'A3', 33);
     

  3. View the data in table tbl1.

     SELECT * FROM tbl1;
     

     The returned result is as follows:

     +------+------+------+
     | col1 | col2 | col3 |
     +------+------+------+
     |    1 | A1   |    1 |
     |    2 | A2   |    2 |
     |    3 | A3   |    3 |
     +------+------+------+
     3 rows in set
     

• table_name.{* | column_name}: specifies all columns or a specific column in a specified table.

  • table_name.*: specifies all columns in a specified table.

  • table_name.column_name: specifies a specific column in a specified table.

    Example:

    Read the col1 data from table tbl1.

    SELECT tbl1.col1 FROM tbl1;
    

    The returned result is as follows:

    +------+
    | col1 |
    +------+
    |    1 |
    |    2 |
    |    3 |
    +------+
    3 rows in set
    

• table_alias_name.{* | column_name}: similar to the previous format, but uses the table alias when selecting columns.

• expr [[AS] column_alias_name]: specifies an expression to be selected and allows you to assign an alias to the selected expression as the column name.

  • expr: specifies a column name, function, or calculation expression.

    • expr AS column_alias_name/expr column_alias_name: specifies the expression to be selected and named column_alias_name.

  Example:

  Read the col1 data from table tbl1, calculate a new column col1_add with the value col1+1, and calculate another new column col3_mul with the value col3*2.

  SELECT col1, col1+1 AS col1_add, col3*2 col3_mul FROM tbl1;
  

  The returned result is as follows:

  +------+----------+----------+
  | col1 | col1_add | col3_mul |
  +------+----------+----------+
  |    1 |        2 |        2 |
  |    2 |        3 |        4 |
  |    3 |        4 |        6 |
  +------+----------+----------+
  3 rows in set
  

from_list

• DUAL: specifies a virtual table name. It is typically used to perform calculations or execute functions without an actual table.

  Example:

  SELECT 1+1, SYSDATE() FROM DUAL;
  

  The returned result is as follows:

  +------+---------------------+
  | 1+1  | SYSDATE()           |
  +------+---------------------+
  |    2 | 2024-08-28 15:20:59 |
  +------+---------------------+
  1 row in set
  

• table_references: specifies a list of table references. Multiple table references are separated by commas (,). It indicates the list of data source tables to be queried, which can be a combination of one or more tables. For more information about the syntax of table_references, see JOIN clause.

where_condition

expression: specifies the condition expression to filter the rows to be updated.

Example:

Select all rows from tbl1 that satisfy the conditions col1 > 1 and col2 = 'A3'.

SELECT * FROM tbl1
  WHERE col1 > 1
  AND col2 = 'A3';

The returned result is as follows:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    3 | A3   |    3 |
+------+------+------+
1 row in set

table_function

• The LOCATION clause specifies the path where the external table files are stored. Usually, the data files of an external table are stored in a separate directory, which can contain subdirectories. When you create an external table, the system automatically collects all files in the directory. Valid values:

  • file_path: specifies the path of the external table files. For more information, see the following description:

    • For a local external table, the LOCATION clause is specified as LOCATION = '[file://] local_file_path', where local_file_path can be a relative path or an absolute path. If you specify a relative path, the current directory must be the installation directory of OceanBase Database. secure_file_priv specifies the file paths that the OBServer nodes can access. local_file_path must be a subpath of the secure_file_priv path.

    • For a remote external table, the LOCATION clause is specified as:

      • LOCATION = '{oss\|s3}://$ACCESS_ID:$ACCESS_KEY@$HOST:s3_region/remote_file_path', where $ACCESS_ID, $ACCESS_KEY, and $HOST are the access information required for accessing Alibaba Cloud OSS, AWS S3, or an object storage service that is compatible with the S3 protocol. s3_region specifies the region information when you use S3. These sensitive access information are stored in the system table of the database in an encrypted manner.

      • LOCATION = hdfs://localhost:port/PATH, where localhost specifies the address of HDFS, port specifies the port number of HDFS, and PATH specifies the directory path in HDFS.

        • If Kerberos authentication is used, the LOCATION clause is specified as LOCATION = 'hdfs://localhost:port/user?principal=xxx&keytab=xxx&krb5conf=xxx&configs=xxx'. In this case:

          • principal: specifies the user for login authentication.
          • keytab: specifies the path of the key file for user authentication.
          • krb5conf: specifies the path of the description file for the Kerberos environment.
          • configs: specifies additional HDFS configurations. The default value is empty. However, in a Kerberos environment, this parameter usually has a value, which needs to be configured. For example, dfs.data.transfer.protection=authentication,privacy specifies the data transmission protection level as authentication and privacy.

  • @location_name['/path']: specifies the path of the external table files by referencing the location object. ['/path'] is optional and specifies a subdirectory. For more information about how to create a location object, see CREATE LOCATION.

    Note

    For OceanBase Database V4.4.x, the @location_name['/path'] parameter is supported starting from V4.4.1.

• The FORMAT clause specifies the file format. Valid values: CSV, PARQUET, and ORC.

  • When the value of the TYPE clause is 'CSV', the following parameters are supported:
    • LINE_DELIMITER: specifies the line delimiter of the CSV file. Default value: LINE_DELIMITER='\n'.
    • FIELD_DELIMITER: specifies the column delimiter of the CSV file. Default value: FIELD_DELIMITER='\t'.
    • ESCAPE: specifies the escape character of the CSV file. The default value is ESCAPE ='\'.
    • FIELD_OPTIONALLY_ENCLOSED_BY: specifies the character that wraps the field values in the CSV file. Default value: empty. This option is used to wrap only specific types of fields, such as CHAR, VARCHAR, TEXT, and JSON.
    • ENCODING: specifies the character set encoding format of the file. If this parameter is not specified, the default value is UTF8MB4.
    • NULL_IF: specifies the string that is treated as NULL. Default value: empty.
    • SKIP_HEADER: specifies the number of lines to skip at the beginning of the file.
    • SKIP_BLANK_LINES: specifies whether to skip blank lines. Default value: FALSE, which means that blank lines are not skipped.
    • TRIM_SPACE: specifies whether to remove leading and trailing spaces from the fields in the file. Default value: FALSE, which means that leading and trailing spaces are not removed from the fields in the file.
    • EMPTY_FIELD_AS_NULL: specifies whether to treat empty strings as NULL. Default value: FALSE, which means that empty strings are not treated as NULL.
    • PARSE_HEADER: specifies to directly obtain the first line of the CSV file and use it as the column names for each column.

      Notice

      The PARSE_HEADER and SKIP_HEADER parameters cannot be used together, as they have conflicting semantics.

  • When the value of the TYPE clause is 'PARQUET' or 'ORC', no other parameters are supported.

• The PATTERN clause specifies a regular expression pattern to filter the files in the LOCATION directory. For each file path in the LOCATION directory, if the file path matches the pattern, the external table accesses the file; otherwise, it skips the file. If this parameter is not specified, the external table accesses all files in the LOCATION directory by default.

For the ODPS format, data is not obtained through files, and there is no meaningful URL path. Therefore, only the source form of the table_function is supported.

• When the value of the TYPE clause is 'ODPS', the following parameters are supported:
  • ACCESSID: specifies the ID of the ODPS user.
  • ACCESSKEY: specifies the password of the ODPS user.
  • ENDPOINT: specifies the connection address of the ODPS service.
  • TUNNEL_ENDPOINT: specifies the connection address of the Tunnel data transmission service.
  • PROJECT_NAME: specifies the project where the table to be queried is located.
  • SCHEMA_NAME: specifies the schema of the table to be queried. This parameter is optional.
  • TABLE_NAME: specifies the name of the table to be queried.
  • QUOTA_NAME: specifies the quota to be used. This parameter is optional.
  • COMPRESSION_CODE: specifies the compression format of the data source. Valid values: ZLIB, ZSTD, LZ4, and ODPS_LZ4. If this parameter is not specified, compression is not enabled.

Here is an example:

Read data in the ODPS format.

SELECT * FROM
source (
  type = 'ODPS',
  accessid = '$ODPS_ACCESSID',
  accesskey = '$ODPS_ACCESSKEY',
  endpoint= '$ODPS_ENDPOINT',
  project_name = 'example_project',
  schema_name = '',
  table_name = 'example_table',
  quota_name = '',
  compression_code = ''
);

location_url

• The FORMAT clause specifies the file format. Valid values: CSV, PARQUET, and ORC:
  • For CSV files, specify parse_header=true/false to indicate whether to parse the first line as the column headers (default is false). Additionally, use TYPE=CSV to declare the file format (this must be specified when exporting data).
  • For PARQUET or ORC files, the file structure is automatically recognized, and no additional configuration for the header row is required.

Here is an example:

SELECT * FROM
FILES( location = '/data/',
      format (TYPE = 'csv', field_delimiter = ',', parse_header = true),
      pattern = 'datafiles$';

group_by_condition

expression [ASC | DESC]: specifies the grouping condition. You can add ASC or DESC to specify whether the groups are sorted in ascending or descending order.

Here is an example:

Query col1 and col2 from the tbl1 table, group the result by col2, and calculate the sum of col3.

SELECT col1, col2, SUM(col3)
  FROM tbl1
  GROUP BY col2 DESC;

The result is as follows:

+------+------+-----------+
| col1 | col2 | SUM(col3) |
+------+------+-----------+
|    3 | A3   |         3 |
|    2 | A2   |         2 |
|    1 | A1   |         1 |
+------+------+-----------+
3 rows in set

group_by_summary_option

• GROUPING SETS: specifies custom grouping. You can specify multiple grouping combinations. It has the following characteristics:

  • You can flexibly define the grouping combinations as needed.
  • The result set only contains the grouping combinations specified by you.

  Note

  GROUP BY GROUPING SETS ((col1), (col2), (col3)) is equivalent to GROUP BY GROUPING SETS (col1, col2, col3).

  Here is an example:

  SELECT col1, col2, col3, COUNT(*)
  FROM tbl1
  GROUP BY GROUPING SETS ((col1), (col2), (col3));
  

  The result is as follows:

  +------+------+------+----------+
  | col1 | col2 | col3 | COUNT(*) |
  +------+------+------+----------+
  |    1 | NULL | NULL |        1 |
  |    2 | NULL | NULL |        1 |
  |    3 | NULL | NULL |        1 |
  | NULL | A1   | NULL |        1 |
  | NULL | A2   | NULL |        1 |
  | NULL | A3   | NULL |        1 |
  | NULL | NULL |    1 |        1 |
  | NULL | NULL |    2 |        1 |
  | NULL | NULL |    3 |        1 |
  +------+------+------+----------+
  9 rows in set
  

• ROLLUP: specifies hierarchical aggregation. It reduces the number of grouping columns in the order of the grouping columns and generates hierarchical aggregation results. It has the following characteristics:

  • It reduces the grouping columns in the order of the columns in expression_list until only the total row remains.

  • The result set contains:

    • Details of each group.
    • Sub-aggregated data of each group.
    • The total row.

  ROLLUP can be considered a special form of GROUPING SETS. For example:

  GROUP BY ROLLUP(col1, col2, col3)
  
  is equivalent to
  
  GROUP BY GROUPING SETS ((col1, col2, col3), (col1, col2), (col1), ())
  

  ROLLUP can be used with GROUPING SETS. For example:

  GROUP BY GROUPING SETS((col2), (col3), ROLLUP(col1, col2, col3))
  
  is equivalent to
  
  GROUP BY GROUPING SETS((col2), (col3), (col1, col2, col3), (col1, col2), (col1), ())
  

  Here is an example:

  SELECT col1, col2, col3, COUNT(*)
  FROM tbl1
  GROUP BY ROLLUP (col1, col2, col3);
  

  The result is as follows:

  +------+------+------+----------+
  | col1 | col2 | col3 | COUNT(*) |
  +------+------+------+----------+
  |    1 | A1   |    1 |        1 |
  |    1 | A1   | NULL |        1 |
  |    1 | NULL | NULL |        1 |
  |    2 | A2   |    2 |        1 |
  |    2 | A2   | NULL |        1 |
  |    2 | NULL | NULL |        1 |
  |    3 | A3   |    3 |        1 |
  |    3 | A3   | NULL |        1 |
  |    3 | NULL | NULL |        1 |
  | NULL | NULL | NULL |        3 |
  +------+------+------+----------+
  10 rows in set
  

• CUBE: specifies multidimensional aggregation. It generates all possible grouping combinations. It has the following characteristics:

  • Details of each group.
  • Aggregated data of all possible grouping combinations.
  • The total row.

  CUBE can be considered a special form of GROUPING SETS. For example:

  GROUP BY CUBE(col1, col2, col3)
  
  is equivalent to
  
  GROUP BY GROUPING SETS ((col1, col2, col3), (col1, col2), (col1, col3), (col2, col3), (col1), (col2), (col3), ())
  

  CUBE can be used with GROUPING SETS. For example:

  GROUP BY col1, CUBE(col2, col3), GROUPING SETS((col4), (col5))
  
  is equivalent to
  
  GROUP BY GROUPING SETS(
    (col1, col2, col3, col4), (col1, col2, col3, col5),
    (col1, col2, col4), (col1, col2, col5),
    (col1, col3, col4), (col1, col3, col5),
    (col1, col4), (col1, col5))
  

  Here is an example:

  SELECT col1, col2, col3, COUNT(*)
  FROM tbl1
  GROUP BY CUBE (col1, col2, col3);
  

  The result is as follows:

  +------+------+------+----------+
  | col1 | col2 | col3 | COUNT(*) |
  +------+------+------+----------+
  | NULL | NULL | NULL |        3 |
  | NULL | NULL |    1 |        1 |
  | NULL | NULL |    2 |        1 |
  | NULL | NULL |    3 |        1 |
  | NULL | A1   | NULL |        1 |
  | NULL | A2   | NULL |        1 |
  | NULL | A3   | NULL |        1 |
  | NULL | A1   |    1 |        1 |
  | NULL | A2   |    2 |        1 |
  | NULL | A3   |    3 |        1 |
  |    1 | NULL | NULL |        1 |
  |    2 | NULL | NULL |        1 |
  |    3 | NULL | NULL |        1 |
  |    1 | NULL |    1 |        1 |
  |    2 | NULL |    2 |        1 |
  |    3 | NULL |    3 |        1 |
  |    1 | A1   | NULL |        1 |
  |    2 | A2   | NULL |        1 |
  |    3 | A3   | NULL |        1 |
  |    1 | A1   |    1 |        1 |
  |    2 | A2   |    2 |        1 |
  |    3 | A3   |    3 |        1 |
  +------+------+------+----------+
  22 rows in set
  

having_condition

expression: specifies the condition for filtering the result set after grouping.

Here is an example:

Query col1 and col2 from the tbl1 table, group the result by col2, and calculate the sum of col3. Then, query the rows where the sum of col3 is less than 3.

SELECT col1, col2, SUM(col3)
  FROM tbl1
  GROUP BY col2
  HAVING SUM(col3) < 3;

The result is as follows:

+------+------+-----------+
| col1 | col2 | SUM(col3) |
+------+------+-----------+
|    1 | A1   |         1 |
|    2 | A2   |         2 |
+------+------+-----------+
2 rows in set

order_by_condition

expression [ASC | DESC]: specifies the condition for sorting the result set.

ASC | DESC: optional. ASC specifies ascending order (default), and DESC specifies descending order.

Here is an example:

Query data from the tbl1 table and output the query result in descending order (DESC) based on col3.

SELECT * FROM tbl1
  ORDER BY col3 DESC;

The result is as follows:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    3 | A3   |    3 |
|    2 | A2   |    2 |
|    1 | A1   |    1 |
+------+------+------+
3 rows in set

limit_clause

• [offset,] row_count: must be an integer constant. The following table describes the parameters.

  • offset: optional. Specifies the number of rows to skip from the start of the result set (offset). The default value is 0.
  • row_count: specifies the number of rows to return. If you specify only row_count, the first row_count rows of the result set are returned.

• row_count OFFSET offset: specifies to skip offset rows from the start of the result set and return the next row_count rows.

Here is an example:

Query data from the tbl1 table, start from the second row, and return two rows of data from the tbl1 table.

SELECT * FROM tbl1
  LIMIT 1, 2;

The result is as follows:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    2 | A2   |    2 |
|    3 | A3   |    3 |
+------+------+------+
2 rows in set

lock_option

• FOR UPDATE [opt_for_update_wait]: Specifies to lock all rows in the query result set to prevent other transactions from modifying the data concurrently or reading the data concurrently at certain transaction isolation levels. For more information about using exclusive locks, see Lock query results with SELECT FOR UPDATE.

  • opt_for_update_wait: Optional. Specifies the behavior for acquiring locks. The options are as follows:

    • WAIT {decimal | intnum}: Specifies the time to wait for other transactions to release resources. If the time is exceeded, the resource cannot be acquired. The unit is seconds.
    • NOWAIT/NO_WAIT: Specifies not to wait for other transactions to release resources and directly return that the resource cannot be acquired.
    • SKIP LOCKED: Specifies not to wait for row locks. The query will be executed immediately, and the locked rows will be removed from the result set.

  Here is an example:

  • You can use SELECT ... FOR UPDATE to lock rows in a table. If you use the LIMIT 1 clause, the optimizer will push the operator down to the table scan step and lock only the rows returned by LIMIT.

    SELECT * FROM tbl1
      LIMIT 1
      FOR UPDATE;
    

  • If you use the ORDER BY clause to sort the query results, the results will be sorted first, and then LIMIT 1 will be executed. In this case, all selected rows will be locked.

    SELECT * FROM tbl1
      ORDER BY col1
      LIMIT 1
      FOR UPDATE;
    

• LOCK IN SHARE MODE: Specifies to acquire shared locks when querying data to prevent other transactions from writing to the data, but allows other transactions to read the data. For more information about using shared locks, see Lock query results with LOCK IN SHARE MODE.

Examples

Simple table query example

1. Create a table named test_tbl1.

   CREATE TABLE test_tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
   

2. Insert test data into the test_tbl1 table.

   INSERT INTO test_tbl1 VALUES (1, 'A1', 10),(2, 'A2', 15),(3, 'A1', 8);
   

3. View the data in the col2 column of the test_tbl1 table and perform deduplication.

   SELECT DISTINCT col2 FROM test_tbl1;
   

   The result is as follows:

   +------+
   | col2 |
   +------+
   | A1   |
   | A2   |
   +------+
   2 rows in set
   

Example of querying data using a DBLink

• Read the number_t table from the remote test database.

  SELECT * FROM number_t@ob_dblink;
  

  The result is as follows:

  +--------+-------------+---------+---------------------+--------------------------------+
  | c_int  | c_bigint    | c_float |    c_double         |   c_decimal                    |
  +--------+-------------+---------+---------------------+--------------------------------+
  | 214748 | 92233720368 | 3.40282 | 1.7976931348623157  | 123456789123456789.1234567890  |
  +--------+-------------+---------+---------------------+--------------------------------+
  1 row in set
  

• Read the number_t table from the remote test database and perform a JOIN operation with the local number_t table.

  SELECT a.c_decimal, b.c_double
    FROM number_t a, number_t@ob_dblink b
    WHERE a.c_int = b.c_int;
  

  The result is as follows:

  +--------------------------------+---------------------+
  |   c_decimal                    |    c_double         |
  +---------------------+--------------------------------+
  | 123456789123456789.1234567890  |  1.7976931348623157 |
  +---------------------+--------------------------------+
  1 row in set
  

• Read the datetime_t table from the remote mysql database.

  SELECT * FROM mysql.datetime_t@ob_dblink;
  

  The result is as follows:

  +-------------+------------+---------+----------------------+----------------------+
  | c_date      | c_time     | c_year  | c_datetime           |  c_timestamp         |
  +-------------+------------+---------+-----------------------+---------------------+
  |  2023-04-13 | 12:12:12   |  2078   | 2100-11-01 12:12:13  | 2100-12-01 21:14:15  |
  +-------------+------------+---------+----------------------+----------------------+
  1 row in set
  

References

Single table query