SQL SELECT: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(100 intermediate revisions by the same user not shown)
Line 4: Line 4:


=Overview=
=Overview=
A query consists in at least one (<code>[[#SELECT|SELECT]]</code>) and at most six categories of [[#Clauses|clauses]]:
<font>
[[#SELECT|SELECT]] <font color=teal>[one or more things]</font>
[[#FROM|FROM]] <font color=teal>[one or more places]</font>
[[#WHERE|WHERE]] <font color=teal>[one or more conditions apply]</font>
[[SQL_GROUP_BY#Overview|GROUP BY]] [...] [[SQL_GROUP_BY#Overview|HAVING]] [...]
[[#ORDER_BY|ORDER BY]] [...];
</font>
Almost every query will include at least three of these clauses (<code>[[#SELECT|SELECT]]</code>, <code>[[#FROM|FROM]]</code> and <code>[[#WHERE|WHERE]]</code>).
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 [[SQL_WHERE#Conditions|filter conditions]] specified in the <code>[[SQL_WHERE#Overview|WHERE]]</code> clause, if present.
Upon execution, an SQL query returns a [[SQL#Result_Set|result set]], which is just another table containing rows and columns.


Upon execution, an SQL query returns a [[SQL#Result_Set|result set]].
=Example=


Query execution. SQL optimizer. Subqueries.
<syntaxhighlight lang='sql'>
SELECT id, name FROM person WHERE id = 1;
</syntaxhighlight>
The following query:
<syntaxhighlight lang='sql'>
SELECT;
</syntaxhighlight>
is valid, it returns one empty row.


=Clauses=
==<tt>SELECT</tt>==
<font>
<font>
  SELECT FROM WHERE GROUP BY HAVING ORDER BY
  SELECT <font color=teal>[one or more things]</font> ...
</font>
</font>
<code>SELECT</code> determines which columns from the <code>[[#FROM|FROM]]</code> sources need to be retrieved and included in the result set. Even though <code>SELECT</code> 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 <code>[[#FROM|FROM]]</code> clause. A very obvious example is <code>SELECT * FROM person;</code>: only the <code>person</code> table columns can be included.


Select all (possibly across multiple tables) then filter and discard with where.
All columns can be specified with an <code>*</code>:
<syntaxhighlight lang='sql'>
SELECT * FROM person;
</syntaxhighlight>
Specific columns can be named:
<syntaxhighlight lang='sql'>
SELECT id, name FROM person;
</syntaxhighlight>
Aside from column names, <code>SELECT</code> accepts literals, such as numbers or strings, expressions, built-in function calls and user-defined function calls:
<syntaxhighlight lang='sql'>
SELECT name, '@@@', id + 5, UPPER(name) FROM person;
</syntaxhighlight>
<font size=-1.5>
  name  | ?column? | ?column? |  upper
---------+----------+----------+---------
  Alice  | @@@      |        6 | ALICE
  ...
</font>
 
===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''':
<syntaxhighlight lang='sql'>
/*
  '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;
</syntaxhighlight>
<font size=-1.5>
  original_name | some_prefix | shifted_id | upper_name
---------------+-------------+------------+------------
  Alice        | @@@        |          6 | ALICE
  ...
</font>
In order to make your column aliases stand out even more, the <code>AS</code> keyword can be optionally used before the alias name:
<syntaxhighlight lang='sql'>
SELECT name AS original_name, '@@@' AS some_prefix, id + 5 AS shifted_id, UPPER(name) AS upper_name FROM person;
</syntaxhighlight>
 
===<tt>DISTINCT</tt>===
The <code>DISTINCT</code> keyword specified after <code>SELECT</code> removes duplicates.
<syntaxhighlight lang='sql'>
SELECT DISTINCT eye_color FROM person;
</syntaxhighlight>
Note that generating a distinct set of results requires data to be sorted, which may have performance implication for large result sets.
 
<font color=darkkhaki>Analyze situations. What if there are multiple columns?</font>
===Conditional <tt>SELECT</tt>===
{{Internal|SQL_Conditional_Logic#Overview|SQL Conditional Logic}}


=Clauses=
==<tt>FROM</tt>==
==<tt>FROM</tt>==
<font>
<font>
  FROM permanent|derived|temporary|virtual(view)
  ... FROM <font color=teal>[one or more places]</font> ...
</font>
</font>


Table alias.
<code>FROM</code> identifies the tables from which to retrieve data, and, if there are more than one table, how the tables should be [[SQL_Querying_Multiple_Tables#Overview|joined]]. The most common situation involves just one table:
 
<syntaxhighlight lang='sql'>
SELECT person.name FROM person;
</syntaxhighlight>
 
The tables used in the <code>FROM</code> clause are not necessarily [[SQL#Permanent_Table|permanent tables]], they can also be [[SQL#Derived_Table|derived]] tables, [[SQL#Temporary_Table|temporary]] tables or [[SQL#Virtual_Table_(View)|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 <code>FROM</code> clause can aliased within the context of the query with a '''table alias''', introduced by the keyword <code>AS</code>. The table aliases can then be used to prefix the column names used by <code>SELECT</code> clause. This feature is useful to disambiguate between columns with the same name in different tables.
 
<syntaxhighlight lang='sql'>
SELECT pers.name FROM person AS pers;
</syntaxhighlight>
 
Table aliases can also be used with [[SQL_Querying_Multiple_Tables#Using_Table_Aliases_in_Joins|join tables]].
 
===<span id='Subquery'></span><span id='Querying_a_Derived_Table'></span>Subqueries (Querying a Derived Table)===
<font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch09.html</font>
 
[[SQL#Derived_Table|Derived tables]] are dynamically generated in memory by '''subqueries''', which are complete <code>SELECT</code> statements declared inside the <code>FROM</code> clause of a '''main query''' <span id='Containing_Query'></span>(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:


===Querying Multiple Tables===
<syntaxhighlight lang='sql'>
SELECT specific_person.name FROM
(
  SELECT id, name, birthday FROM person WHERE id = 1
) AS specific_person;
</syntaxhighlight>
 
Every time a subquery is used, the subquery must be identified with an alias, prefixed by the optional keyword <code>AS</code>.
 
Subqueries can be used in [[SQL_Querying_Multiple_Tables#Join|joins]]. An example is provided here: {{Internal|SQL_Querying_Multiple_Tables#Subquery_in_Join|Subquery in Join}}
 
===<span id='Querying_Multiple_Tables'></span>Joins (Querying Multiple Tables)===
{{Internal|SQL_Querying_Multiple_Tables#Overview|Querying Multiple Tables}}
{{Internal|SQL_Querying_Multiple_Tables#Overview|Querying Multiple Tables}}


==<tt>WHERE</tt>==
==<tt>WHERE</tt>==
{{Internal|SQL_WHERE#Overview|The <tt>WHERE</tt> Clause}}


Filters, conditions, condition evaluation.
==<tt>GROUP BY ... HAVING</tt>==
{{Internal|SQL_GROUP_BY#Overview|The <tt>GROUP BY ... HAVING</tt> Clauses}}


==<tt>GROUP BY ... HAVING</tt>==
==<tt>ORDER BY</tt>==
<code>ORDER BY</code> clause sorts the rows of the final result set by one or more columns, using either raw column data or expressions based on columns:
 
<syntaxhighlight lang='sql'>
SELECT name FROM person ORDER BY name;
</syntaxhighlight>


(make sure I understand thoroughly and link)
By default, the sorting is in ascending order. <code>ASC</code> can be specified but it is redundant. To sort in descending order, use the keyword <code>DESC</code>:
<syntaxhighlight lang='sql'>
SELECT ... ORDER BY ... DESC;
</syntaxhighlight>


==<tt>ORDER BY</tt>==
<code>ORDER BY</code> works with column names or with their numeric placeholder.<syntaxhighlight lang='sql'>
=Subqueries=
SELECT ... ORDER BY 3;
</syntaxhighlight>

Latest revision as of 21:09, 30 May 2024

Internal

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;