Select

Selecting data is the most important aspect of SQL for database users. This page provides an overview of different statements that can be used in the SELECT expression.

Statements order

You must remember that SQL statements are executed in a specific order. The following picture shows the order of basic sql statements.

Origin of the image is this page.

Sorting (ORDER BY)

Result of the query can be sorted using syntax ORDER BY <columns> (DESC). Find out more in the special page.

---

The following cell demonstrates an example where the query uses ORDER BY numeric_value, ensuring that the entire table follows this order.

--postgreSQL
SELECT *
FROM (
    VALUES (7, 'a'), (5, 'b'), (1, 'c'), (4, 'd'), (4, 'e'), (5, 'f')
) AS temp(numeric_value, symbol)
ORDER BY numeric_value;

numeric_value	symbol
1	c
4	d
4	e
5	b
5	f
7	a

Empty values

You can check if value (not) taking NULL value using following suntax <value> IS (NOT) NULL. Check more about handling empty values in specific page.

---

The following example shows the original column and the application of IS NULL and IS NOT NULL to it.

--postgreSQL
SELECT
    numeric_value,
    numeric_value IS NULL is_null,
    numeric_value IS NOT NULL is_not_null
FROM (
    VALUES (NULL), (1), (2), (NULL), (4), (5)
) AS temp(numeric_value);

numeric_value	is_null	is_not_null
	True	False
1	False	True
2	False	True
	True	False
4	False	True
5	False	True

Conditional (CASE)

With the CASE keyword, you can assign specified values to logical conditions. Find out more in the specific page.

---

The following cell shows how you can assign a string that describes the value originally in the column.

--postgreSQL
SELECT
    numeric_value,
    CASE
        WHEN numeric_value<2 THEN 'less than two'
        ELSE 'more or equals than two'
    END
FROM (
    VALUES (0), (1), (2), (3), (4), (5)
) AS temp(numeric_value);

numeric_value	case
0	less than two
1	less than two
2	more or equals than two
3	more or equals than two
4	more or equals than two
5	more or equals than two

Array expanding

If you have columns of arrays and need to create a separate record for each value in the array, you may need operations to unnest the arrays. Find out more in particular page.

---

The following cell demonstrates the result of applying the unnest function to a column containing arrays. Each array is expanded so that each element is represented in a separate record, duplicating the other columns’ values accordingly.

--postgreSQL
SELECT 
    col1, 
    unnest(col2) AS unnestted_value
FROM (
    VALUES
    (10, '{1,2,3}'::int[]),
    (7, '{3,4,1}'::int[])
) AS temp(col1, col2);

col1	unnestted_value
10	1
10	2
10	3
7	3
7	4
7	1