The SELECT statement is the most commonly used SQL statement in any database. The SELECT statement is used to retrieve records from one or more tables.
Teradata SELECT Statement syntax
The syntax of the Teradata SELECT statement is as follows.
SELECT column 1, column 2, ..... FROM database_name.table_name;
Teradata SELECT Statement example
Let’s understand the concept of the Teradata SELECT statement with the help of some examples.
Consider the following Employee table.
| EMPNO | FIRST_NAME | LAST_NAME | HIREDATE | DESIGNATION | SALARY | DEPTNO |
| 1001 | STEFAN | SALVATORE | 2001-04-12 | BUSINESS ANALYST | 55000 | 40 |
| 1002 | DIANA | LORANCE | 2005-01-05 | TECHNICAL ARCHITECT | 75000 | 10 |
| 1003 | JAMES | MADISON | 2005-09-18 | MANAGER | 60000 | 20 |
| 1004 | JONES | NICK | 2004-08-19 | HR ANALYST | 35000 | 30 |
| 1005 | LUCY | GILLER | 2006-04-01 | HR ASSOCIATE | 30000 | 30 |
| 1006 | ISSAC | STEFAN | 2002-11-18 | TRAINEE | 22000 | 10 |
| 1007 | NANCY | GILBERT | 2003-04-06 | CLERK | 18000 | 10 |
| 1008 | JOHN | SMITH | 2005-10-12 | SALESMAN | 20000 | 10 |
The following is an example of the SELECT statement.
SELECT EMPNO,FIRST_NAME,LAST_NAME FROM TUTORIALSBOOK.Employee;
If the above query executes, it fetches EMPNO, FIRST_NAME, LAST_NAME from the Employee table.
SELECT EMPNO,FIRST_NAME,LAST_NAME
FROM TUTORIALSBOOK.Employee;
*** Query completed. 8 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
EMPNO FIRST_NAME LAST_NAME
----------- ------------------------- -------------------------
1008 JOHN SMITH
1004 JONES NICK
1006 ISSAC STEFAN
1002 DIANA LORANCE
1007 NANCY GILBERT
1003 JAMES MADISON
1005 LUCY GILLER
1001 STEFAN SALVATORE
If you want to retrieve all the columns of a table, you can use the asterisk(*) instead of writing down all the column names as below.
SELECT * FROM TUTORIALSBOOK.Employee;
Output:
SELECT * FROM TUTORIALSBOOK.Employee;
*** Query completed. 8 rows found. 7 columns returned.
*** Total elapsed time was 1 second.
EMPNO FIRST_NAME LAST_NAME HIREDATE DESIGNATION SALARY DEPTNO
----------- ------------------------- ------------------------- ---------- -------------------- ---------- -----------
1008 JOHN SMITH 2005-10-12 SALESMAN 20000.00 10
1004 JONES NICK 2004-08-19 HR ANALYST 35000.00 30
1006 ISSAC STEFAN 2002-11-18 TRAINEE 22000.00 10
1002 DIANA LORANCE 2005-01-05 TECHNICAL ARCHITECT 75000.00 10
1007 NANCY GILBERT 2003-04-06 CLERK 18000.00 10
1003 JAMES MADISON 2005-09-18 MANAGER 60000.00 20
1005 LUCY GILLER 2006-04-01 HR ASSOCIATE 30000.00 30
1001 STEFAN SALVATORE 2001-04-12 BUSINESS ANALYST 55000.00 40
Different options in Teradata SELECT statement
ORDER BY
The order by clause is used along with the SELECT statement to sort the result set in ascending or descending order. The default is ascending.
Syntax
The basic syntax of the order by clause is as follows.
SELECT column1, column2, .... FROM tablename ORDER BY 1 DESC;
Example
In the following example, the resultset sorts on EMPNO in ascending order.
SELECT EMPNO,FIRST_NAME,LAST_NAME FROM TUTORIALSBOOK.Employee ORDER BY EMPNO;
The above query can be written below as well. Here, the numeric "1" denotes the position of the column in the result set.
SELECT EMPNO,FIRST_NAME,LAST_NAME FROM TUTORIALSBOOK.Employee ORDER BY 1;
Output:
*** Query completed. 8 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
EMPNO FIRST_NAME LAST_NAME
----------- ------------------------- -------------------------
1001 STEFAN SALVATORE
1002 DIANA LORANCE
1003 JAMES MADISON
1004 JONES NICK
1005 LUCY GILLER
1006 ISSAC STEFAN
1007 NANCY GILBERT
1008 JOHN SMITH
GROUP BY
The GROUP BY clause is used to get similar records into groups.
Syntax
Following is the syntax of the GROUP BY clause.
SELECT coulmn1, column2, ... from tablename group by 1;
Example
The following example group the records by DEPTNO and identifies the number of employees in each department.
SELECT DEPTNO, COUNT(*) AS NUMBER_OF_EMPLYOEES FROM TUTORIALSBOOK.Employee GROUP BY DEPTNO ORDER BY DEPTNO;
Output:
*** Query completed. 4 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
DEPTNO NUMBER_OF_EMPLYOEES
----------- -------------------
10 4
20 1
30 2
40 1
WHERE Clause
The WHERE clause is used to filter the records returned by the SELECT statement. Only the records that satisfy the condition in the WHERE clause are returned.
Syntax
The syntax of the WHERE is as follows.
SELECT * from tablename WHERE [condition];
Example
The following example filters the record(s) whose DEPTNO is 30. That means only the records having DEPTNO=30 will be included in the result set.
SELECT * FROM TUTORIALSBOOK.Employee WHERE DEPTNO=30;
Output:
*** Query completed. 2 rows found. 7 columns returned.
*** Total elapsed time was 1 second.
EMPNO FIRST_NAME LAST_NAME HIREDATE DESIGNATION SALARY DEPTNO
----------- ------------------------- ------------------------- ---------- -------------------- ---------- -----------
1005 LUCY GILLER 2006-04-01 HR ASSOCIATE 30000.00 30