SQL SELECT

From NovaOrdis Knowledge Base
Jump to navigation Jump to search

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.

FROM

FROM [one or more places]

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

Subqueries