SUPPORT THE WORK

GetWiki

Select (SQL)

ARTICLE SUBJECTS
aesthetics  →
being  →
complexity  →
database  →
enterprise  →
ethics  →
fiction  →
history  →
internet  →
knowledge  →
language  →
licensing  →
linux  →
logic  →
method  →
news  →
perception  →
philosophy  →
policy  →
purpose  →
religion  →
science  →
sociology  →
software  →
truth  →
unix  →
wiki  →
ARTICLE TYPES
essay  →
feed  →
help  →
system  →
wiki  →
ARTICLE ORIGINS
critical  →
discussion  →
forked  →
imported  →
original  →
Select (SQL)
[ temporary import ]
please note:
- the content below is remote from Wikipedia
- it has been imported raw for GetWiki
The SQL SELECT statement returns a result set of records from one or more tables.WEB,weblink Transact-SQL Syntax Conventions, Microsoft, WEB,weblink SQL SELECT Syntax, MySQL, A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used data query language (DQL) command. As SQL is a declarative programming language, SELECT queries specify a result set, but do not specify how to calculate it. The database translates the query into a "query plan" which may vary between executions, database versions and database software. This functionality is called the "query optimizer" as it is responsible for finding the best possible execution plan for the query, within applicable constraints.The SELECT statement has many optional clauses:
  • WHERE specifies which rows to retrieve.
  • GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group.
  • HAVING selects among the groups defined by the GROUP BY clause.
  • ORDER BY specifies an order in which to return the rows.
  • AS provides an alias which can be used to temporarily rename tables or columns.

Examples{| class"wikitable" style"float: right; clear:right; margin: 1em" border"1"

!Table "T"!Query!Result
{| cellpadding="2" rules="all" style="border: 1px solid gray; text-align: center;"! C1 !! C2
| a
| b
{{code1=SELECT * FROM T;}}{| cellpadding="2" rules="all" style="border: 1px solid gray; text-align: center;"! C1 !! C2
| a
| b
{| cellpadding="2" rules="all" style="border: 1px solid gray; text-align: center;"! C1 !! C2
| a
| b
{{code1=SELECT C1 FROM T;}}{| cellpadding="2" rules="all" style="border: 1px solid gray; text-align: center;"! C1| 1
| 2
{| cellpadding="2" rules="all" style="border: 1px solid gray; text-align: center;"! C1 !! C2
| a
| b
{{code1=SELECT * FROM T WHERE C1 = 1;}}{| cellpadding="2" rules="all" style="border: 1px solid gray; text-align: center;"! C1 !! C2
| a
{| cellpadding="2" rules="all" style="border: 1px solid gray; text-align: center;"! C1 !! C2
| a
| b
{{code1=SELECT * FROM T ORDER BY C1 DESC;}}{| cellpadding="2" rules="all" style="border: 1px solid gray; text-align: center;"! C1 !! C2
| b
| a
Given a table T, the query {{code|2=sql|1=SELECT * FROM T}} will result in all the elements of all the rows of the table being shown.With the same table, the query {{code|2=sql|1=SELECT C1 FROM T}} will result in the elements from the column C1 of all the rows of the table being shown. This is similar to a projection in Relational algebra, except that in the general case, the result may contain duplicate rows. This is also known as a Vertical Partition in some database terms, restricting query output to view only specified fields or columns.With the same table, the query {{code|2=sql|1=SELECT * FROM T WHERE C1 = 1}} will result in all the elements of all the rows where the value of column C1 is '1' being shown â€” in Relational algebra terms, a selection will be performed, because of the WHERE clause. This is also known as a Horizontal Partition, restricting rows output by a query according to specified conditions.With more than one table, the result set will be every combination of rows. So if two tables are T1 and T2, {{code|2=sql|1=SELECT * FROM T1, T2}} will result in every combination of T1 rows with every T2 rows. E.g., if T1 has 3 rows and T2 has 5 rows, then 15 rows will result.The SELECT clause specifies a list of properties (columns) by name, or the wildcard character (“*”) to mean “all properties”.

Limiting result rows

Often it is convenient to indicate a maximum number of rows that are returned. This can be used for testing or to prevent consuming excessive resources if the query returns more information than expected. The approach to do this often varies per vendor.In ISO , result sets may be limited by using
  • cursors, or
  • By introducing SQL window function to the SELECT-statement
ISO introduced the FETCH FIRST clause.According to PostgreSQL v.9 documentation, an SQL Window function performs a calculation across a set of table rows that are somehow related to the current row, in a way similar to aggregate functions.PostgreSQL 9.1.24 Documentation - Chapter 3. Advanced FeaturesThe name recalls signal processing window functions. A window function call always contains an OVER clause.

ROW_NUMBER() window function

ROW_NUMBER() OVER may be used for a simple table on the returned rows, e.g. to return no more than ten rows:SELECT * FROM( SELECT
ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS row_number,
columns
FROM tablename
) AS fooWHERE row_number 20 FETCH FIRST 10 ROWS ONLYIBM DB2>DB2 (new rows are filtered after comparing with key column of table T)

Hierarchical query

Some databases provide specialised syntax for hierarchical data.A window function in is an aggregate function applied to a partition of the result set.For example,
sum(population) OVER( PARTITION BY city )
calculates the sum of the populations of all rows having the same city value as the current row.Partitions are specified using the OVER clause which modifies the aggregate. Syntax:
:: =
OVER ( [ PARTITION BY , ... ]
[ ORDER BY ] )
The OVER clause can partition and order the result set. Ordering is used for order-relative functions such as row_number.

Query evaluation ANSI

The processing of a SELECT statement according to ANSI SQL would be the following:Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan, Lubor Kollar, and Dejan Sarka{{ordered list|1=select g.*from users u inner join groups g on g.Userid = u.Useridwhere u.LastName = 'Smith'and u.FirstName = 'John'|2= the FROM clause is evaluated, a cross join or Cartesian product is produced for the first two tables in the FROM clause resulting in a virtual table as Vtable1|3= the ON clause is evaluated for vtable1; only records which meet the join condition g.Userid = u.Userid are inserted into Vtable2|4= If an outer join is specified, records which were dropped from vTable2 are added into VTable 3, for instance if the above query were:select u.*from users u left join groups g on g.Userid = u.Useridwhere u.LastName = 'Smith'and u.FirstName = 'John' all users who did not belong to any groups would be added back into Vtable3|5= the WHERE clause is evaluated, in this case only group information for user John Smith would be added to vTable4|6= the GROUP BY is evaluated; if the above query were:select g.GroupName, count(g.*) as NumberOfMembersfrom users u inner join groups g on g.Userid = u.Useridgroup by GroupNamevTable5 would consist of members returned from vTable4 arranged by the grouping, in this case the GroupName|7= the HAVING clause is evaluated for groups for which the HAVING clause is true and inserted into vTable6. For example:select g.GroupName, count(g.*) as NumberOfMembersfrom users u inner join groups g on g.Userid = u.Useridgroup by GroupNamehaving count(g.*) > 5|8= the SELECT list is evaluated and returned as Vtable 7|9= the DISTINCT clause is evaluated; duplicate rows are removed and returned as Vtable 8|10= the ORDER BY clause is evaluated, ordering the rows and returning VCursor9. This is a cursor and not a table because ANSI defines a cursor as an ordered set of rows (not relational).}}

Window function support by RDBMS vendors

The implementation of window function features by vendors of relational databases and SQL engines differs wildly. Apart from MySQL, most databases support at least some flavour of window functions. However, when we take a closer look it becomes clear that most vendors only implement a subset of the standard. Let's take the powerful RANGE clause as an example. Only Oracle, DB2, Spark/Hive, and Google Big Query fully implement this feature. More recently, vendors have added new extensions to the standard, e.g. array aggregation functions. These are particularly useful in the context of running SQL against a distributed file system (Hadoop, Spark, Google BigQuery) where we have weaker data co-locality guarantees than on a distributed relational database (MPP). Rather than evenly distributing the data across all nodes, SQL engines running queries against a distributed filesystem can achieve data co-locality guarantees by nesting data and thus avoiding potentially expensive joins involving heavy shuffling across the network. User-defined aggregate functions that can be used in window functions are another extremely powerful feature.

Generating data in T-SQL

Method to generate data based on the union allselect 1 a, 1 b union allselect 1, 2 union allselect 1, 3 union allselect 2, 1 union allselect 5, 1SQL Server 2008 supports the "row constructor" specified in the SQL3 ("SQL:1999") standardselect *from (values (1, 1), (1, 2), (1, 3), (2, 1), (5, 1)) as x(a, b)

References

{{Reflist}}

Sources

  • Horizontal & Vertical Partitioning, Microsoft SQL Server 2000 Books Online.

External links

{{SQL}}

- content above as imported from Wikipedia
- "Select (SQL)" does not exist on GetWiki (yet)
- time: 1:01am EDT - Sun, Jul 22 2018
[ this remote article is provided by Wikipedia ]
LATEST EDITS [ see all ]
GETWIKI 09 MAY 2016
GETWIKI 18 OCT 2015
M.R.M. Parrott
Biographies
GETWIKI 20 AUG 2014
GETWIKI 19 AUG 2014
GETWIKI 18 AUG 2014
Wikinfo
Culture
CONNECT