📋Steps in this guide1/6

SQL for Beginners (Part 2) : The FROM Clause

In this article we take a look at the type of things you are likely to see in the FROM clause of queries.

oracle miscconfigurationintermediate

by OracleDba

13 views

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. 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.

Code/Command (click line numbers to comment):

--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;

Tables

Typically the 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 clause. 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). Many Oracle developers prefer to use the non-ANSI syntax for joins. The tables are referenced as a comma separated list in the clause and the join conditions are predicates in the clause. The following statement is functionally equivalent to the previous one. There are a number of variations on joins, which are described here .

Code/Command (click line numbers to comment):

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>

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>

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>

Inline Views

You will often see subqueries in the 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.

Code/Command (click line numbers to comment):

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 clause. We can then reference this named query in the clause of the main statement. This can be used to make a very complicated clause look much simpler. The clause is discussed in more detail here .

Code/Command (click line numbers to comment):

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>

Views

Another alternative is to create a conventional view based on the subquery. The view hides some of the complexity of the query. Notice how simple the clause looks now.

Code/Command (click line numbers to comment):

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;

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 clause. First we have to create a collection type and the pipelined table function itself. If you see something like the following in a 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. Pipelined table functions are discussed in more depth here . For more information see: - SQL for Beginners (Part 2) : The FROM Clause - SQL for Beginners - Full Playlist - Oracle SQL Articles - Getting Started - Database SQL Language Reference : SELECT Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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;
/

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!