Teradata Select Statement
The SELECT statement is the most frequently used SQL statement. The SELECT statement is used to retrieve records from a table.
Syntax
Following is the basic syntax of the SELECT statement.
Example
Suppose the following student table.
| RollNo | FirstName | LastName | BirthDate |
|---|---|---|---|
| 1001 | James | Williams | 1/5/1980 |
| 1002 | Robert | Vadra | 3/5/1983 |
| 1003 | Peter | Dinklage | 4/1/1983 |
| 1004 | Alexa | Bliss | 11/6/1984 |
| 1005 | Edward | Norton | 12/1/1984 |
Now execute the Following query of the SELECT statement.
When the above query is executed, it fetches RollNo, FirstName, and LastName columns from the student table.
| RollNo | FirstName | LastName |
|---|---|---|
| 1001 | James | Williams |
| 1002 | Robert | Vadra |
| 1003 | Peter | Dinklage |
| 1004 | Alexa | Bliss |
| 1005 | Edward | Norton |
If we want to fetch all the columns from a table, we use the following command instead of listing down all columns.
The above query will fetch all the records from the student table.
Options, Clauses, and Lists
We can use the following options, lists, and clauses with the SELECT statement to request data from Teradata Database, such as:
- DISTINCT option
- FROM clause
- WHERE clause, including subqueries
- GROUP BY clause
- HAVING clause
- QUALIFY clause
- ORDER BY clause
- CASESPECIFIC option
- International sort orders
- WITH clause
- Query expressions and set operators
SELECT INTO statement is another variation of the Select statement. It is used in embedded SQL and stored procedures.
SELECT INTO statement selects at most one row from a table and assigns the values in that row to host variables in embedded SQL or to local variables or parameters in Teradata Database stored procedures.
WHERE Clause
The WHERE clause is used to filter the records, which is returned by the SELECT statement. A condition is associated with the WHERE clause. In the WHERE clause, only those records are returned who satisfy the condition.
Syntax
Following is the syntax of the SELECT statement with the WHERE clause.
Example
In the following query we try to fetch record where RollNo is 1001 from the given student table.
When the above query is executed, it returns the following records.
| RollNo | FirstName | LastName |
|---|---|---|
| 1001 | James | Williams |
ORDER BY
When the SELECT statement is executed, the returned rows are not in any specific order. ORDER BY clause is used to arrange the records in ascending or descending order on any columns.
Syntax
Following is the syntax of the SELECT statement with the ORDER BY clause.
Example
In the following query we fetch records from the student table and order the results by FirstName.
When the above query is executed, it produces the following output.
| RollNo | FirstName | LastName |
|---|---|---|
| 1004 | Alexa | Bliss |
| 1005 | Edward | Norton |
| 1001 | James | Williams |
| 1003 | Peter | Dinklage |
| 1002 | Robert | Vadra |
GROUP BY
GROUP BY clause is used with SELECT statement and arranges similar records into groups.
Syntax
Following is the syntax of the SELECT statement with the GROUP BY clause.
SELECT Statement and Set Operators
The SELECT statement can use the set operators UNION, INTERSECT, and MINUS/EXCEPT. These set operators allow manipulating the answers to two or more queries by combining the results of each query into a single result set.
We can use the set operators within the following operations:
- View definitions
- Derived tables
- Subqueries
SELECT Statement and Joins
A SELECT statement can reference data in two or more tables, and the relational join combines the data from the referenced tables.
In this way, the SELECT statement defines a join of specified tables to retrieve data more efficiently than without defining a join of tables.
We can specify both inner joins and outer joins:
- An inner join selects data from two or more tables that meet specific join conditions. Each source must be named, and the join condition is a common relationship among the tables to be joined. It can be on an ON clause or a WHERE clause.
- The outer join is an extension of the inner join that includes rows that qualify for a simple inner join, as well as a specified set of rows that do not match the join conditions expressed by the query.