Select Distinct Command
The SELECT DISTINCT statement works much like a SELECT statement works, in that it selects data from a database. However, SELECT DISTINCT will only return distinct or different values, not duplicates.
This can be useful for getting a better idea of the range of values in a particular column.
Order By Clause
The ORDER BY keyword is used to sort the result-set by a specified column set of column. The ORDER BY keyword sorts the records in ascending (ASC) order by default. If you want to sort the records in a descending order, you can use the DESC keyword.
You can use multiple columns to sort, this will sort by state first, and for each state the rows will be sorted by name.
Limiting Rows Returned
SELECT commands can have the number of rows that the query returns limited using a special keyword. The keyword differs between database providers but the effect is the same, limiting the number of rows returned to a value that you specify.
|MS SQL Server/ MS Access||SELECT TOP value/percent|
Note that the SELECT TOP command is unique in that you can also specify a percentage value instead of an exact number of records.
|Oracle DB||ROWNUM <= value|
Note that the ROWNUM command is unique in that you can use it to identify the row number for any reason, not just limiting the number of rows returned.
In a SQL query, aliases are used to give columns or even tables a temporary name for that query. Simply place the keyword AS after a column or table, followed by the alias name. If the alias is two words, it needs to be encapsulated in single quotes.
This can be really useful when the table has complex column names.
This can also be useful when using multiple tables in a query, such as with a JOIN.
The UNION operator is used to combine the results of two different SELECT statements. This differs from a JOIN in that there does not have to be a relationship between columns. However, both SELECT statements need to select the same number of columns with similar data types in a similar order. So if my first statement selects an int column and then a string column, the second statement needs to do the same. The name of the columns in the resultset will take the name of the columns from the first SELECT in the UNION.
By default, the UNION operator will only select distinct values between the two tables.
To select all values from both tables, we can use UNION ALL instead.
Static values can be used in a UNION to help differentiate the rows from each table.