Select (SQL): Difference between revisions
m →Rows Pagination: <code> |
m Task 70: Update syntaxhighlight tags - remove use of deprecated <source> tags |
||
Line 126: | Line 126: | ||
<code>ROW_NUMBER() OVER</code> may be used for a ''simple table'' on the returned rows, e.g. to return no more than ten rows: |
<code>ROW_NUMBER() OVER</code> may be used for a ''simple table'' on the returned rows, e.g. to return no more than ten rows: |
||
< |
<syntaxhighlight lang="sql" highlight="3"> |
||
SELECT * FROM |
SELECT * FROM |
||
( SELECT |
( SELECT |
||
Line 135: | Line 135: | ||
WHERE row_number <= 10 |
WHERE row_number <= 10 |
||
</syntaxhighlight> |
|||
</source> |
|||
ROW_NUMBER can be [[Nondeterministic algorithm|non-deterministic]]: if ''sort_key'' is not unique, each time you run the query it is possible to get different row numbers assigned to any rows where ''sort_key'' is the same. When ''sort_key'' is unique, each row will always get a unique row number. |
ROW_NUMBER can be [[Nondeterministic algorithm|non-deterministic]]: if ''sort_key'' is not unique, each time you run the query it is possible to get different row numbers assigned to any rows where ''sort_key'' is the same. When ''sort_key'' is unique, each row will always get a unique row number. |
||
Line 143: | Line 143: | ||
The <code>RANK() OVER</code> window function acts like ROW_NUMBER, but may return more or less than ''n'' rows in case of tie conditions, e.g. to return the top-10 youngest persons: |
The <code>RANK() OVER</code> window function acts like ROW_NUMBER, but may return more or less than ''n'' rows in case of tie conditions, e.g. to return the top-10 youngest persons: |
||
< |
<syntaxhighlight lang="sql" highlight="3"> |
||
SELECT * FROM ( |
SELECT * FROM ( |
||
SELECT |
SELECT |
||
Line 153: | Line 153: | ||
) AS foo |
) AS foo |
||
WHERE ranking <= 10 |
WHERE ranking <= 10 |
||
</syntaxhighlight> |
|||
</source> |
|||
The above code could return more than ten rows, e.g. if there are two people of the same age, it could return eleven rows. |
The above code could return more than ten rows, e.g. if there are two people of the same age, it could return eleven rows. |
||
Line 161: | Line 161: | ||
Since ISO [[SQL:2008]] results limits can be specified as in the following example using the <code>FETCH FIRST</code> clause. |
Since ISO [[SQL:2008]] results limits can be specified as in the following example using the <code>FETCH FIRST</code> clause. |
||
< |
<syntaxhighlight lang="sql" highlight="2">SELECT * FROM T |
||
FETCH FIRST 10 ROWS ONLY</ |
FETCH FIRST 10 ROWS ONLY</syntaxhighlight> |
||
This clause currently is supported by CA DATACOM/DB 11, IBM DB2, SAP SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c and [[Mimer SQL]]. |
This clause currently is supported by CA DATACOM/DB 11, IBM DB2, SAP SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c and [[Mimer SQL]]. |
||
Line 168: | Line 168: | ||
Microsoft SQL Server 2008 and higher [https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017#using-offset-and-fetch-to-limit-the-rows-returned supports <code>FETCH FIRST</code>], but it is considered part of the <code>ORDER BY</code> clause. The <code>ORDER BY</code>, <code>OFFSET</code>, and <code>FETCH FIRST</code> clauses are all required for this usage. |
Microsoft SQL Server 2008 and higher [https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017#using-offset-and-fetch-to-limit-the-rows-returned supports <code>FETCH FIRST</code>], but it is considered part of the <code>ORDER BY</code> clause. The <code>ORDER BY</code>, <code>OFFSET</code>, and <code>FETCH FIRST</code> clauses are all required for this usage. |
||
< |
<syntaxhighlight lang="tsql" highlight="2">SELECT * FROM T |
||
ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY</ |
ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY</syntaxhighlight> |
||
=== Non-standard syntax === |
=== Non-standard syntax === |
||
Line 176: | Line 176: | ||
{|class="wikitable" |
{|class="wikitable" |
||
|- |
|- |
||
| < |
| <syntaxhighlight lang="tsql" highlight="1">SET ROWCOUNT 10 |
||
SELECT * FROM T</ |
SELECT * FROM T</syntaxhighlight> |
||
| [[Microsoft SQL Server|MS SQL Server]] (This also works on Microsoft SQL Server 6.5 while the '''Select top 10 * from T''' does not) |
| [[Microsoft SQL Server|MS SQL Server]] (This also works on Microsoft SQL Server 6.5 while the '''Select top 10 * from T''' does not) |
||
|- |
|- |
||
| < |
| <syntaxhighlight lang="postgres" highlight="2">SELECT * FROM T |
||
LIMIT 10 OFFSET 20</ |
LIMIT 10 OFFSET 20</syntaxhighlight> |
||
| [[Netezza]], [[MySQL]], [[MariaDB]], [[SQL Anywhere|SAP SQL Anywhere]], [[PostgreSQL]] (also supports the standard, since version 8.4), [[SQLite]], [[HSQLDB]], [[H2 (DBMS)|H2]], [[Vertica]], [[Polyhedra DBMS|Polyhedra]], [[Couchbase Server]], [[Snowflake Computing]], [[Virtuoso Universal Server|OpenLink Virtuoso]] |
| [[Netezza]], [[MySQL]], [[MariaDB]], [[SQL Anywhere|SAP SQL Anywhere]], [[PostgreSQL]] (also supports the standard, since version 8.4), [[SQLite]], [[HSQLDB]], [[H2 (DBMS)|H2]], [[Vertica]], [[Polyhedra DBMS|Polyhedra]], [[Couchbase Server]], [[Snowflake Computing]], [[Virtuoso Universal Server|OpenLink Virtuoso]] |
||
|- |
|- |
||
| < |
| <syntaxhighlight lang="sql" highlight="2">SELECT * from T |
||
WHERE ROWNUM <= 10</ |
WHERE ROWNUM <= 10</syntaxhighlight> |
||
| [[Oracle database|Oracle]] |
| [[Oracle database|Oracle]] |
||
|- |
|- |
||
Line 200: | Line 200: | ||
| [[Microsoft SQL Server|MS SQL Server]], [[Adaptive Server Enterprise|SAP ASE]], [[Microsoft Access|MS Access]], [[SAP IQ]], [[Teradata]] |
| [[Microsoft SQL Server|MS SQL Server]], [[Adaptive Server Enterprise|SAP ASE]], [[Microsoft Access|MS Access]], [[SAP IQ]], [[Teradata]] |
||
|- |
|- |
||
| < |
| <syntaxhighlight lang="sql" highlight="2">SELECT * FROM T |
||
SAMPLE 10</ |
SAMPLE 10</syntaxhighlight> |
||
| [[Teradata]] |
| [[Teradata]] |
||
|- |
|- |
||
Line 214: | Line 214: | ||
| [[Firebird (database server)|Firebird]] |
| [[Firebird (database server)|Firebird]] |
||
|- |
|- |
||
| < |
| <syntaxhighlight lang="sql" highlight="2">SELECT * FROM T |
||
ROWS 20 TO 30</ |
ROWS 20 TO 30</syntaxhighlight> |
||
| [[Firebird (database server)|Firebird]] (since version 2.1) |
| [[Firebird (database server)|Firebird]] (since version 2.1) |
||
|- |
|- |
||
| < |
| <syntaxhighlight lang="sql" highlight="2">SELECT * FROM T |
||
WHERE ID_T > 10 FETCH FIRST 10 ROWS ONLY</ |
WHERE ID_T > 10 FETCH FIRST 10 ROWS ONLY</syntaxhighlight> |
||
| [[IBM DB2|DB2]] |
| [[IBM DB2|DB2]] |
||
|- |
|- |
||
| < |
| <syntaxhighlight lang="sql" highlight="2">SELECT * FROM T |
||
WHERE ID_T > 20 FETCH FIRST 10 ROWS ONLY</ |
WHERE ID_T > 20 FETCH FIRST 10 ROWS ONLY</syntaxhighlight> |
||
| [[IBM DB2|DB2]] (new rows are filtered after comparing with key column of table T) |
| [[IBM DB2|DB2]] (new rows are filtered after comparing with key column of table T) |
||
|} |
|} |
||
Line 238: | Line 238: | ||
# Select all rows from the database |
# Select all rows from the database |
||
# Read all rows but send to display only when the row_number of the rows read is between <code>{begin_base_0 + 1}</code> and <code>{begin_base_0 + rows}</code> |
# Read all rows but send to display only when the row_number of the rows read is between <code>{begin_base_0 + 1}</code> and <code>{begin_base_0 + rows}</code> |
||
< |
<syntaxhighlight lang="sql">Select * |
||
from {table} |
from {table} |
||
order by {unique_key}</ |
order by {unique_key}</syntaxhighlight> |
||
==== Other simple method (a little more efficient than read all rows) ==== |
==== Other simple method (a little more efficient than read all rows) ==== |
||
Line 251: | Line 251: | ||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">select * |
||
from {table} |
from {table} |
||
order by {unique_key} |
order by {unique_key} |
||
FETCH FIRST {begin_base_0 + rows} ROWS ONLY</ |
FETCH FIRST {begin_base_0 + rows} ROWS ONLY</syntaxhighlight> |
||
| SQL ANSI 2008<br>Postgresql<br>SQL Server 2012<br>Derby<br>Oracle 12c<br>DB2 12 |
| SQL ANSI 2008<br>Postgresql<br>SQL Server 2012<br>Derby<br>Oracle 12c<br>DB2 12 |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="mysql">Select * |
||
from {table} |
from {table} |
||
order by {unique_key} |
order by {unique_key} |
||
LIMIT {begin_base_0 + rows}</ |
LIMIT {begin_base_0 + rows}</syntaxhighlight> |
||
| MySQL<br>SQLite |
| MySQL<br>SQLite |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">Select TOP {begin_base_0 + rows} * |
||
from {table} |
from {table} |
||
order by {unique_key}</ |
order by {unique_key}</syntaxhighlight> |
||
| SQL Server 2005 |
| SQL Server 2005 |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">SET ROWCOUNT {begin_base_0 + rows} |
||
Select * |
Select * |
||
from {table} |
from {table} |
||
order by {unique_key} |
order by {unique_key} |
||
SET ROWCOUNT 0</ |
SET ROWCOUNT 0</syntaxhighlight> |
||
| Sybase, SQL Server 2000 |
| Sybase, SQL Server 2000 |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="sql">Select * |
||
FROM ( |
FROM ( |
||
SELECT * |
SELECT * |
||
Line 285: | Line 285: | ||
ORDER BY {unique_key} |
ORDER BY {unique_key} |
||
) a |
) a |
||
where rownum <= {begin_base_0 + rows}</ |
where rownum <= {begin_base_0 + rows}</syntaxhighlight> |
||
| Oracle 11 |
| Oracle 11 |
||
|} |
|} |
||
Line 299: | Line 299: | ||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="postgres">Select * |
||
from {table} |
from {table} |
||
order by {unique_key} |
order by {unique_key} |
||
OFFSET {begin_base_0} ROWS |
OFFSET {begin_base_0} ROWS |
||
FETCH NEXT {rows} ROWS ONLY</ |
FETCH NEXT {rows} ROWS ONLY</syntaxhighlight> |
||
| SQL ANSI 2008<br>Postgresql<br>SQL Server 2012<br>Derby<br>Oracle 12c<br>DB2 12 |
| SQL ANSI 2008<br>Postgresql<br>SQL Server 2012<br>Derby<br>Oracle 12c<br>DB2 12 |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="postgres">Select * |
||
from {table} |
from {table} |
||
order by {unique_key} |
order by {unique_key} |
||
LIMIT {rows} OFFSET {begin_base_0}</ |
LIMIT {rows} OFFSET {begin_base_0}</syntaxhighlight> |
||
| MySQL<br>MariaDB<br>Postgresql<br>SQLite |
| MySQL<br>MariaDB<br>Postgresql<br>SQLite |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="mysql">Select * |
||
from {table} |
from {table} |
||
order by {unique_key} |
order by {unique_key} |
||
LIMIT {begin_base_0}, {rows}</ |
LIMIT {begin_base_0}, {rows}</syntaxhighlight> |
||
| MySQL<br>MariaDB<br>SQLite |
| MySQL<br>MariaDB<br>SQLite |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">select TOP {begin_base_0 + rows} |
||
*, _offset=identity(10) |
*, _offset=identity(10) |
||
into #temp |
into #temp |
||
Line 327: | Line 327: | ||
ORDER BY {unique_key} |
ORDER BY {unique_key} |
||
select * from #temp where _offset > {begin_base_0} |
select * from #temp where _offset > {begin_base_0} |
||
DROP TABLE #temp</ |
DROP TABLE #temp</syntaxhighlight> |
||
| Sybase 12.5.3: |
| Sybase 12.5.3: |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">SET ROWCOUNT {begin_base_0 + rows} |
||
select *, _offset=identity(10) |
select *, _offset=identity(10) |
||
into #temp |
into #temp |
||
Line 338: | Line 338: | ||
select * from #temp where _offset > {begin_base_0} |
select * from #temp where _offset > {begin_base_0} |
||
DROP TABLE #temp |
DROP TABLE #temp |
||
SET ROWCOUNT 0</ |
SET ROWCOUNT 0</syntaxhighlight> |
||
| Sybase 12.5.2: |
| Sybase 12.5.2: |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">select TOP {rows} * |
||
from ( |
from ( |
||
select *, ROW_NUMBER() over (order by {unique_key}) as _offset |
select *, ROW_NUMBER() over (order by {unique_key}) as _offset |
||
from {table} |
from {table} |
||
) xx |
) xx |
||
where _offset > {begin_base_0}</ |
where _offset > {begin_base_0}</syntaxhighlight> |
||
<br> |
<br> |
||
| SQL Server 2005 |
| SQL Server 2005 |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">SET ROWCOUNT {begin_base_0 + rows} |
||
select *, _offset=identity(int,1,1) |
select *, _offset=identity(int,1,1) |
||
into #temp |
into #temp |
||
Line 359: | Line 359: | ||
select * from #temp where _offset > {begin_base_0} |
select * from #temp where _offset > {begin_base_0} |
||
DROP TABLE #temp |
DROP TABLE #temp |
||
SET ROWCOUNT 0</ |
SET ROWCOUNT 0</syntaxhighlight> |
||
| SQL Server 2000 |
| SQL Server 2000 |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="sql">SELECT * FROM ( |
||
SELECT rownum-1 as _offset, a.* |
SELECT rownum-1 as _offset, a.* |
||
FROM( |
FROM( |
||
Line 372: | Line 372: | ||
WHERE rownum <= {begin_base_0 + cant_regs} |
WHERE rownum <= {begin_base_0 + cant_regs} |
||
) |
) |
||
WHERE _offset >= {begin_base_0}</ |
WHERE _offset >= {begin_base_0}</syntaxhighlight> |
||
| Oracle 11 |
| Oracle 11 |
||
|} |
|} |
||
Line 392: | Line 392: | ||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">select * |
||
from {table} |
from {table} |
||
order by {unique_key} |
order by {unique_key} |
||
FETCH FIRST {rows} ROWS ONLY</ |
FETCH FIRST {rows} ROWS ONLY</syntaxhighlight> |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">select * |
||
from {table} |
from {table} |
||
where {unique_key} > {last_val} |
where {unique_key} > {last_val} |
||
order by {unique_key} |
order by {unique_key} |
||
FETCH FIRST {rows} ROWS ONLY</ |
FETCH FIRST {rows} ROWS ONLY</syntaxhighlight> |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">select * |
||
from ( |
from ( |
||
Select * |
Select * |
||
Line 411: | Line 411: | ||
FETCH FIRST {rows} ROWS ONLY |
FETCH FIRST {rows} ROWS ONLY |
||
) a |
) a |
||
order by {unique_key}</ |
order by {unique_key}</syntaxhighlight> |
||
| SQL ANSI 2008<br>Postgresql<br>SQL Server 2012<br>Derby<br>Oracle 12c<br>DB2 12 |
| SQL ANSI 2008<br>Postgresql<br>SQL Server 2012<br>Derby<br>Oracle 12c<br>DB2 12 |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="mysql">select * |
||
from {table} |
from {table} |
||
order by {unique_key} |
order by {unique_key} |
||
LIMIT {rows}</ |
LIMIT {rows}</syntaxhighlight> |
||
| |
| |
||
< |
<syntaxhighlight lang="mysql">select * |
||
from {table} |
from {table} |
||
where {unique_key} > {last_val} |
where {unique_key} > {last_val} |
||
order by {unique_key} |
order by {unique_key} |
||
LIMIT {rows}</ |
LIMIT {rows}</syntaxhighlight> |
||
| |
| |
||
< |
<syntaxhighlight lang="mysql">select * |
||
from ( |
from ( |
||
select * |
select * |
||
Line 434: | Line 434: | ||
LIMIT {rows} |
LIMIT {rows} |
||
) a |
) a |
||
order by {unique_key}</ |
order by {unique_key}</syntaxhighlight> |
||
| MySQL<br>SQLite |
| MySQL<br>SQLite |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">select TOP {rows} * |
||
from {table} |
from {table} |
||
order by {unique_key}</ |
order by {unique_key}</syntaxhighlight> |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">select TOP {rows} * |
||
from {table} |
from {table} |
||
where {unique_key} > {last_val} |
where {unique_key} > {last_val} |
||
order by {unique_key}</ |
order by {unique_key}</syntaxhighlight> |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">select * |
||
from ( |
from ( |
||
select TOP {rows} * |
select TOP {rows} * |
||
Line 454: | Line 454: | ||
order by {unique_key} DESC |
order by {unique_key} DESC |
||
) a |
) a |
||
order by {unique_key}</ |
order by {unique_key}</syntaxhighlight> |
||
| SQL Server 2005 |
| SQL Server 2005 |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">SET ROWCOUNT {rows} |
||
select * |
select * |
||
from {table} |
from {table} |
||
order by {unique_key} |
order by {unique_key} |
||
SET ROWCOUNT 0</ |
SET ROWCOUNT 0</syntaxhighlight> |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">SET ROWCOUNT {rows} |
||
select * |
select * |
||
from {table} |
from {table} |
||
where {unique_key} > {last_val} |
where {unique_key} > {last_val} |
||
order by {unique_key} |
order by {unique_key} |
||
SET ROWCOUNT 0</ |
SET ROWCOUNT 0</syntaxhighlight> |
||
| |
| |
||
< |
<syntaxhighlight lang="tsql">SET ROWCOUNT {rows} |
||
select * |
select * |
||
from ( |
from ( |
||
Line 480: | Line 480: | ||
) a |
) a |
||
order by {unique_key} |
order by {unique_key} |
||
SET ROWCOUNT 0</ |
SET ROWCOUNT 0</syntaxhighlight> |
||
| Sybase, SQL Server 2000 |
| Sybase, SQL Server 2000 |
||
|- |
|- |
||
| |
| |
||
< |
<syntaxhighlight lang="sql">select * |
||
from ( |
from ( |
||
select * |
select * |
||
Line 490: | Line 490: | ||
order by {unique_key} |
order by {unique_key} |
||
) a |
) a |
||
where rownum <= {rows}</ |
where rownum <= {rows}</syntaxhighlight> |
||
| |
| |
||
< |
<syntaxhighlight lang="sql">select * |
||
from ( |
from ( |
||
select * |
select * |
||
Line 499: | Line 499: | ||
order by {unique_key} |
order by {unique_key} |
||
) a |
) a |
||
where rownum <= {rows}</ |
where rownum <= {rows}</syntaxhighlight> |
||
| |
| |
||
< |
<syntaxhighlight lang="sql">select * |
||
from ( |
from ( |
||
select * |
select * |
||
Line 512: | Line 512: | ||
where rownum <= {rows} |
where rownum <= {rows} |
||
) a2 |
) a2 |
||
order by {unique_key}</ |
order by {unique_key}</syntaxhighlight> |
||
| Oracle 11 |
| Oracle 11 |
||
|} |
|} |
||
Line 540: | Line 540: | ||
{{ordered list |
{{ordered list |
||
|1=< |
|1=<syntaxhighlight lang="sql"> |
||
select g.* |
select g.* |
||
from users u inner join groups g on g.Userid = u.Userid |
from users u inner join groups g on g.Userid = u.Userid |
||
where u.LastName = 'Smith' |
where u.LastName = 'Smith' |
||
and u.FirstName = 'John' |
and u.FirstName = 'John' |
||
</syntaxhighlight> |
|||
</source> |
|||
|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 |
|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 |
|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: |
|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: |
||
< |
<syntaxhighlight lang="sql"> |
||
select u.* |
select u.* |
||
from users u left join groups g on g.Userid = u.Userid |
from users u left join groups g on g.Userid = u.Userid |
||
where u.LastName = 'Smith' |
where u.LastName = 'Smith' |
||
and u.FirstName = 'John' </ |
and u.FirstName = 'John' </syntaxhighlight> |
||
all users who did not belong to any groups would be added back into Vtable3 |
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 |
|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: |
|6= the GROUP BY is evaluated; if the above query were: |
||
< |
<syntaxhighlight lang="sql"> |
||
select g.GroupName, count(g.*) as NumberOfMembers |
select g.GroupName, count(g.*) as NumberOfMembers |
||
from users u inner join groups g on g.Userid = u.Userid |
from users u inner join groups g on g.Userid = u.Userid |
||
group by GroupName |
group by GroupName |
||
</syntaxhighlight> |
|||
</source> |
|||
vTable5 would consist of members returned from vTable4 arranged by the grouping, in this case the GroupName |
vTable5 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: |
|7= the HAVING clause is evaluated for groups for which the HAVING clause is true and inserted into vTable6. For example: |
||
< |
<syntaxhighlight lang="sql"> |
||
select g.GroupName, count(g.*) as NumberOfMembers |
select g.GroupName, count(g.*) as NumberOfMembers |
||
from users u inner join groups g on g.Userid = u.Userid |
from users u inner join groups g on g.Userid = u.Userid |
||
group by GroupName |
group by GroupName |
||
having count(g.*) > 5 |
having count(g.*) > 5 |
||
</syntaxhighlight> |
|||
</source> |
|||
|8= the SELECT list is evaluated and returned as Vtable 7 |
|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 |
|9= the DISTINCT clause is evaluated; duplicate rows are removed and returned as Vtable 8 |
||
Line 582: | Line 582: | ||
==Generating data in T-SQL== |
==Generating data in T-SQL== |
||
Method to generate data based on the union all |
Method to generate data based on the union all |
||
< |
<syntaxhighlight lang="sql"> |
||
select 1 a, 1 b union all |
select 1 a, 1 b union all |
||
select 1, 2 union all |
select 1, 2 union all |
||
Line 588: | Line 588: | ||
select 2, 1 union all |
select 2, 1 union all |
||
select 5, 1 |
select 5, 1 |
||
</syntaxhighlight> |
|||
</source> |
|||
SQL Server 2008 supports the "row constructor" specified in the SQL3 ("SQL:1999") standard |
SQL Server 2008 supports the "row constructor" specified in the SQL3 ("SQL:1999") standard |
||
< |
<syntaxhighlight lang="sql"> |
||
select * |
select * |
||
from (values (1, 1), (1, 2), (1, 3), (2, 1), (5, 1)) as x(a, b) |
from (values (1, 1), (1, 2), (1, 3), (2, 1), (5, 1)) as x(a, b) |
||
</syntaxhighlight> |
|||
</source> |
|||
==References== |
==References== |
Revision as of 18:30, 11 May 2020
The SQL SELECT statement returns a result set of records from one or more tables.[1][2]
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 manipulation language (DML) 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:
FROM
specifies which table to get the data.[3]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
Table "T" | Query | Result | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
SELECT * FROM T;
|
| ||||||||||||
|
SELECT C1 FROM T;
|
| ||||||||||||
|
SELECT * FROM T WHERE C1 = 1;
|
| ||||||||||||
|
SELECT * FROM T ORDER BY C1 DESC;
|
| ||||||||||||
does not exist | SELECT 1+1, 3*2;
|
|
Given a table T, the query SELECT * FROM T
will result in all the elements of all the rows of the table being shown.
With the same table, the query 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 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, 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.
Although not in standard, most DBMS allows using a select clause without a table by pretending that an imaginary table with one row is used. This is mainly used to perform calculations where a table is not needed.
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 SQL:2003, result sets may be limited by using
- cursors, or
- by adding a SQL window function to the SELECT-statement
ISO SQL:2008 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. [4] The 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 foo
WHERE row_number <= 10
ROW_NUMBER can be non-deterministic: if sort_key is not unique, each time you run the query it is possible to get different row numbers assigned to any rows where sort_key is the same. When sort_key is unique, each row will always get a unique row number.
RANK() window function
The RANK() OVER
window function acts like ROW_NUMBER, but may return more or less than n rows in case of tie conditions, e.g. to return the top-10 youngest persons:
SELECT * FROM (
SELECT
RANK() OVER (ORDER BY age ASC) AS ranking,
person_id,
person_name,
age
FROM person
) AS foo
WHERE ranking <= 10
The above code could return more than ten rows, e.g. if there are two people of the same age, it could return eleven rows.
FETCH FIRST clause
Since ISO SQL:2008 results limits can be specified as in the following example using the FETCH FIRST
clause.
SELECT * FROM T
FETCH FIRST 10 ROWS ONLY
This clause currently is supported by CA DATACOM/DB 11, IBM DB2, SAP SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c and Mimer SQL.
Microsoft SQL Server 2008 and higher supports FETCH FIRST
, but it is considered part of the ORDER BY
clause. The ORDER BY
, OFFSET
, and FETCH FIRST
clauses are all required for this usage.
SELECT * FROM T
ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY
Non-standard syntax
Some DBMSs offer non-standard syntax either instead of or in addition to SQL standard syntax. Below, variants of the simple limit query for different DBMSes are listed:
SET ROWCOUNT 10
SELECT * FROM T
|
MS SQL Server (This also works on Microsoft SQL Server 6.5 while the Select top 10 * from T does not) |
SELECT * FROM T
LIMIT 10 OFFSET 20
|
Netezza, MySQL, MariaDB, SAP SQL Anywhere, PostgreSQL (also supports the standard, since version 8.4), SQLite, HSQLDB, H2, Vertica, Polyhedra, Couchbase Server, Snowflake Computing, OpenLink Virtuoso |
SELECT * from T
WHERE ROWNUM <= 10
|
Oracle |
SELECT FIRST 10 * from T
|
Ingres |
SELECT FIRST 10 * FROM T order by a
|
Informix |
SELECT SKIP 20 FIRST 10 * FROM T order by c, d
|
Informix (row numbers are filtered after order by is evaluated. SKIP clause was introduced in a v10.00.xC4 fixpack) |
SELECT TOP 10 * FROM T
|
MS SQL Server, SAP ASE, MS Access, SAP IQ, Teradata |
SELECT * FROM T
SAMPLE 10
|
Teradata |
SELECT TOP 20, 10 * FROM T
|
OpenLink Virtuoso (skips 20, delivers next 10)[5] |
SELECT TOP 10 START AT 20 * FROM T
|
SAP SQL Anywhere (also supports the standard, since version 9.0.1) |
SELECT FIRST 10 SKIP 20 * FROM T
|
Firebird |
SELECT * FROM T
ROWS 20 TO 30
|
Firebird (since version 2.1) |
SELECT * FROM T
WHERE ID_T > 10 FETCH FIRST 10 ROWS ONLY
|
DB2 |
SELECT * FROM T
WHERE ID_T > 20 FETCH FIRST 10 ROWS ONLY
|
DB2 (new rows are filtered after comparing with key column of table T) |
Rows Pagination
Rows Pagination [6] 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)
- Select all rows from the database
- 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)
- Select all the rows from the beginning of the table to the last row to display (
{begin_base_0 + rows}
) - 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}
SQL | Dialect |
---|---|
select *
from {table}
order by {unique_key}
FETCH FIRST {begin_base_0 + rows} ROWS ONLY
|
SQL ANSI 2008 Postgresql SQL Server 2012 Derby Oracle 12c DB2 12 |
Select *
from {table}
order by {unique_key}
LIMIT {begin_base_0 + rows}
|
MySQL SQLite |
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 + rows}
|
Oracle 11 |
Method with positioning
- Select only
{rows}
rows starting from the next row to display ({begin_base_0 + 1}
) - Read and send to display all the rows read from the database
SQL | Dialect |
---|---|
Select *
from {table}
order by {unique_key}
OFFSET {begin_base_0} ROWS
FETCH NEXT {rows} ROWS ONLY
|
SQL ANSI 2008 Postgresql SQL Server 2012 Derby Oracle 12c DB2 12 |
Select *
from {table}
order by {unique_key}
LIMIT {rows} OFFSET {begin_base_0}
|
MySQL MariaDB Postgresql SQLite |
Select *
from {table}
order by {unique_key}
LIMIT {begin_base_0}, {rows}
|
MySQL MariaDB SQLite |
select TOP {begin_base_0 + rows}
*, _offset=identity(10)
into #temp
from {table}
ORDER BY {unique_key}
select * from #temp where _offset > {begin_base_0}
DROP TABLE #temp
|
Sybase 12.5.3: |
SET ROWCOUNT {begin_base_0 + rows}
select *, _offset=identity(10)
into #temp
from {table}
ORDER BY {unique_key}
select * from #temp where _offset > {begin_base_0}
DROP TABLE #temp
SET 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 #temp
from {table}
ORDER BY {unique-key}
select * from #temp where _offset > {begin_base_0}
DROP TABLE #temp
SET 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 + cant_regs}
)
WHERE _offset >= {begin_base_0}
|
Oracle 11 |
Method with filter (it is more sophisticated but necessary for very big dataset)
- Select only then
{rows}
rows with filter:- First Page: select only the first
{rows}
rows, depending on the type of database - Next Page: select only the first
{rows}
rows, depending on the type of database, where the{unique_key}
is greater than{last_val}
(the value of the{unique_key}
of the last row in the current page) - 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 order
- First Page: select only the first
- Read and send to display all the rows read from the database
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 2008 Postgresql SQL Server 2012 Derby Oracle 12c DB2 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}
|
MySQL SQLite |
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 <= {rows}
|
select *
from (
select *
from {table}
where {unique_key} > {last_val}
order by {unique_key}
) a
where rownum <= {rows}
|
select *
from (
select *
from (
select *
from {table}
where {unique_key} < {first_val}
order by {unique_key} DESC
) a1
where rownum <= {rows}
) a2
order by {unique_key}
|
Oracle 11 |
Hierarchical query
Some databases provide specialised syntax for hierarchical data.
A window function in SQL:2003 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_CLAUSE> :: = OVER ( [ PARTITION BY <expr>, ... ] [ ORDER BY <expression> ] )
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:[7]
select g.* from users u inner join groups g on g.Userid = u.Userid where u.LastName = 'Smith' and u.FirstName = 'John'
- 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
- the ON clause is evaluated for vtable1; only records which meet the join condition g.Userid = u.Userid are inserted into Vtable2
- If an outer join is specified, records which were dropped from vTable2 are added into VTable 3, for instance if the above query were:
all users who did not belong to any groups would be added back into Vtable3
select u.* from users u left join groups g on g.Userid = u.Userid where u.LastName = 'Smith' and u.FirstName = 'John'
- the WHERE clause is evaluated, in this case only group information for user John Smith would be added to vTable4
- the GROUP BY is evaluated; if the above query were:
vTable5 would consist of members returned from vTable4 arranged by the grouping, in this case the GroupName
select g.GroupName, count(g.*) as NumberOfMembers from users u inner join groups g on g.Userid = u.Userid group by GroupName
- 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 NumberOfMembers from users u inner join groups g on g.Userid = u.Userid group by GroupName having count(g.*) > 5
- the SELECT list is evaluated and returned as Vtable 7
- the DISTINCT clause is evaluated; duplicate rows are removed and returned as Vtable 8
- 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. 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 all
select 1 a, 1 b union all
select 1, 2 union all
select 1, 3 union all
select 2, 1 union all
select 5, 1
SQL Server 2008 supports the "row constructor" specified in the SQL3 ("SQL:1999") standard
select *
from (values (1, 1), (1, 2), (1, 3), (2, 1), (5, 1)) as x(a, b)
References
- ^ Microsoft. "Transact-SQL Syntax Conventions".
- ^ MySQL. "SQL SELECT Syntax".
- ^ Omitting FROM clause isn't standard, but allowed by most major DBMSes.
- ^ PostgreSQL 9.1.24 Documentation - Chapter 3. Advanced Features
- ^ OpenLink Software. "9.19.10. The TOP SELECT Option". docs.openlinksw.com. Retrieved 1 October 2019.
- ^ Ing. Óscar Bonilla, MBA
- ^ Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan, Lubor Kollar, and Dejan Sarka
Sources
- Horizontal & Vertical Partitioning, Microsoft SQL Server 2000 Books Online.