SQL SELECT: Difference between revisions
(→SELECT) |
|||
(27 intermediate revisions by the same user not shown) | |||
Line 15: | Line 15: | ||
Almost every query will include at least three of these clauses (<code>[[#SELECT|SELECT]]</code>, <code>[[#FROM|FROM]]</code> and <code>[[#WHERE|WHERE]]</code>). | 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. | |||
=Example= | =Example= | ||
Line 37: | Line 37: | ||
SELECT <font color=teal>[one or more things]</font> ... | SELECT <font color=teal>[one or more things]</font> ... | ||
</font> | </font> | ||
<code>SELECT</code> | <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. | ||
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. 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''': | |||
<syntaxhighlight lang='sql'> | |||
/* prefix, 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=-2> | |||
original_name | some_prefix | shifted_id | upper_name | |||
---------------+-------------+------------+-------------- | |||
Binh Ngo Jr. | @@@ | 6 | BINH NGO JR. | |||
</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> | |||
==<tt>FROM</tt>== | ==<tt>FROM</tt>== | ||
<font> | <font> | ||
FROM <font color=teal>[one or more places]</font> | ... FROM <font color=teal>[one or more places]</font> ... | ||
</font> | </font> | ||
<code>FROM</code> identifies the tables from which to retrieve data, and how the tables should be [[SQL_Querying_Multiple_Tables#Overview|joined]]. | |||
The "places" we select from can be: | The "places" we select from can be: | ||
Line 60: | Line 92: | ||
==<tt>GROUP BY ... HAVING</tt>== | ==<tt>GROUP BY ... HAVING</tt>== | ||
{{Internal|SQL_GROUP_BY#Overview|<tt>GROUP BY ... HAVING</tt>}} | {{Internal|SQL_GROUP_BY#Overview|The <tt>GROUP BY ... HAVING</tt> Clauses}} | ||
==<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. | |||
=Subqueries= | =Subqueries= |
Revision as of 01:04, 23 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. 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:
/* prefix, 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 ---------------+-------------+------------+-------------- Binh Ngo Jr. | @@@ | 6 | BINH NGO JR.
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?
FROM
... FROM [one or more places] ...
FROM
identifies the tables from which to retrieve data, and how the tables should be joined.
The "places" we select from can be:
- permanent
- derived
- temporary
- virtual(view)
Table alias.
Querying Multiple Tables
WHERE
GROUP BY ... HAVING
ORDER BY
ORDER BY
clause sorts the rows of the final result set by one or more columns.