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