SQL for Beginners (Part 2) : The FROM Clause-优快云博客

This is the second part of a series of articles showing the basics of SQL. In this article we take a look at the type of things you are likely to see in the FROM clause of queries.

Pipelined Table Functions

Setup

You can perform all these queries online for free using SQL Fiddle.

The examples in this article require the following tables to be present.

--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;

CREATE TABLE departments (
  department_id   NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
  department_name VARCHAR2(14),
  location        VARCHAR2(13)
);

INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;


CREATE TABLE employees (
  employee_id   NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
  employee_name VARCHAR2(10),
  job           VARCHAR2(9),
  manager_id    NUMBER(4),
  hiredate      DATE,
  salary        NUMBER(7,2),
  commission    NUMBER(7,2),
  department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);

INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

These tables are a variant of the EMP and DEPT tables from the SCOTT schema. You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema. You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.

Tables

Typically the FROM clause lists the tables and join conditions used in the query. Its simplest form is when only a single table is accessed, as shown below. In this example, we've asked for the data from all of the columns from the EMPLOYEES table and we've asked for the data to be ordered by the EMPLOYEE_ID column. Since all the data is coming from a single table, we just have that one table referenced in the FROM clause.

SELECT *
FROM   employees
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                       20
       7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300            30
       7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500            30
       7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                       20
       7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400            30
       7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                       30
       7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                       10
       7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                       20
       7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                       10
       7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0            30
       7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                       20
       7900 JAMES      CLERK           7698 03-DEC-1981 00:00:00        950                       30
       7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                       20
       7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                       10

14 rows selected.

SQL>

The output above shows all the data from all the columns in the EMPLOYEES table, as expected.

In many cases, we need to source data from multiple tables at once. This is done using joins. In the following example we are using an ANSI style join between the EMPLOYEES and DEPARTMENTS tables. The join, or match, is done using the DEPARTMENT_ID column in each table. When we do an ANSI style join, all the tables and their join conditions are listed in the FROM clause. When we execute the following query, we see each employee name (EMPPLOYEE.EMPLOYEE_NAME) and their department name (DEPARTMENT.DEPARTMENT_NAME).

SELECT e.employee_name, d.department_name
FROM   employees e
       JOIN departments d ON d.department_id = e.department_id
ORDER BY e.employee_name;

EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS      RESEARCH
ALLEN      SALES
BLAKE      SALES
CLARK      ACCOUNTING
FORD       RESEARCH
JAMES      SALES
JONES      RESEARCH
KING       ACCOUNTING
MARTIN     SALES
MILLER     ACCOUNTING
SCOTT      RESEARCH
SMITH      RESEARCH
TURNER     SALES
WARD       SALES

14 rows selected.

SQL>

Many Oracle developers prefer to use the non-ANSI syntax for joins. The tables are referenced as a comma separated list in the FROM clause and the join conditions are predicates in the WHERE clause. The following statement is functionally equivalent to the previous one.

SELECT e.employee_name, d.department_name
FROM   employees e, departments d
WHERE  d.department_id = e.department_id
ORDER BY e.employee_name;

EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS      RESEARCH
ALLEN      SALES
BLAKE      SALES
CLARK      ACCOUNTING
FORD       RESEARCH
JAMES      SALES
JONES      RESEARCH
KING       ACCOUNTING
MARTIN     SALES
MILLER     ACCOUNTING
SCOTT      RESEARCH
SMITH      RESEARCH
TURNER     SALES
WARD       SALES

14 rows selected.

SQL>

There are a number of variations on joins, which are described here.

Inline Views

You will often see subqueries in the FROM clause. These are known as inline views. Once defined and given an alias, they can be joined and referenced like a regular table. The following statement is functionally equivalent to the previous two examples, but the join has been moved into an inline view.

SELECT ed.employee_name, ed.department_name
FROM   (SELECT e.employee_name, d.department_name
        FROM   employees e, departments d
        WHERE  d.department_id = e.department_id) ed
ORDER BY ed.employee_name;

EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS      RESEARCH
ALLEN      SALES
BLAKE      SALES
CLARK      ACCOUNTING
FORD       RESEARCH
JAMES      SALES
JONES      RESEARCH
KING       ACCOUNTING
MARTIN     SALES
MILLER     ACCOUNTING
SCOTT      RESEARCH
SMITH      RESEARCH
TURNER     SALES
WARD       SALES

14 rows selected.

SQL>

WITH Clause

An alternative to an inline view is to move the subquery out to the WITH clause. We can then reference this named query in the FROM clause of the main SELECT statement. This can be used to make a very complicated FROM clause look much simpler.

WITH emp_dept_join AS (
  SELECT e.employee_name, d.department_name
  FROM   employees e, departments d
  WHERE  d.department_id = e.department_id
)
SELECT ed.employee_name, ed.department_name
FROM   emp_dept_join ed
ORDER BY ed.employee_name;

EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS      RESEARCH
ALLEN      SALES
BLAKE      SALES
CLARK      ACCOUNTING
FORD       RESEARCH
JAMES      SALES
JONES      RESEARCH
KING       ACCOUNTING
MARTIN     SALES
MILLER     ACCOUNTING
SCOTT      RESEARCH
SMITH      RESEARCH
TURNER     SALES
WARD       SALES

14 rows selected.

SQL>

The WITH clause is discussed in more detail here.

Views

Another alternative is to create a conventional view based on the subquery.

CREATE OR REPLACE VIEW emp_dept_join_v AS
  SELECT e.employee_name, d.department_name
  FROM   employees e, departments d
  WHERE  d.department_id = e.department_id;

The view hides some of the complexity of the query. Notice how simple the FROM clause looks now.

SELECT ed.employee_name, ed.department_name
FROM   emp_dept_join_v ed
ORDER BY ed.employee_name;

EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS      RESEARCH
ALLEN      SALES
BLAKE      SALES
CLARK      ACCOUNTING
FORD       RESEARCH
JAMES      SALES
JONES      RESEARCH
KING       ACCOUNTING
MARTIN     SALES
MILLER     ACCOUNTING
SCOTT      RESEARCH
SMITH      RESEARCH
TURNER     SALES
WARD       SALES

14 rows selected.

SQL>

Pipelined Table Functions

The example in this section uses a pipelined table function. Don't worry too much about the functionality at this point. It's a more advanced technique, but it's good to see what it looks like when it is referenced in the FROM clause. First we have to create a collection type and the pipelined table function itself.

CREATE TYPE t_employee_name_tab AS TABLE OF VARCHAR2(10);
/

CREATE OR REPLACE FUNCTION get_employee_names
  RETURN t_employee_name_tab PIPELINED
AS
BEGIN
  FOR cur_rec IN (SELECT employee_name FROM employees) LOOP
    PIPE ROW (cur_rec.employee_name);
  END LOOP;
  RETURN;
END;
/

If you see something like the following in a FROM clause, it means a table function or a pipelined table function is being used. These are special types of functions that can be queried as if they were tables.

SELECT e.column_value AS employee_name
FROM   TABLE(get_employee_names) e
ORDER BY e.column_value;

EMPLOYEE_N
----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD

14 rows selected.

SQL>

Pipelined table functions are discussed in more depth here.

For more information see:

Hope this helps. Regards Tim...