SQL para Principiantes (Parte 2): La Cláusula FROM

  • Preparar
  • Tables
  • View Inline
  • La cláusula WITH
  • Views
  • Funciones de tabla canalizadas

Preparar

Puede realizar todas estas consultas en línea de forma gratuita utilizando SQL Fiddle.

Los ejemplos de este artículo requieren que estén presentes las siguientes tablas.

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

Estas tablas son una variante de las tablas EMP y DEPT del esquema SCOTT. Verá muchos ejemplos de Oracle en Internet utilizando las tablas del esquema de SCOTT. Puede encontrar las definiciones de la tabla original en el script «$ORACLE_HOME/rdbms/admin/utlsampl.sql».

Tables

Normalmente, la cláusula FROM enumera las tablas y las condiciones de combinación utilizadas en la consulta. Su forma más simple es cuando se accede a una única tabla, como se muestra a continuación. En este ejemplo, solicitamos los datos de todas las columnas de la tabla EMPLOYEES y solicitamos que los datos estén ordenados por la columna EMPLOYEE_ID. Dado que todos los datos provienen de una sola tabla, solo tenemos esa tabla referenciada en la cláusula FROM.

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>

El resultado anterior muestra todos los datos de todas las columnas de la tabla EMPLOYEES, como se esperaba.

En muchos casos, necesitamos obtener datos de varias tablas a la vez. Esto se hace mediante combinaciones. En el siguiente ejemplo, estamos usando una combinación de estilo ANSI entre las tablas EMPLOYEES y DEPARTMENTS. La combinación, o coincidencia, se realiza mediante la columna DEPARTMENT_ID en cada tabla. Cuando hacemos una combinación de estilo ANSI, todas las tablas y sus condiciones de combinación se enumeran en la cláusula FROM. Cuando ejecutamos la siguiente consulta, vemos el nombre de cada empleado (EMPPLOYEE.EMPLOYEE_NAME) y su nombre de departamento (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>

Muchos desarrolladores de Oracle prefieren utilizar la sintaxis que no es ANSI para las combinaciones. Se hace referencia a las tablas como una lista separada por comas en la cláusula FROM y las condiciones de combinación son predicados en la cláusula WHERE. La siguiente declaración es funcionalmente equivalente a la anterior.

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>

Hay una serie de variaciones en las combinaciones, que se describen aquí.

Vistas en línea / View InLine

A menudo verá subconsultas en la cláusula FROM. Se conocen como vistas en línea. Una vez definidos y con un alias, se pueden unir y referenciar como una tabla normal. La siguiente declaración es funcionalmente equivalente a los dos ejemplos anteriores, pero la combinación se ha movido a una vista en línea.

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>

Cláusula WITH

Una alternativa a una vista en línea es mover la subconsulta a la cláusula WITH. Luego podemos hacer referencia a esta consulta con nombre en la cláusula FROM de la instrucción SELECT principal. Esto se puede usar para hacer que una cláusula FROM muy complicada parezca mucho más simple.

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>

La cláusula WITH se analiza con más detalle WITH Clause : Subquery Factoring in Oracle
(OJO)

Views

Otra alternativa es crear una vista convencional basada en la subconsulta.

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;

La vista oculta parte de la complejidad de la consulta. Observe lo simple que se ve ahora la cláusula FROM.
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>

Funciones de tabla canalizadas

El ejemplo de esta sección utiliza una función de tabla canalizada. No se preocupe demasiado por la funcionalidad en este momento. Es una técnica más avanzada, pero es bueno ver cómo se ve cuando se hace referencia a ella en la cláusula FROM. Primero tenemos que crear un tipo de colección y la propia función de tabla canalizada.

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

Si ve algo como lo siguiente en una cláusula FROM, significa que se está utilizando una función de tabla o una función de tabla canalizada. Son tipos especiales de funciones que se pueden consultar como si fueran tablas.

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>

Las funciones de tabla canalizadas se analizan con más profundidad aquí.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *