- Preparar
- Introducción
- Condiciones de igualdad y desigualdad
- Condiciones IN y NOT IN
- Condiciones EXISTS y NOT EXISTS
- Condiciones BETWEEN y NOT BETWEEN
- Condiciones LIKE y NOT LIKE
- OR condición
- Conclusión
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».
Introducción
La cláusula WHERE se utiliza para filtrar el conjunto de resultados. Si las condiciones de filtro en la cláusula WHERE se evalúan como FALSE para una fila específica, esa fila no se devuelve en el conjunto de resultados final. Se pueden agrupar varias condiciones de filtro mediante las condiciones Y y O.
Cuando se utiliza la sintaxis de combinación no ANSI, la cláusula WHERE contiene condiciones de combinación y condiciones de filtro.
SELECT e.employee_name, e.salary, d.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id -- Join Condition AND d.department_id = 20 -- Filter AND e.salary >= 2000 -- Filter ORDER BY e.employee_name; EMPLOYEE_N SALARY DEPARTMENT_ID DEPARTMENT_NAM ---------- ---------- ------------- -------------- FORD 3000 20 RESEARCH JONES 2975 20 RESEARCH SCOTT 3000 20 RESEARCH 3 rows selected. SQL>
Cuando se usa la sintaxis de combinación ANSI, la cláusula WHERE solo contiene condiciones de filtro.
SELECT e.employee_name, e.salary, d.department_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id = 20 -- Filter
AND e.salary >= 2000 -- Filter
ORDER BY e.employee_name;
EMPLOYEE_N SALARY DEPARTMENT_ID DEPARTMENT_NAM
---------- ---------- ------------- --------------
FORD 3000 20 RESEARCH
JONES 2975 20 RESEARCH
SCOTT 3000 20 RESEARCH
3 rows selected.
SQL>
En el resto de este artículo, veremos los tipos de condiciones que probablemente verá en la cláusula WHERE. Recuerde, esta no es una lista exhaustiva, solo algunas de las cosas más comunes. Para simplificar las cosas, los siguientes ejemplos usarán la sintaxis de combinación ANSI, por lo que la cláusula WHERE solo contiene condiciones de filtro.
Condiciones de igualdad y desigualdad
Las condiciones de filtro pueden utilizar pruebas básicas de igualdad y desigualdad.
=: Igual.
<> o! =: No es igual.
>: Mayor que.
<: Menos de. > =: Mayor o igual que.
<=: Menor o igual.
SELECT e.employee_name, e.department_id, e.salary FROM employees e WHERE e.department_id = 20 AND e.salary >= 2000 ORDER BY e.employee_name; EMPLOYEE_N DEPARTMENT_ID SALARY ---------- ------------- ---------- FORD 20 3000 JONES 20 2975 SCOTT 20 3000 3 rows selected. SQL>
Condiciones IN y NOT IN
La condición IN se evalúa como TRUE para los valores de columna en la lista especificada.
SELECT e.department_id, e.employee_id, e.employee_name
FROM employees e
WHERE e.department_id IN (10, 20)
ORDER BY e.department_id, e.employee_id;
DEPARTMENT_ID EMPLOYEE_ID EMPLOYEE_N
------------- ----------- ----------
10 7782 CLARK
10 7839 KING
10 7934 MILLER
20 7369 SMITH
20 7566 JONES
20 7788 SCOTT
20 7876 ADAMS
20 7902 FORD
8 rows selected.
SQL>
La condición NOT IN se evalúa como TRUE para los valores de columna que no están en la lista especificada.
SELECT e.department_id, e.employee_id, e.employee_name
FROM employees e
WHERE e.department_id NOT IN (10, 20)
ORDER BY e.department_id, e.employee_id;
DEPARTMENT_ID EMPLOYEE_ID EMPLOYEE_N
------------- ----------- ----------
30 7499 ALLEN
30 7521 WARD
30 7654 MARTIN
30 7698 BLAKE
30 7844 TURNER
30 7900 JAMES
6 rows selected.
SQL>
Las condiciones IN y NOT IN se pueden evaluar con los valores devueltos por una subconsulta.
SELECT e.department_id, e.employee_id, e.employee_name
FROM employees e
WHERE e.department_id IN (SELECT d.department_id
FROM departments d
WHERE d.department_id < 30) ORDER BY e.department_id, e.employee_id; DEPARTMENT_ID EMPLOYEE_ID EMPLOYEE_N ------------- ----------- ---------- 10 7782 CLARK 10 7839 KING 10 7934 MILLER 20 7369 SMITH 20 7566 JONES 20 7788 SCOTT 20 7876 ADAMS 20 7902 FORD 8 rows selected. SQL>
Condiciones EXISTS y NOT EXISTS
La condición EXISTS se evalúa como TRUE si la subconsulta devuelve una o más filas.
SELECT d.department_id, d.department_name
FROM departments d
WHERE EXISTS (SELECT 1
FROM employees e
WHERE d.department_id = e.department_id)
ORDER BY d.department_id;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
3 rows selected.
SQL>
La condición NOT EXISTS se evalúa como TRUE si la subconsulta devuelve cero filas.
SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (SELECT 1
FROM employees e
WHERE d.department_id = e.department_id)
ORDER BY d.department_id;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
40 OPERATIONS
1 row selected.
SQL>
BETWEEN y NOT BETWEEN Condiciones
La condición BETWEEN se evalúa como TRUE para los valores de columna dentro del rango especificado, incluidos los límites.
SELECT d.department_id, d.department_name
FROM departments d
WHERE department_id BETWEEN 20 AND 40
ORDER BY d.department_id;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
20 RESEARCH
30 SALES
40 OPERATIONS
3 rows selected.
SQL>
La condición NOT BETWEEN se evalúa como TRUE para los valores de columna que no se encuentran dentro del rango especificado, incluidos los límites.
SELECT d.department_id, d.department_name
FROM departments d
WHERE department_id NOT BETWEEN 20 AND 40
ORDER BY d.department_id;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
1 row selected.
SQL>
Condiciones LIKE y NOT LIKE
La condición LIKE se evalúa como TRUE si hay una coincidencia de patrón. El ‘%’ es un comodín de longitud variable. El ‘_’ es un comodín de un solo carácter.
SELECT d.department_id, d.department_name
FROM departments d
WHERE department_name LIKE '%O%'
ORDER BY d.department_id;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
40 OPERATIONS
2 rows selected.
SQL>
La condición NOT LIKE se evalúa como TRUE si no hay coincidencia de patrón.
SELECT d.department_id, d.department_name
FROM departments d
WHERE department_name NOT LIKE '%O%'
ORDER BY d.department_id;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
20 RESEARCH
30 SALES
2 rows selected.
SQL>
Condición OR
Tenga cuidado al vincular condiciones en la cláusula WHERE utilizando la condición OR. Es fácil estropear tu lógica. En el siguiente ejemplo, creo que he solicitado gerentes y empleados del departamento 20.
SELECT e.employee_id, e.employee_name, e.department_id, e.salary, e.job
FROM employees e
WHERE e.department_id = 20
AND e.job = 'MANAGER'
OR e.job = 'CLERK'
ORDER BY e.employee_id;
EMPLOYEE_ID EMPLOYEE_N DEPARTMENT_ID SALARY JOB
----------- ---------- ------------- ---------- ---------
7369 SMITH 20 800 CLERK
7566 JONES 20 2975 MANAGER
7876 ADAMS 20 1100 CLERK
7900 JAMES 30 950 CLERK
7934 MILLER 10 1300 CLERK
5 rows selected.
SQL>
Podemos ver en el resultado que también hemos devuelto empleados de los departamentos 30 y 10. Esto se debe a que la condición OR ha cancelado la condición del departamento si el empleado es un empleado.
Deberíamos haber utilizado paréntesis para asegurarnos de que estábamos haciendo la pregunta correcta.
SELECT e.employee_id, e.employee_name, e.department_id, e.salary, e.job
FROM employees e
WHERE e.department_id = 20
AND (e.job = 'MANAGER' OR e.job = 'CLERK')
ORDER BY e.employee_id;
EMPLOYEE_ID EMPLOYEE_N DEPARTMENT_ID SALARY JOB
----------- ---------- ------------- ---------- ---------
7369 SMITH 20 800 CLERK
7566 JONES 20 2975 MANAGER
7876 ADAMS 20 1100 CLERK
3 rows selected.
SQL>
Conclusión
Acabamos de arañar la superficie aquí. Hay muchas funciones, condiciones y operadores con los que entrará en contacto en el transcurso de su trabajo con SQL, pero todas son adiciones a esta base. Comprenda los conceptos básicos y las otras cosas irán encajando con el tiempo.
Los principiantes a menudo encuentran combinaciones bastante confusas y tienden a gravitar hacia las condiciones IN y EXISTS para filtrar datos, incluso en los casos en los que sería más eficiente unirse a la tabla específica y usar un filtro simple en su contra. En los primeros días probablemente se concentrará en tratar de obtener los resultados correctos, pero para su desarrollo a largo plazo, debe probar tantos métodos diferentes para obtener los mismos resultados como sea posible y comparar el rendimiento. Siempre es bueno conocer soluciones alternativas y, con el tiempo, conocerá qué condiciones funcionan mejor en qué situaciones.
También debe tener en cuenta que lo que escribe no siempre es lo que ejecuta Oracle. En algunos casos, el optimizador transformará (reescribirá) su declaración. Por ejemplo, una condición IN que usa una lista puede expandirse a un grupo de condiciones de igualdad vinculadas con condiciones OR. No se obsesione con esto en las primeras etapas, pero a medida que se desarrolle, deberá comenzar a considerar esto al elegir las condiciones de filtro que usará.
