In this article we will learn about common types of FROM queries.
1. Setup
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | --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; |
2. Tables
Usually the FROM clause will contain a list of tables and their join conditions. The simplest form that we can come across is when we get data from only one table.
As in the example below, we will retrieve all data of all columns in the EMPLOYEES table, which will be sorted by the value of the EMPLOYEE_ID column. Since we only fetch data from one table, the FROM clause will also contain only one table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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 |
In many cases, the data we want to extract is in different tables. In the next example we will join the two tables EMPLOYEES and DEPARTMENTS together, provided that join is the DEPARTMENT_ID column of the two tables.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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 |
At Oracle, we can write a join in a different way. Instead of using the key-word JOIN and ON, the joined tables are separated by commas, and the join condition is specified in the WHERE clause. The query below gives the same result as the one above.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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 |
You can refer to the different join methods here
3. Inline Views
We can also encounter subquery in FROM clause, which are called inline view. After defining and assigning the alias, the inline view can be used as a normal table. The example below uses inline view to get data similar to the two examples above.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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 |
4. Clause WITH
There is another similar way to inline view, which is to pass the contents of the sub query to the WITH clause, and call it like a normal table. This helps to simplify the complicated and complicated queries, and also increases the legibility of the query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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 |
5. Views
Another way to write a sub query is to create a view based on that query.
1 2 3 4 5 | 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 will hide part of the query complexity, making the rest look much simpler.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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 |
Refer: https://oracle-base.com/articles/misc/sql-for-beginners-the-from-clause