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