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)

Rows Pagination

Rows Pagination Ing. Óscar Bonilla, MBA is an approach used to limit and display only a part of the total data of a query in the database. Instead of showing hundreds or thousands of rows at the same time, the server is requested only one page (a limited set of rows, per example only 10 rows), and the user starts navigating by requesting the next page, and then the next one, and so on. It is very useful, specially in web systems, where there is no dedicated connection between the client and the server, so the client does not have to wait to read and display all the rows of the server.Data in Pagination approach:
  • {rows} = Number of rows in a page
  • {page_number} = Number of the current page
  • {begin_base_0} = Number of the row - 1 where the page starts = (page_number-1) rows
Simplest method (but very inefficient):1) Select all rows from the database2) Read all rows but send to display only when the row_number of the rows read is between {begin_base_0 + 1} and {begin_base_0 + rows}Select * from {table} order by {unique_key}Other simple method (a little more efficient than read all rows):1) Select all the rows from the beginning of the table to the last row to display ({begin_base_0 + rows})2) Read the {begin_base_0 + rows} rows but send to display only when the row_number of the rows read is greater than {begin_base_0}{|class="wikitable"| SQL| Dialect
| select *from {table}order by {unique_key}FETCH FIRST {begin_base_0 + rows} ROWS ONLY| SQL ANSI 2008PostgresqlSQL Server 2012DerbyOracle 12cDB2 12
|Select *from {table}order by {unique_key}LIMIT {begin_base_0 + rows}| MySQLSQLite
|Select TOP {begin_base_0 + rows} * from {table} order by {unique_key}| SQL Server 2005
|SET ROWCOUNT {begin_base_0 + rows}Select * from {table} order by {unique_key}SET ROWCOUNT 0| Sybase, SQL Server 2000
|Select *
FROM (
SELECT *
FROM {table}
ORDER BY {unique_key}
) a
where rownum {begin_base_0}DROP TABLE #temp| Sybase 12.5.3:
|SET ROWCOUNT {begin_base_0 + rows}select *, _offset=identity(10) into #tempfrom {table}ORDER BY {unique_key} select * from #temp where _offset > {begin_base_0}DROP TABLE #tempSET ROWCOUNT 0| Sybase 12.5.2:
|select TOP {rows} * from (
select *, ROW_NUMBER() over (order by {unique_key}) as _offset
from {table}
) xx where _offset > {begin_base_0}| SQL Server 2005
|SET ROWCOUNT {begin_base_0 + rows}select *, _offset=identity(int,1,1) into #tempfrom {table}ORDER BY {unique-key}select * from #temp where _offset > {begin_base_0}DROP TABLE #tempSET ROWCOUNT 0| SQL Server 2000
|SELECT * FROM (
SELECT rownum-1 as _offset, a.*
FROM(
SELECT *
FROM {table}
ORDER BY {unique_key}
) a
WHERE rownum = {begin_base_0}| Oracle 11
Method with filter (it is more sophisticated but necessary for very big dataset):1) Select only then rows with filter:1.1) First Page: select only the first {rows} rows, depending on the type of database1.2) Next Page: select only the first {rows} rows, depending on the type of database, where the {unique_key} is grater than {last_val} (the value of the {unique_key} of the last row in the current page)1.3) Previous Page: sort the data in the reverse order, select only the first {rows} rows, where the {unique_key} is less than {first_val} (the value of the {unique_key} of the first row in the current page), and sort the result in the correct order2) Read and send to display all the rows read from the database{|class="wikitable"| First Page| Next Page| Previous Page| Dialect
| select *from {table} order by {unique_key}FETCH FIRST {rows} ROWS ONLY| select * from {table} where {unique_key} > {last_val}order by {unique_key}FETCH FIRST {rows} ROWS ONLY|
select *
from (
Select *
from {table}
where {unique_key} < {first_val}
order by {unique_key} DESC
FETCH FIRST {rows} ROWS ONLY
) a
order by {unique_key}| SQL ANSI 2008PostgresqlSQL Server 2012DerbyOracle 12cDB2 12
|select *from {table}order by {unique_key}LIMIT {rows}| select * from {table} where {unique_key} > {last_val}order by {unique_key}LIMIT {rows}|
select *
from (
select *
from {table}
where {unique_key} < {first_val}
order by {unique_key} DESC
LIMIT {rows}
) a
order by {unique_key}| MySQLSQLite
| select TOP {rows} * from {table} order by {unique_key}| select TOP {rows} * from {table} where {unique_key} > {last_val}order by {unique_key}|
select *
from (
select TOP {rows} *
from {table}
where {unique_key} < {first_val}
order by {unique_key} DESC
) a
order by {unique_key}| SQL Server 2005
| SET ROWCOUNT {rows}select *from {table} order by {unique_key}SET ROWCOUNT 0| SET ROWCOUNT {rows}select *from {table} where {unique_key} > {last_val}order by {unique_key}SET ROWCOUNT 0|
SET ROWCOUNT {rows}
select *
from (
select *
from {table}
where {unique_key} < {first_val}
order by {unique_key} DESC
) a
order by {unique_key}
SET ROWCOUNT 0| Sybase, SQL Server 2000
| select *from (
select *
from {table}
order by {unique_key}
) a
where rownum {last_val}
order by {unique_key}
) a where rownum

- content above as imported from Wikipedia
- "Select (SQL)" does not exist on GetWiki (yet)
- time: 4:21am EST - Fri, Feb 22 2019
[ 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