- SELECT Overview and Syntax
- Choosing Columns: The SELECT Clause
- Specifying Tables: The FROM Clause
- Selecting Rows: The WHERE Clause
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Specifying Tables: The FROM Clause
The table list names the table(s), the view(s), or both, that contain columns included in the SELECT list and in the WHERE clause. (Views are covered in Chapter 9—for now, just consider them a kind of table.) Separate table names in the table list with commas. The FROM syntax looks like this:
SYNTAX
SELECT select_list
FROM [qualifier.]{table_name | view_name} [alias]
[, [qualifier.]{table_name | view_name} [alias] ]... The full naming syntax for tables and views, with qualifying database and owner names, is always permitted in the table list. It's necessary, however, only when there might be some confusion about the name.
Using Table Aliases
In many SQL dialects, you can give table names aliases to save typing. Assign an alias in the table list by giving the alias after the table name, like this:
SQL
select p.pub_id, p.pub_name from publishers p
The p in front of each of the column names in the SELECT list acts as a substitute for the full table name (publishers). This query is equivalent to
SQL
select publishers.pub_id, publishers.pub_name from publishers
You can't combine the two naming conventions. Once you assign an alias, you must use the alias or no qualifier—alternately using the alias and the full table name in a given query isn't allowed because the alias actually substitutes for the table or view name during the query. In effect, the table name does not exist. Here's an example of assigning an alias but also using the full name:
SQL
select publishers.pub_id, p.pub_name from publishers p Correlation name 'publishers' not found.
Since only one table is involved in these queries, there is no ambiguity about which pub_id column you're referencing, so using the table name—either its alias or its full name—as a qualifier is optional. Aliases are really useful only in multiple-table queries where you need to qualify columns from different tables. You'll see examples of their use in Chapters 7 and 8.
Skipping FROM
Some systems allow you to write queries without a FROM clause. For example, a query for the current date and time (information not stored in a table) may work fine, like this:
Adaptive Server Anywhere
select current date current date ============ Mar 01 2000 12:00am [1 row]
SQL Variants
Other systems don't allow you to skip FROM. When you retrieve nontable information, you must use FROM with a dummy table that you create or the system supplies (for Oracle, dual).
Oracle
SQL> select sysdate 2 from dual; SYSDATE -------------------- Mar 01 2000 12:00 AM