SQL SELECT

Internal

• SQL
• SQL WHERE

Overview

A query consists in at least one (SELECT) and at most six categories of clauses:

SELECT [one or more things] 
FROM [one or more places] 
WHERE [one or more conditions apply] 
GROUP BY [...] HAVING [...]
ORDER BY [...];

Almost every query will include at least three of these clauses (SELECT, FROM and WHERE).

Once submitted to the server, the server verifies the syntax and passes the query to the query optimizer to be executed. The optimizer look at such things as the order in which to join tables, what indexes are available, etc. and the picks an execution plan.

The execution consists in selecting all rows, possibly across multiple tables, then filtering and discarding the rows that do not match the filter conditions specified in the WHERE clause, if present.

Upon execution, an SQL query returns a result set, which is just another table containing rows and columns.

Example

SELECT id, name FROM person WHERE id = 1;

The following query:

SELECT;

is valid, it returns one empty row.

Clauses

SELECT

SELECT [one or more things] ...

SELECT determines which columns from the FROM sources need to be retrieved and included in the result set. Even though SELECT clause is the first in the statement, it is one of the last clauses to be evaluated. This is because before the server can determine what to include in the final result set, it needs to know all of the possible columns that could be included, which are determined by the content of the FROM clause. A very obvious example is SELECT * FROM person;: only the person table columns can be included.

All columns can be specified with an *:

SELECT * FROM person;

Specific columns can be named:

SELECT id, name FROM person;

Aside from column names, SELECT accepts literals, such as numbers or strings, expressions, built-in function calls and user-defined function calls:

SELECT name, '@@@', id + 5, UPPER(name) FROM person;

  name   | ?column? | ?column? |  upper
---------+----------+----------+---------
 Alice   | @@@      |        6 | ALICE
 ...

Column Aliases

In each of the cases mentioned above, the label of the "synthetic" column such generated can be specified after the literal/expression/function. These labels are known as column aliases:

/*
   'original_name', 'shifted_id' and 'upper_name. are column aliases.
*/
SELECT name original_name, '@@@' some_prefix, id + 5 shifted_id, UPPER(name) upper_name FROM person;

 original_name | some_prefix | shifted_id | upper_name
---------------+-------------+------------+------------
 Alice         | @@@         |          6 | ALICE
 ...

In order to make your column aliases stand out even more, the AS keyword can be optionally used before the alias name:

SELECT name AS original_name, '@@@' AS some_prefix, id + 5 AS shifted_id, UPPER(name) AS upper_name FROM person;

DISTINCT

The DISTINCT keyword specified after SELECT removes duplicates.

SELECT DISTINCT eye_color FROM person;

Note that generating a distinct set of results requires data to be sorted, which may have performance implication for large result sets.

Analyze situations. What if there are multiple columns?

Conditional SELECT

SQL Conditional Logic

FROM

... FROM [one or more places] ...

FROM identifies the tables from which to retrieve data, and, if there are more than one table, how the tables should be joined. The most common situation involves just one table:

SELECT person.name FROM person;

The tables used in the FROM clause are not necessarily permanent tables, they can also be derived tables, temporary tables or virtual tables (views). When a query is issued against a view, the query is merged with the view definition to create a final query to be executed.

Table Aliases

Each of the tables provided in the FROM clause can aliased within the context of the query with a table alias, introduced by the keyword AS. The table aliases can then be used to prefix the column names used by SELECT clause. This feature is useful to disambiguate between columns with the same name in different tables.

SELECT pers.name FROM person AS pers;

Table aliases can also be used with join tables.

Subqueries (Querying a Derived Table)

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch09.html

Derived tables are dynamically generated in memory by subqueries, which are complete SELECT statements declared inside the FROM clause of a main query (or containing query). When the main query executes, these tables are created in memory and then "queried" by the main query as it were regular tables:

SELECT specific_person.name FROM 
(
  SELECT id, name, birthday FROM person WHERE id = 1
) AS specific_person;

Every time a subquery is used, the subquery must be identified with an alias, prefixed by the optional keyword AS.

Subqueries can be used in joins. An example is provided here:

Subquery in Join

Joins (Querying Multiple Tables)

Querying Multiple Tables

WHERE

The WHERE Clause

GROUP BY ... HAVING

The GROUP BY ... HAVING Clauses

ORDER BY

ORDER BY clause sorts the rows of the final result set by one or more columns, using either raw column data or expressions based on columns:

SELECT name FROM person ORDER BY name;

By default, the sorting is in ascending order. ASC can be specified but it is redundant. To sort in descending order, use the keyword DESC:

SELECT ... ORDER BY ... DESC;

ORDER BY works with column names or with their numeric placeholder.

SELECT ... ORDER BY 3;