{"id":96210,"date":"2023-03-16T00:26:45","date_gmt":"2023-03-16T00:26:45","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96210"},"modified":"2023-03-09T01:10:57","modified_gmt":"2023-03-09T01:10:57","slug":"the-select-statement-in-oracle","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/the-select-statement-in-oracle\/","title":{"rendered":"The SELECT Statement in Oracle"},"content":{"rendered":"

The SELECT<\/code> statement is used to retrieve information from a database. Following the execution of a SELECT<\/code> statement, a tabular result is held in a result table (called a result set). It allows you to select the table columns depending on a set of criteria.<\/p>\n

SELECT<\/code> statements might be as straightforward as selecting a few columns from a single table. They can also be complex, with several columns and criteria and multiple tables. In this article, I will introduce the basic SELECT<\/code> statement fetching data from just one table.<\/p>\n

SELECT Syntax:<\/h2>\n

The SELECT<\/code> statement contains 4 Parts:<\/p>\n

SELECT >> COLUMNS >> FROM >>WHERE<\/strong><\/p>\n

The SELECT<\/code> clause defines which table columns are retrieved. The FROM<\/code> clause defines which tables are queried. The WHERE<\/code> clause determines\u00a0which table rows should be selected\u00a0and\u00a0if it is omitted, all table rows are selected.<\/p>\n

The basic syntax looks like this:<\/p>\n

SELECT Column_Name1, Column_Name2, Column_Name\r\nFROM Table_Name \r\nWHERE Criteria;<\/pre>\n
A database consists of one or more tables. Each table is given a distinct name (e.g., “Employee”, “Department”, “Address”). Data records are stored in tables (rows).<\/p>\n
Here’s an example of an “<\/strong>Employee<\/code>” <\/strong>table: You can use the following SQL script to create “<\/strong>Employee<\/code>\u201d <\/strong>and load the data.<\/p>\n
SQL script to create the\u00a0 “Employee<\/code>\u201d table.<\/p>\n
CREATE TABLE Employee (\r\n    Employeeid Varchar(15) Primary Key,\r\n    Name       Varchar(15),\r\n    LastName   Varchar(15),\r\n    FirstName  Varchar(15),\r\n    Department Varchar(15),\r\n    Dob        Date,\r\n    Salary     Number(8,0)\r\n);<\/pre>\n
The following INSERT<\/code> statements will load data into the “<\/strong>Employee<\/code>” <\/strong>table for the demos in this article.<\/p>\n
INSERT INTO Employee (EmployeeId, Name, LastName, FirstName, Department, DOB, Salary)\r\nVALUES ('1001', 'Allen Cox', 'Cox', 'Allen', 'IT', TO_DATE('01-July-1982', 'DD-MM-YYYY'),75000);\r\nINSERT INTO Employee (EmployeeId, Name, LastName, FirstName, Department, DOB, Salary)\r\nVALUES ('1002', 'Dave Carry', 'Carry', 'Dave', 'Sales', TO_DATE('01-Jan-1992', 'DD-MM-YYYY'),70000);\r\nINSERT INTO Employee (EmployeeId, Name, LastName, FirstName, Department, DOB, Salary)\r\nVALUES ('1003', 'Amit Singh','Singh', 'Amit', 'Sales', TO_DATE('20-Oct-1991', 'DD-MM-YYYY'),50000);\r\nINSERT INTO Employee (EmployeeId, Name, LastName, FirstName, Department, DOB, Salary)\r\nVALUES ('1004', 'Rhonda Grant', 'Grant', 'Rhonda', 'Marketing', TO_DATE('15-May-1980', 'DD-MM-YYYY'),60000);\r\nINSERT INTO Employee (EmployeeId, Name, LastName, FirstName, Department, DOB, Salary)\r\nVALUES ('1005', 'Marvin Cox', 'Cox', 'Marvin', 'Marketing', TO_DATE('15-Jan-1985', 'DD-MM-YYYY'),62000);\r\nINSERT INTO Employee (EmployeeId, Name, LastName, FirstName, Department, DOB, Salary)\r\nVALUES ('1006', 'Dave Grant', 'Grant', 'Dave', 'Sales', TO_DATE('05-Oct-1980', 'DD-MM-YYYY'),90000);<\/pre>\n
This is how the data in the table looks.<\/p>\n\n\n\n\n\n\n\n\n
\n
EmployeeId<\/strong><\/p>\n<\/td>\n
\n
Name<\/strong><\/p>\n<\/td>\n
\n
LastName<\/strong><\/p>\n<\/td>\n
\n
FirstName<\/strong><\/p>\n<\/td>\n
\n
Department<\/strong><\/p>\n<\/td>\n
\n
DOB<\/strong><\/p>\n<\/td>\n
\n
Salary<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
1001<\/p>\n<\/td>\n
\n
Allen Cox<\/p>\n<\/td>\n
\n
Cox<\/p>\n<\/td>\n
\n
Allen<\/p>\n<\/td>\n
\n
IT<\/p>\n<\/td>\n
\n
01-JUL-82<\/p>\n<\/td>\n
\n
75000<\/p>\n<\/td>\n<\/tr>\n
\n
1002<\/p>\n<\/td>\n
\n
Dave Carry<\/p>\n<\/td>\n
\n
Carry<\/p>\n<\/td>\n
\n
Dave<\/p>\n<\/td>\n
\n
Sales<\/p>\n<\/td>\n
\n
01-JAN-92<\/p>\n<\/td>\n
\n
70000<\/p>\n<\/td>\n<\/tr>\n
\n
1003<\/p>\n<\/td>\n
\n
Amit Singh<\/p>\n<\/td>\n
\n
Singh<\/p>\n<\/td>\n
\n
Amit<\/p>\n<\/td>\n
\n
Sales<\/p>\n<\/td>\n
\n
20-OCT-91<\/p>\n<\/td>\n
\n
50000<\/p>\n<\/td>\n<\/tr>\n
\n
1005<\/p>\n<\/td>\n
\n
Marvin Cox<\/p>\n<\/td>\n
\n
Cox<\/p>\n<\/td>\n
\n
Marvin<\/p>\n<\/td>\n
\n
Marketing<\/p>\n<\/td>\n
\n
15-JAN-85<\/p>\n<\/td>\n
\n
62000<\/p>\n<\/td>\n<\/tr>\n
\n
1006<\/p>\n<\/td>\n
\n
Dave Grant<\/p>\n<\/td>\n
\n
Grant<\/p>\n<\/td>\n
\n
Dave<\/p>\n<\/td>\n
\n
Sales<\/p>\n<\/td>\n
\n
05-OCT-80<\/p>\n<\/td>\n
\n
90000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Let\u2019s write SELECT<\/code> statements on the above table.<\/p>\n
Retrieve Data from a Single Table<\/h1>\n
The SELECT<\/code> statement can retrieve data from single or multiple tables; however, all my examples in this article use a single table to focus on the parts of the SELECT<\/code> statement itself. In a future article, I will cover multiple table queries and how JOIN operators can be used to connect data from multiple tables and then shaped into a single tabular data stream.<\/p>\n
Select all columns<\/h2>\n
An asterisk following the word “SELECT<\/code>” indicates that all fields that are available should be retrieved (columns).<\/p>\n
Using SELECT *<\/code> in SQL queries is great for doing quick ad hoc work, but for code that will be reused in production, this is a poor practice that should be avoided. Instead of SELECT *<\/code>, it is recommended to provide the column names you want in the SELECT<\/code> query. Using column names not only improves efficiency but also makes SQL code easier to understand.<\/p>\n
Furthermore, if you use SQL with *<\/code> in an application such as PeopleSoft code or .NET code and add any new columns to the table or reorder the existing columns, your code may break. It is however, what most people use when exploring data.<\/p>\n
The following SELECT<\/code> statement gets all the columns and rows from the given table. Note that the FROM<\/code> clause is where you specify the set of data that your query will be using. I will discuss this more when covering JOIN<\/code> operations.<\/p>\n
SELECT  *  \r\nFROM Employee;<\/pre>\n
This returns:<\/p>\n
\"Graphical<\/p>\n
Selecting one or more columns by name<\/h2>\n
Any database object such as a column name can be named using either quoted or unquoted identifiers. Unquoted identifiers are not case-sensitive. Oracle classifies them as uppercase.\u00a0 Case is important when using quoted IDs. By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace.<\/p>\n