SQL SELECT: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(36 intermediate revisions by the same user not shown)
Line 41: Line 41:
All columns can be specified with an <code>*</code>:
All columns can be specified with an <code>*</code>:
<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
SELECT * FROM person
SELECT * FROM person;
</syntaxhighlight>
</syntaxhighlight>
Specific columns can be named:
Specific columns can be named:
<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
SELECT id, name FROM person
SELECT id, name FROM person;
</syntaxhighlight>
</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. In each of these cases, the label of the "synthetic" column such generated can be specified after the literal/expression/function. These labels are known as '''column aliases''':
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'>
<syntaxhighlight lang='sql'>
/* prefix, shifted_id and upper_name are column aliases */
SELECT name, '@@@', id + 5, UPPER(name) FROM person;
SELECT name original_name, '@@@' some_prefix, id + 5 shifted_id, UPPER(name) upper_name FROM person  
</syntaxhighlight>
</syntaxhighlight>
<font size=-2>
<font size=-1.5>
   original_name | some_prefix | shifted_id | upper_name
  name  | ?column? | ?column? |  upper
  ---------------+-------------+------------+--------------
---------+----------+----------+---------
   Binh Ngo Jr.  | @@@        |          6 | BINH NGO JR.
  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>
</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:
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'>
<syntaxhighlight lang='sql'>
SELECT name AS original_name, '@@@' AS some_prefix, id + 5 AS shifted_id, UPPER(name) AS upper_name FROM person  
SELECT name AS original_name, '@@@' AS some_prefix, id + 5 AS shifted_id, UPPER(name) AS upper_name FROM person;
</syntaxhighlight>
</syntaxhighlight>
===<tt>DISTINCT</tt>===
===<tt>DISTINCT</tt>===
The <code>DISTINCT</code> keyword specified after <code>SELECT</code> removes duplicates.  
The <code>DISTINCT</code> keyword specified after <code>SELECT</code> removes duplicates.  
Line 69: Line 85:


<font color=darkkhaki>Analyze situations. What if there are multiple columns?</font>
<font color=darkkhaki>Analyze situations. What if there are multiple columns?</font>
===Conditional <tt>SELECT</tt>===
{{Internal|SQL_Conditional_Logic#Overview|SQL Conditional Logic}}


==<tt>FROM</tt>==
==<tt>FROM</tt>==
Line 75: Line 93:
</font>
</font>


<code>FROM</code> identifies the tables from which to retrieve data, and, if there are more than one tables, how the tables should be [[SQL_Querying_Multiple_Tables#Overview|joined]]. The most common situation involves just one table:
<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'>
<syntaxhighlight lang='sql'>
SELECT p.name FROM person AS p;
SELECT person.name FROM person;
</syntaxhighlight>
</syntaxhighlight>


Each of the tables provided in the <code>FROM</code> clause can be renamed within the context of the query with a '''table alias''', introduced by the keyword <code>AS</code>. The table aliases can then also 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.
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.


The tables are not necessarily [[SQL#Permanent_Table|permanent tables]], they can also be [[SQL#Derived_Table|derived]], [[SQL#Temporary_Table|temporary]] or [[SQL#Virtual_Table_(View)|virtual (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===


===Querying a Derived Table===
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.
When a subquery defined under the <code>FROM</code> clause is executed, the execution creates a [[SQL#Derived_Table|derived table]] in memory, which is then used by main query as it were a regular table.
 
<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:


<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
Line 95: Line 123:
</syntaxhighlight>
</syntaxhighlight>


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


===Querying Multiple Tables===
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}}


Line 107: Line 137:


==<tt>ORDER BY</tt>==
==<tt>ORDER BY</tt>==
<code>ORDER BY</code> clause sorts the rows of the final result set by one or more columns.
<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:


=Subqueries=
<syntaxhighlight lang='sql'>
SELECT name FROM person ORDER BY name;
</syntaxhighlight>
 
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>
 
<code>ORDER BY</code> works with column names or with their numeric placeholder.<syntaxhighlight lang='sql'>
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;