Skip to end of metadata
Go to start of metadata


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.

Pseudocode - Selecting Two Columns From a Table, and a Third Column with a Value of 10 for every row
SELECT column1, column2, 10 FROM table

On this page ...


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.

Pseudocode - The Select Distinct Command
SELECT DISTINCT column FROM table

Examples

This can be useful for getting a better idea of the range of values in a particular column.

SQL - Select Distinct Countries
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.

Pseudocode - Order By Clause
SELECT column1, column2 FROM table ORDER BY column2 DESC

Examples

SQL - Ordering by One Column
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.

SQL - Ordering by Multiple Columns
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. 

DatabaseKeywordExample
MS SQL Server/ MS AccessSELECT 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.

SELECT TOP 200 column1, column2 FROM table
SELECT TOP 10 PERCENT column1, column2 FROM table
MySQLLimit value
SELECT column1, column2 FROM table LIMIT 200
Oracle DBROWNUM <= 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.

SELECT column1, column2 FROM table WHERE ROWNUM <= 200


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.

Pseudocode - Aliasing Columns and the Table
SELECT column1 AS a, column2 AS 'b c' FROM table AS t

Example

This can be really useful when the table has complex column names.

SQL - Aliasing 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.

SQL - Joining Columns from Two Tables with Aliases
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.

Pseudocode - Union Two Tables
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.

SQL - Union of Users and Customers
SELECT username FROM users
UNION
SELECT name FROM customers

To select all values from both tables, we can use UNION ALL instead.

SQL - Union All
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.

SQL - Differentiating Between Users and Customers
SELECT 'User' AS Type, username FROM users
UNION
SELECT 'Customer', name FROM customers



 

  • No labels