WHERE y los operadores

1. Qué es

WHERE descarta filas. Se ejecuta fila por fila, una a una, y hace una sola pregunta: ¿esta condición es verdadera para esta fila? Si la respuesta es sí, la fila sigue. Si es no —o si es desconocida, que es un caso aparte y tiene su propio tema— la fila desaparece.

Es el paso 2 del orden-logico-de-ejecucion y de lejos el más importante para el rendimiento: todo lo que descartes aquí es trabajo que no se hace después.

2. Sintaxis

SELECT
    columnas
FROM
    tabla
WHERE
    condición;

2.1 Operadores de comparación

<> es el del estándar SQL; != es un sinónimo que PostgreSQL acepta. Usa <>: funciona en todos los motores.

2.2 Operadores lógicos

AND agrupa antes que OR. Esa línea es la causa de más informes silenciosamente equivocados que ninguna otra en SQL. Se ve en 3.2.

2.3 Atajos

BETWEEN es inclusivo por los dos extremos. No es opinable, es la definición.

3. Ejemplos sobre Northwind

3.1 Comparación simple

Pregunta: ¿qué productos del catálogo cuestan más de 50, y cuánto cuestan?

SELECT
    product_name,
    unit_price
FROM
    products
WHERE
    unit_price > 50
ORDER BY
    unit_price DESC;

      product_name       | unit_price
-------------------------+------------
 Côte de Blaye           |     263.50
 Thüringer Rostbratwurst |     123.79
 Mishi Kobe Niku         |      97.00
 Sir Rodney's Marmalade  |      81.00
 Carnarvon Tigers        |      62.50
 Raclette Courdavault    |      55.00
 Manjimup Dried Apples   |      53.00

7 productos de 77. El WHERE acaba de ahorrar el 91% del trabajo.

3.2 La trampa de la precedencia

Pregunta: ¿en qué clientes de Alemania o Francia la persona de contacto es el dueño del negocio?

En Northwind eso es country y contact_title = 'Owner'. La consulta parece salir sola:

SELECT
    company_name,
    city,
    country,
    contact_title
FROM
    customers
WHERE
    country = 'Germany'
    OR country = 'France'
    AND contact_title = 'Owner'
ORDER BY
    country, company_name;

       company_name       |      city      | country |    contact_title
--------------------------+----------------+---------+----------------------
 Bon app'                 | Marseille      | France  | Owner
 Du monde entier          | Nantes         | France  | Owner
 Paris spécialités        | Paris          | France  | Owner
 Alfreds Futterkiste      | Berlin         | Germany | Sales Representative
 Blauer See Delikatessen  | Mannheim       | Germany | Sales Representative
 Die Wandernde Kuh        | Stuttgart      | Germany | Sales Representative
 Drachenblut Delikatessen | Aachen         | Germany | Order Administrator
 Frankenversand           | München        | Germany | Marketing Manager
 Königlich Essen          | Brandenburg    | Germany | Sales Associate
 Lehmanns Marktstand      | Frankfurt a.M. | Germany | Sales Representative
 Morgenstern Gesundkost   | Leipzig        | Germany | Marketing Assistant
 Ottilies Käseladen       | Köln           | Germany | Owner
 QUICK-Stop               | Cunewalde      | Germany | Accounting Manager
 Toms Spezialitäten       | Münster        | Germany | Marketing Manager
(14 filas)

Mira la columna contact_title. Pediste dueños y ahí abajo hay Sales Representative, Marketing Manager, Accounting Manager… En diez de esas catorce el contacto no es el dueño: entraron solo por ser alemanas.

Y esta, con paréntesis, dice lo mismo en español:

SELECT
    company_name,
    city,
    country,
    contact_title
FROM
    customers
WHERE
    (country = 'Germany' OR country = 'France')
    AND contact_title = 'Owner'
ORDER BY
    country, company_name;

    company_name    |   city    | country | contact_title
--------------------+-----------+---------+---------------
 Bon app'           | Marseille | France  | Owner
 Du monde entier    | Nantes    | France  | Owner
 Paris spécialités  | Paris     | France  | Owner
 Ottilies Käseladen | Köln      | Germany | Owner
(4 filas)

14 contra 4. ¿Qué pasó? AND tiene más precedencia que OR, y eso significa que se lleva sus dos condiciones a un grupo aparte. La primera consulta se lee en realidad así:

WHERE
    country = 'Germany'
    OR (country = 'France' AND contact_title = 'Owner')

Ese paréntesis no lo escribiste tú: lo pone el motor. Y con él, el WHERE se queda con dos partes unidas por un OR:

1. El grupo del AND — los clientes franceses cuyo contacto es el dueño: 3.
2. Y suelta, por su cuenta, la condición del país — todos los alemanes, sea quien sea su contacto: 11.

3 + 11 = 14. El contact_title = 'Owner' quedó encerrado en el grupo de Francia y nunca llegó a aplicarse a Alemania: para una fila alemana, country = 'Germany' ya es cierto, y a un OR le basta con que un lado lo sea. El cargo del contacto ni se mira.

Precedencia no es orden de ejecución. Que AND "vaya primero" quiere decir que agrupa primero, no que se calcule primero. En qué orden evalúa el motor las condiciones no está garantizado — eso se ve en la sección 4.

Ninguna de las dos consultas da error. Las dos devuelven filas creíbles. Solo una responde la pregunta — y si en vez de las filas hubieras pedido un count(*), no habrías tenido forma de notarlo.

Por qué este ejemplo: país y cargo son independientes — el contacto de un cliente alemán puede ser el dueño o no. Con city = 'Paris' no habría trampa: París solo está en Francia, así que Germany no aportaría nada.

Regla sin excepción: si en un WHERE conviven AND y OR, pon paréntesis. Aunque sepas la precedencia. Los paréntesis no cuestan nada y quitan la duda al que lo lea, que puede ser tú dentro de un mes.

3.3 IN — la forma legible del OR

Pregunta: ¿qué clientes tenemos en Austria, Irlanda y Noruega, y en qué ciudad está cada uno?

SELECT
    company_name,
    city,
    country
FROM
    customers
WHERE
    country IN ('Austria', 'Ireland', 'Norway')
ORDER BY
    country, company_name;

         company_name         |   city   | country
------------------------------+----------+---------
 Ernst Handel                 | Graz     | Austria
 Piccolo und mehr             | Salzburg | Austria
 Hungry Owl All-Night Grocers | Cork     | Ireland
 Santé Gourmet                | Stavern  | Norway
(4 filas)

Mira la columna country: están los tres valores de la lista y nada más. Esto equivale a country = 'Austria' OR country = 'Ireland' OR country = 'Norway', pero no puede sufrir el problema de precedencia: IN es un solo operador, así que no hay nada que agrupar mal.

3.4 BETWEEN — y su orden obligatorio

Pregunta: ¿qué productos cuestan entre 21 y 22, y a qué precio exacto?

SELECT
    product_name,
    unit_price
FROM
    products
WHERE
    unit_price BETWEEN 21 AND 22
ORDER BY
    unit_price, product_name;

           product_name           | unit_price
----------------------------------+------------
 Gustaf's Knäckebröd              |      21.00
 Queso Cabrales                   |      21.00
 Louisiana Fiery Hot Pepper Sauce |      21.05
 Chef Anton's Gumbo Mix           |      21.35
 Flotemysost                      |      21.50
 Chef Anton's Cajun Seasoning     |      22.00
(6 filas)

Mira la primera fila y la última: entran los dos productos de 21.00 y entra el de 22.00. Los dos extremos del rango cuentan — BETWEEN es inclusivo por los dos lados, y esa es la respuesta a una duda que casi todo el mundo tiene la primera vez.

Esta otra consulta devuelve exactamente las mismas 6 filas. Es lo que BETWEEN significa por dentro:

SELECT
    product_name,
    unit_price
FROM
    products
WHERE
    unit_price >= 21
    AND unit_price <= 22
ORDER BY
    unit_price, product_name;

Misma pregunta, un dedo mal puesto: el rango escrito al revés.

SELECT
    product_name,
    unit_price
FROM
    products
WHERE
    unit_price BETWEEN 22 AND 21
ORDER BY
    unit_price;

 product_name | unit_price
--------------+------------
(0 filas)

Ni una fila, y sin ningún aviso. BETWEEN 22 AND 21 se traduce a >= 22 AND <= 21, que no puede ser cierto nunca. El menor va primero, siempre.

3.5 Booleanos: sin = true

discontinued es de tipo boolean, así que ya es una condición.

Pregunta: ¿qué productos están descatalogados? Sacamos también la columna discontinued para ver qué valor traen.

SELECT
    product_name,
    discontinued
FROM
    products
WHERE
    discontinued;

      product_name      | discontinued
------------------------+--------------
 Chai                   | t
 Chang                  | t
 Chef Anton's Gumbo Mix | t

10 productos descatalogados. Y el contrario:

SELECT
    count(*)
FROM
    products
WHERE
    NOT discontinued;   -- 67

WHERE discontinued = true funciona, pero es como decir "¿es verdad que es verdad?". WHERE discontinued y WHERE NOT discontinued es como se escribe.

3.6 El texto distingue mayúsculas

Pregunta: ¿cuántos clientes tenemos en Alemania? La escribimos en minúscula, como saldría de un formulario.

SELECT
    count(*)
FROM
    customers
WHERE
    country = 'germany';

 count
-------
     0

Once clientes alemanes, y la consulta devuelve cero. En PostgreSQL la comparación de texto es sensible a mayúsculas y a acentos.

Cuando no sabes cómo está escrito el dato

Es la situación normal en cuanto el valor viene de un formulario: puede llegar Germany, GERMANY o germany. lower() baja los dos lados a minúscula antes de compararlos, así que la escritura deja de importar.

Pregunta: la misma de antes —¿cuántos clientes hay en Alemania?— pero sin dar por hecho cómo está guardado el país.

SELECT
    count(*)
FROM
    customers
WHERE
    lower(country) = 'germany';

 count
-------
    11

Ahora sí salen los once. Pero lower() resuelve la mitad del problema:

lower() arregla las mayúsculas, no los acentos. lower('MÉXICO') es 'méxico', que sigue sin ser igual a 'mexico'. Northwind tiene México D.F., München y Montréal.

Y tiene un precio que conviene saber desde ya: al envolver la columna en una función, un índice normal sobre country deja de servir y el motor recorre la tabla entera. Y no es hipotético: customers tiene uno, idx_customers_country. Con 91 clientes no se nota; con dos millones, sí. Tiene arreglo —un índice sobre lower(country)—, y eso es de indices.

Las otras dos formas de no depender de esto, ILIKE y la extensión unaccent, están en buscar-texto-like-y-regex.

3.7 El aviso que hay que dar ya

Pregunta: ¿cuántos clientes son de la región SP y cuántos no? Entre las dos cuentas deberían salir los 91 de la tabla.

SELECT
    count(*)
FROM
    customers
WHERE
    region = 'SP';   --  6
SELECT
    count(*)
FROM
    customers
WHERE
    region <> 'SP';   -- 25

6 + 25 = 31, y la tabla tiene 91 clientes. Faltan 60.

No es un error del motor: 60 clientes tienen region en NULL, y NULL no es igual ni distinto a nada. Ni entra en el =, ni entra en el <>. Es el tema siguiente y hay que leerlo entero: null-y-la-logica-de-tres-valores.

4. Dónde encaja en el orden de ejecución

WHERE es el paso 2 del orden-logico-de-ejecucion: ocurre justo después de traer la tabla (FROM) y mucho antes de que el SELECT se ejecute. De ahí salen dos consecuencias que se encuentran a golpes.

4.1 El WHERE no puede usar un nombre inventado en el SELECT

SELECT
    ship_country AS pais
FROM
    orders
WHERE
    pais = 'Peru';

ERROR:  column "pais" does not exist

pais nace en el paso 5. Cuando se evalúa el WHERE, en el paso 2, todavía no existe. La solución es repetir la expresión entera:

SELECT
    ship_country AS pais
FROM
    orders
WHERE
    ship_country = 'Peru';

Con una expresión larga esto molesta, y hay una forma de calcularla una sola vez — una tabla derivada o un CTE (cte-with). Pero eso es más adelante.

4.2 Dentro del propio WHERE no hay orden garantizado

El orden lógico dice cuándo se ejecuta el WHERE respecto a las demás cláusulas, pero no en qué orden evalúa las condiciones que tiene dentro. Escribir la protección primero no protege:

SELECT
    *
FROM
    products
WHERE
    units_in_stock <> 0
    AND 100 / units_in_stock > 5;

No hay ninguna garantía de que el <> 0 se evalúe antes que la división. El motor puede reordenar, y esa consulta puede reventar por división entre cero. Para forzar el orden de evaluación existe CASE (case-condicionales-en-sql).

Y lo que más rinde de saber que es el paso 2: todo lo que descartes aquí es trabajo que los pasos 3 a 6 ya no hacen. Filtrar pronto es la optimización más barata que existe.

5. Errores comunes

1. Mezclar AND y OR sin paréntesis. Ver 3.2 (el OR de los países se agrupó con el AND del cargo, y entró Alemania entera). No da error; da un número equivocado.
2. BETWEEN con los extremos al revés. Devuelve 0 filas sin avisar.
3. BETWEEN con fechas que llevan hora. order_date en Northwind es date, así que BETWEEN '1997-01-01' AND '1997-12-31' está bien (408 pedidos). Si la columna fuera timestamp, ese mismo BETWEEN perdería todo el 31 de diciembre a partir de las 00:00:01, porque '1997-12-31' significa 1997-12-31 00:00:00. La forma a prueba de balas es >= '1997-01-01' AND < '1998-01-01'.
4. Comillas dobles en un valor de texto. WHERE country = "Germany" busca una columna llamada Germany y da column "Germany" does not exist. Simples para texto, dobles para identificadores.
5. Comparar texto sin cuidar mayúsculas ni acentos. 'méxico', 'Mexico' y 'México' son tres valores distintos.
6. Restar todo con <> y esperar el complemento. No lo es, por los NULL.

6. Diferencias con SQL Server

La primera fila es la diferencia que más sorprende al venir de SQL Server: allí WHERE country = 'germany' normalmente sí devuelve los alemanes. En PostgreSQL nunca. No es que un motor esté mal: es que SQL Server esconde la decisión en una propiedad de la base y PostgreSQL te obliga a tomarla en la consulta.

7. Ejercicios

1. Productos con precio entre 10 y 20, ambos incluidos, ordenados de más caro a más barato.
2. Clientes de Alemania, Austria o Suiza (Switzerland), usando un solo operador.
3. Pedidos de 1997 con un flete superior a 100. ¿Cuántos son?
4. Productos no descatalogados con menos de 10 unidades en stock. Son los que hay que reponer.
5. Sin ejecutarla, di qué devuelve y por qué: sql SELECT count(*) FROM products WHERE units_in_stock BETWEEN 100 AND 10;
6. Arregla esta consulta sin cambiar lo que devuelve: sql SELECT unit_price * units_in_stock AS valor FROM products WHERE valor > 1000;

8. Pregunta de negocio

Compras pide "el listado de productos en riesgo": los que se están quedando sin stock y todavía se venden. Ellos consideran riesgo si quedan menos unidades de las que marca el punto de reposición, o si hay pedidos pendientes de recibir.

La trampa está en el "o". Léela dos veces antes de escribir.