Quick Search:
 The Oracle FROM Clause      [Return To Index] Jump to:  

Term: FROM

The FROM clause always follows the SELECT clause, and indicates the table(s) from which data is to be retrieved. For example, the FROM keyword in the following statement tells the database that the records to be found are stored in the user_list table:

FROM user_list
WHERE user_name = 'john'

The FROM clause can generally be anything that returns a rowset, a TABLE, VIEW, function, or system-provided information like the Information Schema. The FROM clause can also include optional JOIN subclauses and sub-queries to specify the rules for joining tables.

SELECTING from multiple tables
You can include multiple tables in the FROM clause by listing the tables with a comma in between each table name:

FROM user_list, member_type, message_list
WHERE user_name = 'john'

This will select all rows from the user_list, member_type, message_list tables where the user_name is 'john'.

Using ALIASES with tables names

A table or column can be given a "shorthand" name by using an ALIAS. This can be helpful if the SQL statement is complex or the table or column names are long.

Lets say we have a table called "user_list" and another table called "member_type". We want to get data from both tables for the name 'John Smith'. First we'll write the query without using aliases:

SELECT member_type.user_info, user_list.last_name, user_list.first_name
FROM user_list,
WHERE user_list.last_name='Smith' AND user_list.first_name='John';

Now we'll write the same SQL, but to make it easier to write our SQL statement, we'll give the user_list table an alias of "U" and the member_type table an alias "M":

SELECT M.user_info, U.last_name, U.first_name
FROM user_list AS U,
member_type AS M
WHERE U.last_name='Smith' AND U.first_name='John';

As you can see, the SQL statement with aliases is more compact and easier to write.
Related Code Snippets:
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org