Filtrado de Datos
## Objetivos de Aprendizaje
- Dominar las cláusulas WHERE
- Usar operadores de comparación
- Trabajar con operadores lógicos
- Manejar NULL en el filtrado
## Cláusula WHERE
### Comparación Básica
```sql
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 60000;
```
### Múltiples Condiciones
```sql
SELECT first_name, last_name, department
FROM employees
WHERE department = 'Sales';
```
## Operadores de Comparación
| Operador | Descripción |
|----------|-------------|
| = | Igual |
| <> o != | Diferente |
| > | Mayor que |
| < | Menor que |
| >= | Mayor o igual que |
| <= | Menor o igual que |
### Ejemplos
```sql
-- Salario igual a 75000
WHERE salary = 75000
-- Salario diferente a 50000
WHERE salary <> 50000
-- Salario mayor a 50000
WHERE salary > 50000
-- Salario entre (inclusivo)
WHERE salary >= 50000 AND salary <= 80000
```
## Operadores Lógicos
### AND
```sql
SELECT *
FROM employees
WHERE department = 'Sales' AND salary > 50000;
```
### OR
```sql
SELECT *
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';
```
### NOT
```sql
SELECT *
FROM employees
WHERE NOT department = 'Sales';
-- Es lo mismo que
WHERE department <> 'Sales';
```
### Precedencia de Operadores
```sql
-- AND tiene mayor precedencia que OR
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 60000
```
## Operador IN
### IN en Una Sola Columna
```sql
SELECT *
FROM employees
WHERE department IN ('Sales', 'Marketing', 'HR');
```
### NOT IN
```sql
SELECT *
FROM employees
WHERE department NOT IN ('Sales', 'Marketing');
```
## Operador BETWEEN
### Verificación de Rango
```sql
SELECT *
FROM employees
WHERE salary BETWEEN 50000 AND 80000;
-- Es equivalente a
WHERE salary >= 50000 AND salary <= 80000;
```
### NOT BETWEEN
```sql
SELECT *
FROM employees
WHERE salary NOT BETWEEN 50000 AND 80000;
```
## Operador LIKE
### Coincidencia de Patrones
```sql
-- Nombres que empiezan con 'A'
WHERE first_name LIKE 'A%'
-- Nombres que contienen 'on'
WHERE first_name LIKE '%on%'
-- Nombres que terminan con 's'
WHERE last_name LIKE '%s'
```
### Comodines
| Comodín | Descripción |
|---------|-------------|
| % | Cualquier secuencia de caracteres |
| _ | Un solo carácter |
### Ejemplos de LIKE
```sql
-- Empieza con A, segundo carácter cualquier cosa, resto 'hn'
WHERE first_name LIKE 'A_n%'
-- Contiene 'o' como segundo carácter
WHERE first_name LIKE '_o%'
-- Email que termina en .com
WHERE email LIKE '%.com'
```
### NOT LIKE
```sql
SELECT *
FROM employees
WHERE first_name NOT LIKE 'A%';
```
## ILIKE (PostgreSQL)
Coincidencia de patrones sin distinción de mayúsculas:
```sql
-- Sin distinción de mayúsculas
WHERE first_name ILIKE 'alice'
WHERE first_name ILIKE '%ALICE%'
```
## Manejo de NULL en WHERE
### IS NULL
```sql
SELECT *
FROM employees
WHERE email IS NULL;
```
### IS NOT NULL
```sql
SELECT *
FROM employees
WHERE email IS NOT NULL;
```
### Error Común
```sql
-- INCORRECTO - no devuelve nada
WHERE email = NULL
-- CORRECTO
WHERE email IS NULL
```
## Ejemplos de Patrones
### Números de Teléfono
```sql
-- Formato (555)
WHERE phone LIKE '(___) ___-____'
-- Empieza con 555
WHERE phone LIKE '555%'
```
### Fechas
```sql
-- Contratados en 2020
WHERE hire_date LIKE '2020%'
-- O para manejo correcto de fechas:
WHERE hire_date >= '2020-01-01' AND hire_date <= '2020-12-31'
```
## Combinando Filtros
### WHERE Complejo
```sql
SELECT
first_name,
last_name,
salary,
department
FROM employees
WHERE (department IN ('Sales', 'Marketing', 'Engineering'))
AND salary BETWEEN 50000 AND 100000
AND (first_name LIKE 'A%' OR last_name LIKE 'S%')
AND email IS NOT NULL;
```
## Resumen
- WHERE filtra filas basadas en condiciones
- Operadores de comparación: =, <>, <, >, <=, >=
- Combinar con AND, OR, NOT
- IN verifica membresía en una lista
- BETWEEN verifica rangos (inclusivo)
- LIKE hace coincidencia de patrones con % y _
- IS NULL / IS NOT NULL para valores NULL
- Siempre usa paréntesis para condiciones complejas
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →