Updated on 2025-09-05 GMT+08:00

SELECT

SELECT

  • Syntax Format
    1
    2
    3
    4
    5
    6
    SELECT [ ALL | DISTINCT ]
      { * | projectItem [, projectItem ]* }
      FROM tableExpression
      [ WHERE booleanExpression ]
      [ GROUP BY { groupItem [, groupItem ]* } ]
      [ HAVING booleanExpression ]
    
  • Syntax Description

    SELECT is used to select data from a table.

    ALL indicates that all results are returned.

    DISTINCT indicates that the duplicated results are removed.

  • Precautions
    • The table to be queried must exist. Otherwise, an error is reported.
    • WHERE is used to specify the search condition, which can be the arithmetic operator, relational operator, or logical operator.
    • GROUP BY is used to specify the grouping field, which can be one or more multiple fields.
  • Example

    Select the order which contains more than 3 pieces of data.

    1
    SELECT  * FROM Orders WHERE units > 3; 
    

    Queries a group of constant data.

    1
    SELECT 'Lily', 'male', 'student', 17;
    

DISTINCT

  • Function

    In a table, a column may contain multiple duplicate values. Sometimes, you may want to list only different values. The DISTINCT keyword is used to return only unique values.

  • Syntax Format
    1
    2
    3
    4
    5
    6
    SELECT [ ALL | DISTINCT ]
      { * | projectItem [, projectItem ]* }
      FROM tableExpression
      [ WHERE booleanExpression ]
      [ GROUP BY { groupItem [, groupItem ]* } ]
      [ HAVING booleanExpression ]
    
  • Example

    Query all asset types and deduplicate data based on type.

    SELECT DISTINCT type FROM security_resource;

WHERE

  • Syntax Format
    1
    2
    3
    SELECT   { * | projectItem [, projectItem ]* }
      FROM tableExpression
      [ WHERE booleanExpression ]
    
  • Syntax Description

    This clause is used to filter the query results using the WHERE clause.

  • Precautions
    • The to-be-queried table must exist.
    • WHERE filters the records that do not meet the requirements.
  • Operators

    Operator

    Description

    =

    Queries logs with the value of a specific field equal to a given value.

    <>

    Queries logs with the value of a specific field not equal to a given value.

    >

    Queries logs with the value of a specific field greater than a given value.

    <

    Queries logs with the value of a specific field less than a given value.

    >=

    Queries logs with the value of a specific field equal to or greater than a given value.

    <=

    Queries logs with the value of a specific field equal to or less than a given value.

    IN ('value1', 'value2')

    Queries logs with the value of a specific field set to value1 or value2.

    BETWEEN value1 and value2

    Queries logs with the value of a specific field within the range of value1 and value2.

    LIKE

    Queries logs with a fuzzy keyword, for example, like '%one%'. The percent sign (%) is a wildcard that can replace any character.

    IS NULL

    Queries logs with the value of a specific field set to NULL.

    IS NOT NULL

    Queries logs with the value of a specific field set to NOT NULL.

  • Example

    Search orders which contain more than 3 pieces and fewer than 10 pieces of data.

    1
    2
    SELECT  * FROM Orders
      WHERE units > 3 and units < 10; 
    

IN Operator

  • Syntax Format
    1
    2
    3
    SELECT [ ALL | DISTINCT ]   { * | projectItem [, projectItem ]* }
      FROM tableExpression
      WHERE column_name IN (value (, value)* ) | query
    
  • Syntax Description

    The IN operator allows multiple values to be specified in the WHERE clause. It returns true if the expression exists in the given table subquery.

  • Precautions

    The subquery table must consist of a single column, and the data type of the column must be the same as that of the expression.

  • Example

    Return user and amount information of the products in NewProducts of the Orders table.

    1
    2
    3
    4
    5
    SELECT user, amount
    FROM Orders
    WHERE product IN (
        SELECT product FROM NewProducts
    );