Overview of SELECT
Part III presented the simplest form of the SELECT statement. The full syntax is much more complex. Take a minute to look at the entry for SELECT in the Language Reference Guide. Much of SELECT’s power comes from its rich syntax.
In this topic, you learn a core version of the SELECT syntax:
SELECT [DISTINCT] columns
FROM tables
WHERE <search_conditions>
[GROUP BY column [HAVING <search_condition>]]
[ORDER BY <order_list>]
The SELECT syntax above has seven main keywords. A keyword and its associated information is called a clause. The clauses above are:
|
Clause |
Descriptions |
|---|---|
| SELECT columns |
Lists columns to retrieve |
|
DISTINCT |
Optional keyword that eliminates duplicate rows |
|
FROM tables |
Identifies the tables to search for values. |
|
WHERE <search_conditions> |
Identifies the tables to search for values |
|
GROUP BY column |
Groups rows retrieved according the value of the specified column |
|
HAVING <search_conditions> |
Specifies search condition to use with GROUP BY clause |
|
ORDER BY <order_list> |
Orders the output of a SELECT statement by the specified columns |
Table 1: Seven Important SELECT Clauses Clause Description
You have already used SELECT statements to retrieve data from single tables. However, SELECT can also retrieve data from multiple tables, by listing the table names in the FROM clause, separated by commas.
Retrieving Data from Two Tables at Once
In this example, you want to know the name and employee number of the person who manages the Engineering department. The Department table contains the manager number (mngr_no) for each department. That manager number matches an employee number (emp_no) in the Employee table, which has a first and last name in the record with the employee number. You link the corresponding records of the two tables by using the WHERE clause to specify the foreign key of one (mngr_no) as equal to the primary key (emp_no) of the other. Since the primary key is guaranteed to be unique, you are specifying a unique row in the employee table. Neither key has to be part of the SELECT clause. In this example, the referenced primary key is part of the SELECT clause but the foreign key is not.
To get the information described above, execute the following SQL statement in IBConsole:
SELECT department, last_name, first_name, emp_no>
FROM Department, Employee
WHERE department = 'Engineering' AND mngr_no = emp_no
This statement retrieves the following information:
