SQL SELECT: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
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> 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.
<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.


==<tt>FROM</tt>==
==<tt>FROM</tt>==

Revision as of 00:39, 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.

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

Querying Multiple Tables

WHERE

The WHERE Clause

GROUP BY ... HAVING

GROUP BY ... HAVING

ORDER BY

ORDER BY clause sorts the rows of the final result set by one or more columns.

Subqueries