SELECT statements - Exclusive Mode| Alibaba Cloud Documentation Center

Syntax

SELECT [ DISTINCT ]
{ * | projectItem [, projectItem ]* }
FROM tableExpression;

Test data


a (VARCHAR)	b (INT)	c (DATE)
a1	211	1990-02-20
b1	120	2018-05-12
c1	89	2010-06-14
a1	46	2016-04-05

Simple queries

Test statement
```
SELECT * FROM <Table name>;
```

Test result


a (VARCHAR)	b (INT)	c (DATE)
a1	211	1990-02-20
b1	120	2018-05-12
c1	89	2010-06-14
a1	46	2016-04-05

Rename objects

Test statement
```
SELECT a, c AS d FROM <Table name>;
```

Test result


a (VARCHAR)	d (DATE)
a1	1990-02-20
b1	2018-05-12
c1	2010-06-14
a1	2016-04-05

Deduplication queries

Test statement
```
SELECT DISTINCT a FROM Table name;
```

Test result


a (VARCHAR)
a1
b1
c1

Subqueries

In most cases, SELECT statements read data from tables, for example, SELECT column_1, column_2 … FROM table_name. SELECT statements can also read data from the results of other SELECT statements. This is known as subqueries.

Note You must specify aliases in subqueries.

Test statement

INSERT INTO result_table
SELECT * FROM
               (SELECT   t.a,
                         sum(t.b) AS sum_b
                FROM     t1 t
                GROUP BY t.a
               ) t1 
WHERE  t1.sum_b > 100;

Test result


a (VARCHAR)	b (INT)
a1	211
b1	120
a1	257

Note The preceding test result is a debugging result. In the result, you can view the computing process. If your job is published and the result table is stored in DataHub, Alibaba Cloud Message Queue for Apache Kafka, or Alibaba Cloud Message Queue, the computing process is displayed. If your job is published and the result table is stored in a relational database such as ApsaraDB for RDS, the records that have the same primary key values are combined into one record.