8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
Related articles.
- SQL for Beginners (Part 2) : The FROM Clause
- SQL for Beginners - Full Playlist
- Oracle SQL Articles - Getting Started
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:
- 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...