SQL Select Options
While the SELECT command in its basic form can be very simple to use, the SELECT statement can be used with other statements or in certain ways that allow you to bring in exactly the data you need.
Selecting Static Values
Static values can be inserted into a resultset returned from a SELECT query as another column. Simply use the static value as a column to select, and the query will return a column where the name of the column is the static value, and every row in that column will return that same static value.
SELECT column1, column2, 10 FROM table
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.
SELECT DISTINCT column FROM table
Examples
This can be useful for getting a better idea of the range of values in a particular column.
SELECT DISTINCT country FROM Customers
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.
SELECT column1, column2 FROM table ORDER BY column2 DESC
Examples
SELECT * FROM Customers ORDER BY Name ASC
You can use multiple columns to sort, this will sort by state first, and for each state the rows will be sorted by name.
SELECT * FROM Customers ORDER BY State ASC, Name DESC
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.
| Database | Keyword | Example |
|---|---|---|
| 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. |
| MySQL | Limit value | |
| 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. |
Aliases
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.
SELECT column1 AS a, column2 AS 'b c' FROM table AS t
Examples
This can be really useful when the table has complex column names.
SELECT id AS 'Badge Number', name AS 'Employee Name', dob AS Birthday FROM employees
This can also be useful when using multiple tables in a query, such as with a JOIN.
SELECT * FROM Contacts AS co JOIN Customers AS cu ON cu.ID = co.CustomerID
Union Command
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.
SELECT stringCol, intCol FROM table1
UNION
SELECT stringCol, intCol FROM table2
Examples
By default, the UNION operator will only select distinct values between the two tables.
SELECT username FROM users
UNION
SELECT name FROM customers
To select all values from both tables, we can use UNION ALL instead.
SELECT jobTitle FROM jobs
UNION ALL
SELECT position FROM employees
Static values can be used in a UNION to help differentiate the rows from each table.
SELECT 'User' AS Type, username FROM users
UNION
SELECT 'Customer', name FROM customers