Resource Management:Basic SQL syntax

SELECT list

The SELECT list sits between SELECT and FROM. It specifies which columns to return and supports aliases, arithmetic expressions, string operations, and function calls.

The following code shows the syntax:

SELECT 
	column1, column2, ... , columnN 
FROM 
	table_name;

• column1, column2,...columnN are the column names in the table.

• table_name is the name of the table.

Use any of the following SELECT patterns based on your needs:

Query all columns in the table.

SELECT
	*
FROM
	table_name;

Query specific columns in the table.

SELECT
	column1, column2, ...columnN
FROM
	table_name;

Assign an alias to a column.

SELECT 
	column1 AS alias_name 
FROM 
	table_name; 

Compute a value from two columns.

SELECT 
	column1 + column2 AS expression 
FROM 
	table_name;

FROM

The FROM clause specifies the table or tables to query. If multiple tables are listed, the query scope is the Cartesian product (cross join) of those tables. Use the WHERE clause to narrow the result to a useful subset.

The FROM clause supports the following elements:

• table_name: the name of an existing table.

• alias: an alias for a table that simplifies references and removes ambiguity. When you assign an alias, the original table name is hidden. For example, FROM resources AS a means all other clauses must reference a to access the resources table.

• select: a subquery in the FROM clause. The subquery result becomes a temporary table, and the outer query runs against it. Enclose the subquery in parentheses and assign it an alias.

• join_type: the type of join. For more information, see JOIN.

In advanced search, the resources table is the core data table that stores resource properties. For its schema, see the Table used for queries section in Overview.

JOIN

The JOIN clause combines rows from two or more tables based on shared columns. Five join types are supported — select a join type based on the dataset you need.

• CROSS JOIN: returns the Cartesian product of two tables — every row in one table paired with every row in the other. Use this to generate all possible data combinations.

• INNER JOIN: returns only rows that match in both tables based on the common columns.

• LEFT OUTER JOIN: returns all rows from the left table and the matching rows from the right table. Returns NULL for right-table columns when no match exists.

• RIGHT OUTER JOIN: returns all rows from the right table and the matching rows from the left table. Returns NULL for left-table columns when no match exists.

• FULL OUTER JOIN: returns all rows from both tables. Returns NULL for columns from the non-matching side.

WHERE

The WHERE clause filters rows by a condition. The following code shows the syntax:

SELECT 
	column1, column2, ... , columnN 
FROM 
	table_name 
WHERE 
	[condition]

The WHERE clause supports the following operator types:

• Comparison operators:

  Use = to return rows with an exact match. For example, query all Elastic Compute Service (ECS) instances:

  SELECT 
  	resource_id,
  	resource_name,
  	resource_type,
   	region_id,
   	account_id 
  FROM 
  	resources 
  WHERE 
  	resource_type = 'ACS::ECS::Instance';

  Use >, <, >=, <=, or != to filter by range or inequality. For example, query resources created after a specific date:

  SELECT
  	resource_id,
   	resource_name,
   	region_id,
   	resource_type,
   	account_id,
   	create_time
  FROM
   	resources
  WHERE
   	create_time > '20230607';

• Logical operators:

  Use AND, OR, and NOT to combine multiple filter conditions.

  AND returns rows that meet all specified conditions. For example, query ECS instances in the China (Hangzhou) region:

  SELECT
  	resource_id,
  	resource_name,
  	resource_type,
  	region_id,
  	account_id
  FROM
  	resources
  WHERE
  	region_id = 'cn-hangzhou'
  	AND resource_type = 'ACS::ECS::Instance';

  OR returns rows that meet any of the specified conditions. For example, query ECS instances and their attached disks:

  SELECT
  	resource_id,
  	resource_name,
  	resource_type,
  	region_id,
  	account_id
  FROM
  	resources
  WHERE
  	resource_type = 'ACS::ECS::Disk'
  	OR resource_type = 'ACS::ECS::Instance';

• LIKE operator:

  LIKE performs fuzzy matching and is typically used with wildcards. PostgreSQL supports two wildcards:

  • Percent sign (%): matches zero or more characters of any length.

  • Underscore (_): matches exactly one character.

  Without wildcards, LIKE behaves identically to =. For example, query all ECS resource types using a suffix wildcard:

  SELECT
   resource_id,
   resource_name,
   region_id,
   zone_id,
   resource_type,
   account_id
  FROM
   resources
  WHERE
   resource_type LIKE 'ACS::ECS::%';

• IN operator:

  IN checks whether a value appears in a list and returns true if it does.

  SELECT 
  	column1, column2, ... , columnN 
  FROM 
  	table_name 
  WHERE 
  	exp IN (value1, value2, ..., valueN);

  • exp can be a column name, a value, or an expression such as a function call or arithmetic operation.

  • value1, value2, ..., valueN is the value list. Separate values with commas (,).

  For example, query ECS instances and their attached disks:

  SELECT
  	resource_id,
  	resource_name,
  	region_id,
  	zone_id,
  	resource_type,
  	account_id
  FROM
  	resources
  WHERE
  	resource_type IN('ACS::ECS::Instance', 'ACS::ECS::Disk');

• NULL operators:

  IS NULL checks whether a column has a NULL value. IS NOT NULL checks whether a column has a non-NULL value. For example, query resources where resource_name is NULL:

  SELECT
  	resource_id,
  	resource_name,
  	region_id,
  	zone_id,
  	resource_type,
  	account_id
  FROM
  	resources
  WHERE
  	resource_name IS NULL;

ORDER BY

The ORDER BY clause sorts the query result. The following code shows the syntax:

SELECT 
	column1, column2, ... , columnN
FROM 
	table_name
ORDER BY 
	column1, column2, .. columnN ASC | DESC;

ASC sorts in ascending order; DESC sorts in descending order. If you omit the sort direction, the default is ascending.

Each sort direction applies only to the column it immediately follows. For example, ORDER BY column1, column2 DESC sorts by column1 ascending first, then by column2 descending for rows with the same column1 value. ORDER BY column1 DESC, column2 DESC sorts both columns in descending order.

Sort by one or more columns, including columns not listed in the SELECT list. When multiple columns are specified, the result is sorted by the first column first. For rows with identical values in the first column, the result is then sorted by the second column.

When choosing columns for ORDER BY, note the following:

• Avoid sorting by columns that contain NULL values. Without special handling, resources sort incorrectly when a column has NULL values.

• If the sort column has duplicate values, the same SELECT statement may return different results on repeated executions. Add a secondary sort column to guarantee consistent results.

For example, sort resources first by resource type, then by resource ID within each type:

SELECT
	resource_id,
	resource_name,
	region_id,
	zone_id,
	resource_type,
	account_id
FROM
	resources
ORDER BY
	resource_type,
	resource_id;

GROUP BY

The GROUP BY clause groups the query result by one or more columns. For each group, apply aggregate functions such as COUNT, SUM, or AVG to compute statistics. The following code shows the syntax:

SELECT 
	column1, column2, ... , columnN
FROM 
	table_name
WHERE 
	[ conditions ]
GROUP BY 
	column1, column2....columnN
ORDER BY 
	column1, column2....columnN

The GROUP BY clause must appear after the WHERE clause (if any) and before the ORDER BY clause (if any). All columns in the GROUP BY clause must also appear in the SELECT list; otherwise, an error is returned.

For example, group resources by resource type and count each type. Because the result contains only a string column and a numeric column, advanced search can render it as a chart:

SELECT
 	resource_type,
 	COUNT(*) AS cnt
FROM
 	resources
GROUP BY
 	resource_type
ORDER BY
 	cnt DESC;

LIMIT

The LIMIT clause caps the number of rows returned. Use it together with OFFSET to control which rows are returned:

LIMIT 
	count OFFSET start

The following code shows the full syntax with LIMIT and OFFSET combined:

SELECT 
	column1, column2, ... , columnN 
FROM 
	table_name 
LIMIT 
	[no of rows] OFFSET [row num]

LIMIT 10 returns a maximum of 10 rows. OFFSET 10 starts the result from the 11th row of the source table. Without OFFSET, the result starts from the first row.

A query in advanced search returns at most 1,000 rows. For large datasets, use LIMIT with OFFSET to paginate through results. Always combine LIMIT with ORDER BY to guarantee consecutive, consistent rows across pages.

For example, return the top 20 resources sorted by resource ID in descending order:

SELECT
	resource_id,
	resource_name,
	region_id,
	resource_type,
	account_id
FROM
	resources
ORDER BY
	resource_id DESC
LIMIT
	20;

For example, retrieve rows 1001–2000 sorted by resource ID in descending order:

SELECT
 	resource_id,
 	resource_name,
 	region_id,
 	resource_type,
 	account_id
FROM
 	resources
ORDER BY
 	resource_id DESC
LIMIT
 	1000 OFFSET 1000;