THE SELECT STATEMENT

THE SELECT STATEMENT

The SELECT statement is used to retrieve data from a database. A SELECT statement is also known as a query because it queries a database.

• Simple SELECT:

   SELECT *|<column> [AS <name>|"name"][,...] FROM [<schema>.]<table>; 

  
  

• Add an alias:

   SELECT [<alias>.]*|[<alias>.]<column> [,...] FROM [<schema>.]<table> [<alias>]; 

  
  

• Retrieve unique (DISTINCT) items:

   SELECT DISTINCT|UNIQUE [(]<column>[,...][)] FROM [<schema>.]<table>; 

  
  

The WHERE Clause

The optional WHERE clause is used to filter rows in a query, an UPDATE command, or a DELETE command.

• Simple comparison:

   SELECT * FROM [<schema>.]<table> [WHERE [<schema>.][[<table>.]|[<alias>.]]<column>     <comparison>[...]<column>]; 

  
  

• Conjunctive comparison using logical conditions:

   SELECT * FROM [<schema>.]<table> [WHERE [<schema>.][[<table>.]|[<alias>.]]<column>     <comparison>[...]<column> AND [NOT]| OR [NOT] [...]<column> <comparison>[...]<column> AND [NOT]| OR [NOT] ... ]; 

  
  

• The UPDATE and DELETE commands:

   UPDATE [<schema>.]<table> SET ... [WHERE [<schema>.][[<table>.]|[<alias>.]]<column>     <comparison>[...]<column> ...]; DELETE FROM [<schema>.]<table> [WHERE [<schema>.][[<table>.]|[<alias>.]]<column>     <comparison>[...]<column> ...]; 

  
  

The ORDER BY Clause

The optional ORDER BY clause is used to sort rows returned by a query, and it can also be a part of the OVER clause for analysis.

• Simple sorting:

   SELECT * FROM [<schema>.]<table> [WHERE] [ORDER BY {[<alias>.]<column>|<position>}[,...]]; 

  
  

• Ascending (the default) and descending sorts:

   SELECT * FROM [<schema>.]<table> [WHERE] [ORDER BY {[<alias>.]<column> [ASC|DESC]     |<position> [ASC|DESC]}[,...[ASC|DESC]]]; 

  
  

• Sorting NULL values (by default returned last):

   SELECT * FROM [<schema>.]<table> [WHERE] [ORDER BY {[<alias>.]<column> [ASC|DESC][NULLS {FIRST|LAST}]    |<position> [ASC|DESC][NULLS {FIRST|LAST}]}[,...[ASC|DESC]]]; 

  
  

• The OVER clause:

   SELECT {<column> [AS OVER() [ORDER BY]...]}[,...] FROM <table> [WHERE][ORDER BY]; 

  
  

The GROUP BY Clause

The optional GROUP BY clause is used to summarize, aggregate, and analyze groupings returned from queries. Any SELECT list elements not included in aggregation functions must be included in the GROUP BY list of elements. This includes both columns and expressions. At least one element of the SELECT list of elements must be subjected to an aggregation function.

• Simple GROUP BY:

   SELECT * FROM [<schema>.]<table> [WHERE] [GROUP BY [<alias>.]<column>[,...]][ORDER BY]; 

  
  

• Filtering groups with the optional HAVING clause (similar syntax to that of the WHERE clause):

   SELECT * FROM [<schema>.]<table> [WHERE] [GROUP BY ... [HAVING <column> <comparison> <column> [,...]]] [ORDER BY]; 

  
  

• Extending the GROUP BY clause with OLAP functionality using the ROLLBUP, CUBE, and GROUPING SETS clauses:

   SELECT * FROM [<schema>.]<table> [WHERE] [GROUP BY ... [HAVING ...] [ROLLUP(<column>,[...])|CUBE(<column> [,...])     |GROUPING SETS((<group>)[,...])][ORDER BY];