What is the FROM Clause in SQL? & Explained With Code

FROM Clause in SQL

An essential part of the SQL SELECT statement, the FROM clause is required for all Oracle queries that retrieve data. Its main objective is to identify the table or tables, view or other datathat should be used to retrieve the data. In essence, it instructs Oracle where to get the data you’re requesting.

Oracle handles data logically, so you may specify criteria to filter rows and don’t have to worry about physical storage or retrieval techniques because Oracle will retrieve all rows that satisfy your criteria in a single step. In order to define this logical request, the FROM clause is essential.

Basic Data Retrieval from a Single Table

Querying data from a single table is the most basic application of the FROM clause. The table name appears immediately after the FROM keyword. For example, in the SELECT clause, you would use the asterisk (*) to indicate “all columns” in order to extract all rows and columns from a table called NEWSPAPER:

CREATE TABLE NEWSPAPER (
    FEATURE VARCHAR(50),
    S CHAR(1),
    PAGE NUMBER
);

INSERT ALL
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('National News', 'A', 1)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Sports', 'D', 1)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Editorials', 'A', 12)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Business', 'E', 1)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Weather', 'C', 2)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Births', 'F', 7)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Bridge', 'B', 2)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Classified', 'F', 8)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Comics', 'C', 4)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Doctor Is In', 'F', 6)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Modern Life', 'B', 1)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Movies', 'B', 4)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Obituaries', 'F', 6)
    INTO NEWSPAPER (FEATURE, S, PAGE) VALUES ('Television', 'B', 7)
SELECT * FROM dual;

Code Example:

SELECT *
FROM NEWSPAPER;

Code Output (Example):

FEATURE         S       PAGE
--------------- - ----------
National News   A          1
Sports          D          1
Editorials      A         12
Business        E          1
Weather         C          2
Births          F          7
Bridge          B          2
Classified      F          8
Comics          C          4
Doctor Is In    F          6
Modern Life     B          1
Movies          B          4
Obituaries      F          6
Television      B          7

Retrieving Data from Multiple Tables

Combining data from two or more tables increases the power of the FROM clause. Joins are used to accomplish this, creating connections between tables based on shared columns. It’s a good idea to give table aliases when including several tables in the FROM clause. This will keep your queries more legible and clear up any confusion that may arise if columns with the same name appear in separate tables.

To choose product IDs, quantity sold, customer city, and state province, for instance, you may combine the SALE and CUSTOMER table:

Code Example:

SELECT s.prod_id, s.quantity_sold, c.cust_city, c.cust_state_province
FROM sales s, customers c
WHERE s.cust_id = c.cust_id
AND s.prod_id = 117;

In this example, s is an alias for SALES, and c is an alias for CUSTOMERS. The WHERE clause then specifies the join condition (s.cust_id = c.cust_id) and any additional filtering. ANSI SQL also provides explicit JOIN syntax using ON or USING clauses, where the join criteria are defined directly within the FROM portion of the statement.

Querying Views

Instead of storing data on their own, views are logical tables that are based on SQL queries. But, like a physical table, they may be referenced in the FROM clause, which makes them extremely helpful for streamlining intricate searches, adding an abstraction layer, and enforcing security by limiting access to a subset of data.

For example, you can query the view you created, INVASION, which connects the WEATHER and LOCATION tables:

Code Example:

SELECT City, Condition, Temperature
FROM INVASION;

Views have the ability to conceal the intricacy of underlying aggregations or joins.

Subqueries in the FROM Clause

An inline view is another name for a subquery in the FROM clause. A temporary, named result set can be defined and subsequently queried like a regular table. This method is notably helpful for decomposing complicated queries, particularly when working with several aggregations or levels of grouping.

To determine the proportion of books in each category, consider combining a subquery that counts all books with a view of category counts:

Code Example:

SELECT CategoryName, Counter, (Counter/BookCount)*100 AS Percent
FROM CATEGORY_COUNT,
     (SELECT COUNT(*) AS BookCount FROM BOOKSHELF)
ORDER BY CategoryName;

Here, (SELECT COUNT(*) AS BookCount FROM BOOKSHELF) acts as an inline view, aliased as BookCount, which returns a single row with the total count of books.The computations in the main query are then based on this result. Finding the top N rows according to specific criteria can also be done with inline views.

External Tables

As with conventional database tables, you can access data stored in operating system files (such as flat files) using external tables. These external tables are queried using the FROM clause in the same manner as regular tables, including joins and views.

Code Example:

SELECT * FROM SYS_SQLLDR_X_EXT_BOOKS;

As a result, SQL can be used to report on other data such as log files.

Table Functions

Table functions are PL/SQL operations that yield a collection, such as a VARRAY or nested table. To consider the output of these methods as a relational table and run a straight query on it, use the TABLE function in the FROM clause.

For instance, to show data in distinct lines from a variable array B.Tool:

Code Example:

SELECT B.Name, N.*
FROM BORROWER B, TABLE(B.Tools) N;

SQL statements can be made simpler by using table functions, which can contain sophisticated logic that changes data and displays it in a tabular fashion.

Remote Tables via Database Links

You can utilise database links to query tables that are in a different database. The remote is specified by appending @db_link_name to the table name in the FROM clause.

Code Example:

SELECT *
FROM BOOKSHELF@REMOTE_CONNECT;

This makes it possible to distribute queries among several databases. When building views to conceal the actual location of distant data, database links can also be utilised.

The DUAL Table

Oracle comes with a unique DUAL one-row, one-column table. It is frequently utilised in the FROM clause for SELECT queries that only evaluate an expression or invoke a function and do not require the retrieval of data from a particular table.

Code Example:

SELECT 'Hello world!' FROM dual;

In Summary

In SQL, the FROM clause serves as the foundation for data retrieval. It determines the data, be it a logical view, a dynamic subquery, an external file, a single table, a group of tables, or the output of a function. For efficient information extraction and manipulation from an Oracle database, it is essential to become proficient in its use in conjunction with aliases and different sorts of joins. The FROM clause can be thought of as your SQL query’s GPS, telling it exactly where to go for the data you want.