Skip to main content
Version: v2

Basic SELECT Syntax Tutorial

All operations are ANSI SQL-compliant syntax. For an introduction to ANSI SQL, please refer to W3School SQL Tutorial.

For advanced viewing of the complete support items, please click here Complete SELECT Syntax

SELECT​

Select Specific Fields

SELECT column1, column2
FROM table_name;

Select all fields

SELECT *
FROM table_name;

WHERE​

Select information according to the conditions

SELECT *
FROM table_name
WHERE column1 > value1;

AND, OR​

Join multiple conditions

SELECT *
FROM table_name
WHERE column1 > value1 AND column2 > value1 OR column3 > value1;

Operators​

  • < less than

    SELECT *
    FROM table_name
    WHERE column1 < value1;

  • > greater than

    SELECT *
    FROM table_name
    WHERE column1 > value1;

  • <= less than or equal to

    SELECT *
    FROM table_name
    WHERE column1 <= value1;

  • >= greater than or equal to

    SELECT *
    FROM table_name
    WHERE column1 >= value1;

  • = is equal to

    SELECT *
    FROM table_name
    WHERE column1 = value1;

  • <> is not equal to

    SELECT *
    FROM table_name
    WHERE column1 <> value1;

  • != is not equal to

    SELECT *
    FROM table_name
    WHERE column1 != value1;

  • BETWEEN a AND b between a and b

    SELECT *
    FROM table_name
    WHERE column1 BETWEEN value1 AND value2;

  • IN (a, b, c, ...) is one of a, b, c

    SELECT *
    FROM table_name
    WHERE column IN (12, 15, 18)

  • IS NULL determines whether it is Null

    SELECT *
    FROM table_name
    WHERE column1 IS NULL;

Operators for Specified Types​

Below are some common usages for you to search for different types of fields.

  • UUID

    Use uuid prefix conversion

    SELECT * FROM table
    WHERE id = uuid 'affa0ba3-2744-47c6-9f2a-35b8e2d56581'

  • Timestamp

    Use the timestamp prefix to cast

    SELECT * FROM table
    WHERE create_at > timestamp '2019-08-01 00:00'

    Use date prefix conversion

    SELECT * FROM table
    WHERE create_at > date '2019-08-01'

    Combined with interval to get other time

    SELECT * FROM table
    WHERE create_at > date '2019-08-01'
    AND create_at < date '2019-08-01' + interval '1' month

  • VARCHAR

    Use LIKE with % to match none or any character

    SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS table_name (column1)
    WHERE column1 LIKE '%b%'
    --returns 'abc' AND 'bcd'

    Use LIKE with _ to match a single character

    SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS table_name (column1)
    WHERE column1 like '_b%'
    --returns 'abc'

ORDER BY​

Sorted from small to large

SELECT *
FROM table_name
ORDER BY column1 ASC

sorted from largest to smallest

SELECT *
FROM table_name
ORDER BY column1 DESC

NULL sorted first

SELECT *
FROM table_name
ORDER BY column1 ASC NULLS FIRST

NULL sorts last

SELECT *
FROM table_name
ORDER BY column1 ASC NULLS LAST

GROUP BY​

Group by specific field

SELECT *
FROM table_name
GROUP BY column1

Group by specific field

SELECT count(*)
FROM table_name
GROUP BY column1

HAVING​

Filter the grouped data.

SELECT count(column1), column2
FROM table_name
GROUP BY column2
HAVING count(column1) > value1

JOIN​

Combine two tables according to specific fields

SELECT *
FROM table1
JOIN table2
ON table1.column1 = table2.column1

##UNION

Merge two tables

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

LIMIT, OFFSET​

  • LIMIT number How many records can be obtained at most
  • OFFSET number ignores the first few data

Obtain ten records after the 5th record

SELECT *
FROM table_name
OFFSET 5
LIMIT 10