SELECT

Syntax of the SELECT statement:

SELECT [<table column>, ... | *]
FROM [<table name> | <sub query>] AS <table name alias>
LEFT | RIGHT | CROSS | INNER JOIN <another table> AS <table name alias> ON <join condition>
WHERE <condition>
GROUP BY <table column>
HAVING <condition>
UNION | UNION ALL | EXCEPT | INTERSECT
ORDER BY <table column> [ASC | DESC]
LIMIT [<limit value>]
OFFSET <offset number>

Calling SELECT without specifying a target table

SELECT is used to return computations to the client side and can be used even without specifying a table, as seen in constructs like SELECT NOW(), or for performing operations such as working with dates, converting numbers, or calculating string lengths. However, SELECT is also used in conjunction with FROM ... to retrieve data from a specified table. When used with INSERT INTO ..., SELECT serves to select data that will be inserted into another table. In subqueries, SELECT is utilized within a larger query, not necessarily with FROM ..., to contribute to the overall computation or condition.

For example, SELECT can be used for working with dates, converting numbers, or calculating string length:

SELECT CURRENT_DATE + INTERVAL '1 day';  -- Returns tomorrow's date
SELECT LENGTH('Hello');  -- Returns the length of the string 'Hello'
SELECT CAST('123' AS INTEGER);  -- Converts the string '123' to an integer

Such use of SELECT can be useful for testing, debugging expressions, or SQL functions without accessing a real table, but more often SELECT is used to retrieve rows from one or more tables.

Retrieving values from one or multiple columns

To return values from one or several columns of a table, SELECT is used in the following form:

SELECT <column name> , <column name>
FROM <table name>;

To read all data from a table, for example, the people table, you need to execute the command SELECT * FROM people;, where * is the special symbol indicating all columns. With this statement, all rows from the table will be returned with data from all columns.

To display the "id", "name", and "lastname" columns for all rows of the people table, you can do it as follows:

SELECT id, name, lastname
FROM people;

Limiting the results obtained from a query using WHERE

To select only a subset of rows, the WHERE clause with filtering criteria is used: WHERE <column name> <condition> <column value>;:

SELECT id, name, lastname
FROM people
WHERE age > 30;

WHERE allows the use of multiple conditional selection operators (AND, OR) to create complex selection conditions, such as ranges:

SELECT id, name, lastname
FROM people
WHERE age > 30 AND age < 45;

Retrieving a subset of rows using LIMIT and OFFSET conditions

To limit the number of rows in the result set, LIMIT is used with the specified number of rows:

SELECT id, name, lastname
FROM people
WHERE age > 30 AND age < 45
LIMIT 5;

Thus, the first 5 rows from the query will be printed out. With OFFSET, you can specify how many rows to skip before starting to print out rows:

SELECT id, name, lastname
FROM people
WHERE age > 30 AND age < 45
OFFSET 3
LIMIT 5;

When specifying OFFSET 3, the first 3 rows of the resulting selection from the people table will be skipped.

Sorting the results of a query using ORDER BY

By default, the database does not guarantee the order of returned rows, and it may vary from query to query. If a specific order of rows is required, the ORDER BY clause is used with the designation of the column for sorting and the direction of the sort:

SELECT id, name, lastname, age
FROM people
WHERE age > 30 AND age < 45
ORDER BY age DESC;

Sorting is applied to the results returned by the SELECT clause, not to the original columns of the table specified in the FROM clause. Sorting can be done in ascending order – ASC (from smallest to largest - this is the default option and does not need to be specified) or in descending order – DESC (from largest to smallest). How sorting is executed depends on the data type of the column. For example, strings are stored in utf-8 and are compared according to "unicode collate" (based on character codes).

Grouping the results of a query from one or more tables using GROUP BY

GROUP BY is used to aggregate data across multiple records and group the results by one or several columns. The syntax for using GROUP BY is as follows:

SELECT <column name>, <column name>, ...
FROM <table name>
[WHERE <column name> = <value>]
GROUP BY <column name>, <column name>, ...;
[HAVING <column name> = <limit column value>]
[LIMIT <value>]
[OFFSET <value>]

Example of grouping data from the "people" table by gender ("sex") and age ("age") with a selection limit (WHERE) based on age:

SELECT sex, age
FROM people
WHERE age > 40
GROUP BY sex, age;

In the previous example, we used WHERE – an optional parameter for filtering the result, which filters individual rows before applying GROUP BY. In the next example, we use HAVING to exclude from the result the rows of groups that do not meet the condition. HAVING filters the rows of groups created by GROUP BY. When using HAVING, the query becomes grouped, even if GROUP BY is absent. All selected rows are considered to form one group, and in the SELECT list and HAVING clause, one can refer to the table columns only from aggregate functions. Such a query will yield a single row if the result of the HAVING condition is true, and zero rows otherwise.

Examples for HAVING

SELECT sex, country, age
FROM people
GROUP BY sex, country, age
HAVING sex = 'Female';

SELECT sex, name,age
FROM people
WHERE age > 40
GROUP BY sex, name, age
HAVING sex = 'Female';

Joining tables using the JOIN clause

SELECT can be applied to multiple tables with the specification of the type of table join. The joining of tables is set through the JOIN clause, which comes in five types: LEFT JOIN, RIGHT JOIN, INNER JOIN, CROSS JOIN, FULL JOIN. When a JOIN is performed on a specific condition, such as a key, and one of the tables has several rows with the same value of this key, a Cartesian product occurs. This means that each row from one table will be joined with every corresponding row from the other table.

Joining tables using LEFT JOIN, RIGHT JOIN, or INNER JOIN

The syntax for SELECT using LEFT JOIN, RIGHT JOIN, INNER JOIN, FULL JOIN is the same:

SELECT <table name left>.<column name>, ... ,
FROM <table name left>
LEFT | RIGHT | INNER | FULL JOIN <table name right> AS <table name right alias>
ON <table name left>.<column name> = <table name right>.<column name>;

All JOIN modes, except CROSS JOIN, use the keyword ON for joining tables. In the case of CROSS JOIN, its usage syntax will be as follows: CROSS JOIN <table name> AS <table name alias>;. Let's consider an example of using each JOIN mode separately.

LEFT JOIN

Returns all rows from the left table and the matching rows from the right table. If there are no matches, it returns NULL (the output will be empty) for all columns of the right table. Example of using LEFT JOIN:

SELECT people.name, people.lastname, card.social_card_number
FROM people
LEFT JOIN social_card AS card
ON people.name = card.card_holder_name AND people.lastname = card.card_holder_lastname;

The result of executing an SQL query using LEFT JOIN without one record in the right table social_card:

 name   | lastname | social_card_number
---------+----------+--------------------
 John    | Doe      |          123456789
 Jane    | Smith    |          223456789
 Alice   | Johnson  |          323456789
 Bob     | Brown    |          423456789
 Charlie | Davis    |          523456789
 Eve     | Martin   |          623456789
 Frank   | White    |

RIGHT JOIN

Returns all rows from the right table and the matching rows from the left table. If there are no matches, it returns NULL for all columns of the left table. This type of JOIN is rarely used, as its functionality can be replaced by LEFT JOIN, and swapping the tables. Example of using RIGHT JOIN:

SELECT people.name, people.lastname, card.social_card_number
FROM people
RIGHT JOIN social_card AS card
ON people.name = card.card_holder_name AND people.lastname = card.card_holder_lastname;

The result of executing an SQL query using RIGHT JOIN without one record in the left table people:

 name   | lastname | social_card_number
---------+----------+--------------------
John    | Doe      |          123456789
Jane    | Smith    |          223456789
Alice   | Johnson  |          323456789
Bob     | Brown    |          423456789
Charlie | Davis    |          523456789
Eve     | Martin   |          623456789
        |          |          723456789

INNER JOIN

Returns rows when there are matching values in both tables. Excludes from the results those rows for which there are no matches in the joined tables. Example of using INNER JOIN:

SELECT people.name, people.lastname, card.social_card_number
FROM people
RIGHT JOIN social_card AS card
ON people.name = card.card_holder_name AND people.lastname = card.card_holder_lastname;

Such an SQL query will return only those rows for which there are matches in both tables:

 name   | lastname | social_card_number
---------+----------+--------------------
John    | Doe      |          123456789
Jane    | Smith    |          223456789
Alice   | Johnson  |          323456789
Bob     | Brown    |          423456789
Charlie | Davis    |          523456789
Eve     | Martin   |          623456789

CROSS JOIN

Returns the combined result of every row from the left table with every row from the right table. CROSS JOIN is usually used when all possible combinations of rows from two tables are needed. CROSS JOIN simply combines each row of one table with every row of another without any condition, which is why its syntax lacks the ON keyword: CROSS JOIN <table name> AS <table name alias>;.

Example of using CROSS JOIN with the result output limited by LIMIT 5:

SELECT people.name, people.lastname, card.social_card_number
FROM people
CROSS JOIN social_card AS card
LIMIT 5;

The example above will return all possible combinations of columns participating in the selection from the two tables:

name | lastname | social_card_number
------+----------+--------------------
 John | Doe      |          123456789
 John | Doe      |          223456789
 John | Doe      |          323456789
 John | Doe      |          423456789
 John | Doe      |          523456789

FULL JOIN

Returns both matched and unmatched rows in both tables, filling in NULL for columns from the table for which there is no match. Example of executing an SQL query using FULL JOIN:

SELECT people.name, people.lastname, card.social_card_number
FROM people
FULL JOIN social_card AS card
ON people.name = card.card_holder_name AND people.lastname = card.card_holder_lastname;

As a result of executing the SQL query, the following output will be returned:

 name   | lastname | social_card_number
---------+----------+--------------------
 Liam    | Martinez |         1323456789
 Eve     | Martin   |          623456789
 Hank    | Miller   |          923456789
 Molly   | Robinson |         1423456789
 Sam     | Walker   |
 Paul    | Harris   |         1723456789
 Kara    | Thompson |         1223456789
         |          |         1923456789
...