---
title: "SQL Select Statements"
---

# SQL Select Statements

> 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 result set 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.

```sql title="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
```

## 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.

```sql title="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 title="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.

```sql title="Pseudocode - Order By Clause"
SELECT column1, column2 FROM table ORDER BY column2 DESC
```

#### Examples

```sql title="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 title="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. 

| 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.<pre>SELECT TOP 200 column1, column2 FROM table</pre><pre>SELECT TOP 10 PERCENT column1, column2 FROM table</pre> |
| MySQL | Limit value | <pre>SELECT column1, column2 FROM table LIMIT 200</pre> |
| Oracle DB | ROWNUM &lt;= 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.<pre>SELECT column1, column2 FROM table WHERE ROWNUM &lt;= 200</pre> |

## 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.

```sql title="Pseudocode - Aliasing Columns and the Table"
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.

```sql title="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 title="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.

```sql title="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 title="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 title="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 title="SQL - Differentiating Between Users and Customers"
SELECT 'User' AS Type, username FROM users
UNION
SELECT 'Customer', name FROM customers
```