- The Select Statement
- The Select Clause
- The Where Clause
- The Order By Clause
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
The Select Clause
The select clause is the first part of a query. The select clause says which columns of information you want, what order you want them in, and what you want them to be called. Do not confuse the select clause with the select statement.
2-3 Overview of the select clause
There are three forms of the select clause. The following pages show an example of each of these.
|
select a list of columns |
|
|
select * or select table_name.* |
|
|
select distinct a list of columns |
|
The first form, select a list of columns, gets only the columns that are listed. It can rename these columns, giving them a column alias. It also specifies the order in which the columns are to be listed.
The second form, select *, gets all the columns of a table. This does not list the columns individually, so it cannot give the columns an alias or specify an order for the columns. The columns are listed in the order in which they appear in the table.
The third form, select distinct a list of columns, is similar to the first form, but it includes the word distinct. This eliminates all the duplicate rows from the result table. Two rows are duplicates if they have identical values in every column of the result table. If even one column is different, then they do not match and they are not duplicates.
The only required clauses are the select clause and the from clause. You can write a select statement with only these two clauses. The following query lists all the columns and all the rows of the l_employees table.
select * from l_employees;
2-4 Using the select clause to get a list of some of the columns
This section shows an example of a select clause that is used to get a list of columns. Only the columns listed in the select clause appear in the result table. The other columns of the beginning table are omitted.
The order of the columns within the select clause determines their order within the result table. This can be different from their order within the beginning table.
It is possible for the same column to be listed two or more times. This is sometimes useful when different formatting or functions are applied to the column. Chapters 6 and 7 discuss formatting. Functions are covered in chapters 9, 10, and 11.
A literal value can be included in the select clause. That value will then appear in every row of the result table. If the literal value is text or a date, it must be enclosed in single quotes. If it is a number, no quotes are used.
A column can be renamed by giving it a column alias. This changes the heading that appears in the result table. It does not have any permanent effect on the table or the database. To assign a column alias, use this syntax:
column_name AS alias_name
The AS is optional in Oracle and required in Access. I recommend that you use it because it makes the select statement easier to read and understand. Usually you should avoid having spaces within the name of the column alias. A common convention is to replace the spaces with underscore characters.
Sometimes the column heading is truncated in the result table to save space. Instead of showing the full column name or column alias, only the beginning part is shown. This is done in both Oracle and Access.
In Access, if you want to see the full column heading, use the mouse to make the column wider. This can be done after SQL has been run.
In Oracle, you must use a command to the SQLplus environment to set the column width. This must be done before SQL is run. The syntax of this command is
COLUMN <column name or column alias>FORMAT A <maximum width> ;
So in the following example, for Oracle, I used the SQLplus commands
column employee_number format 9999; column extension format a10;
Employee_number is a numeric column because it comes from employee_id, a number column. Each 9 in the format for this column stands for one digit, so the format for this column says that it will be formatted as a four-digit number.
Extension is a text column because it comes from phone_number, a text column. "A" in the format means that this is an alphanumeric column, consisting of letters and numbers. Alphanumeric is another word for a text column. The 10 after the A makes the column ten characters wide.
I have already set up for you most of these column formats you will need in this book. Oracle does not retain them from one session to another, so each time you log on to Oracle, you should refresh them by entering the command
start c:\temp\sqlfun_login.sql
Replace c:temp\ with your own path name. This Oracle script is explained further in appendix B.
Task
Get three columns from the l_employees table:
employee_id phone_number last_name
Display them in that order. Change the name of the employee_id column to employee_number and the name of the phone_number column to extension. Also create two new columns: evaluation and rating. Give every employee an evaluation of "excellent worker" and a rating of 10.
Oracle & Access SQL
select employee_id as employee_number,phone_number as extension, last_name, 'excellent worker' as evaluation,
10 as rating
from l_employees;
Beginning table (l_employees table)
Result table
Notes
|
|
The employee_id column is being renamed employee_number. This new name, the column alias, is the column heading in the result table. An underscore character is used to join the words "employee" and "number." This makes the column alias a single word, as it contains no spaces. The reason for doing this is that Oracle and Access SQL are the same as long as the column alias does not contain spaces. Both Oracle and Access allow spaces in the column alias. However, the code is written with a slight difference. In Oracle, double quotes must be used around a column alias that contains a space, while in Access, square brackets are used: Oracle:select employee_id as "employee number" Access:select employee_id as [employee number] |
|
|
The text 'excellent worker' is added to every row of the result table in a column called evaluation. This is an example of placing a literal value in a select statement. In this case, the literal value is text, so it is enclosed in single quotes. |
|
|
Here the literal value is a number, so it is not enclosed in quotes. |
2-5 Using the select clause to get a list of all of the columns
Here is an example of a select clause that gets all the columns of a table and lists them in the same order in which they occur within the beginning table. In this example, there is no where clause, so the result table contains all the columns and all the rows of the beginning table. This means that the beginning table and the result table are identical.
This is the simplest select statement that you can write. The select clause and the from clause are required in any select statement. All other clauses are optional.
Task
Get the entire l_employees table, all the columns and all the rows. Display all the columns in the same order as they are defined in the table.
Oracle & Access SQL
Select * from l_employees;
Beginning table (l_employees table)
Result table
Notes
|
|
The result table is identical to the beginning table, except possibly for the order of the rows. In the listings here, the rows are in exactly the same order. I did this to make the example easy to understand. In theory, however, the rows of both tables are unordered sets, so the rows in the result table could appear in a different order. |
Oracle & Access SQL:
Variation 1 — Adding a where clause
If a where clause is added to the select statement, the result table can contain only some of the rows of the beginning table. For example:
select * from l_employees where manager_id is null;
This lists the two rows for employees 201 and 206.
Variation 1: Result table
Oracle & Access SQL:
Variation 2 — Adding an order by clause
If an order by clause is added to the select statement, the rows of the result table may be sorted in a different order. For example, you could sort them by hire_date. When there is no order by clause, the computer is allowed to list the rows of the result table in any order. To control the order and ensure that the rows are sorted by the value in the employee_id column, it is necessary to write:
select * from l_employees order by employee_id;
Variation 2: Result table
Displaying the data in any table
If you know the name of any table, you can display all the data in it with the select statement
select * from table_name;
You replace table_name with the name of your table. In Oracle, if the table contains many rows, the screen may start to scroll. To stop the scrolling, you can use
CTRL + C or File -> Cancel
In Access, this problem does not occur. The screen scrolls only in response to your input.
2-6 Using the select statement to get the distinct values in one column
This section shows an example of using select distinct on one column to find all of its values and list each of them only once. This is particularly useful when you are working with a column that contains codes, such as the dept_code column. In this example, we apply select distinct to the manager_id column. In the result table, manager ID 201 is displayed only once, even though there are three rows of the beginning table with this value. The duplicate values are removed.
Notice that the null value does appear in the result table. Here we see that select distinct treats nulls as it treats any other data in the table. If there were several nulls in the manager_id column of the beginning table, the result table would still contain only a single null.
Task
Get a list of all the different values in the manager_id column of the l_employees table.
Oracle & Access SQL
select distinct manager_id from l_employees;
Beginning table (l_employees table)
Result table
Where nulls are placed in the sort order — A difference between Oracle and Access
In Oracle, nulls are placed at the bottom of the sort order. In Access they are placed at the top. This is not a big difference. It causes a slight difference in the appearance of the result, although the rows in the result are the same in both cases.
Everyone agrees on the sort order for the numbers 0 to 9 and for the letters A to Z. However, there is no such agreement about how nulls fit into the sort order. In the absence of a common agreement, the developers of Oracle decided to resolve the issue one way and the developers of Access decided to resolve it another way.
The result table shown next shows the null at the bottom. This is the Oracle method. People using Access will find the null at the top. In Access, the null appears as a blank.
In this example, one could argue that because the select statement contains no order by clause, the rows of the result table are allowed to be in any order. In theory, the null can appear in any position within the result table. In practice, when select distinct is used, a sort is performed as part of the process of eliminating duplicates. Therefore, the rows of the result table are presented in sorted order, even though no order by clause is used. In this case, the sort is performed on the manager_id column.
Oracle & Access SQL:
Variation 1 — Adding a where clause to select distinct
Select distinct may be used with a where clause to limit the number of rows in the result table. The where clause is processed first, which removes some rows from the beginning table. Then the select distinct clause is processed. Here is an example:
select distinct manager_id from l_employees where employee_id in (201, 208, 210);
Variation 1: Result table
Oracle & Access SQL:
Variation 2 — Adding an order by clause to select distinct
Select distinct may be used with an order by clause to sort the rows of the result table in either an ascending or a descending order.
select distinct manager_id from l_employees order by manager_id desc;
Variation 2: Result table
Oracle & Access SQL:
Variation 3 — What happens if you eliminate the word distinct?
If the word distinct is removed from the select statement, then the result table will be the same as the manager_id column of the beginning table. The value 201 will appear three times. No duplicate values will be removed, nor will any sort occur. The rows might appear in the same order as in the beginning table, or they could appear in some completely different order. Here is an example:
select manager_id from l_employees;
Variation 3: Result table
2-7 Using the select clause to get the distinct values of several columns
This section shows an example of using select distinct with two columns. The SQL code is similar to the code in the previous section. Here a second column is added to the select distinct clause, the credit_limit column. The result table shows all the different combinations of values in the two columns, manager_id and credit_limit.
When select distinct is used with several columns, the result table shows a single instance of each valid combination of the columns. In other words, no two rows of the result table are the same. Every two rows differ in the values of at least one of the columns.
Task
Get a list of all the different values in the manager_id and credit_limit columns of the l_employees table.
Oracle & Access SQL
select distinct manager_id,
credit_limit
from l_employees;
Beginning table (l_employees table)
Result table
What it means to eliminate duplicate rows from the result
The result table here contains two rows with a manager ID of 201. In section 2-6, there was only one such row. What is the difference?
There is another column in the result, the credit_limit column. The two rows in which manager ID equals 201 have different values in the credit_limit column, $15.00 and $25.00. Two rows of the result are distinct as long as there is at least one column in which they differ. In section 2-6, the credit limit was not part of the result, so the difference between these rows is not in the result. That is why these two occurrences of 201 are condensed into a single row.
The beginning table contains three rows with a manager ID of 201. Two rows have a $25.00 credit limit and one has a $15.00 credit limit. The result table shows only one row for each of these combinations.
In the result table, each row is distinct. You can think of this as a three-step process. First, all the columns in each row of the result table are concatenated together into a single unit of data, then these units are sorted. Last, all the duplicate units are removed.