Hola, en nuestro quehacer diario con bases de datos relacionales nos topamos a veces con que hay que extraer el o los últimos caracteres de un campo de tipo texto o string de una tabla. Según la base de datos usada la instrucción puede variar.
Enseguida veremos algunos ejemplos, en las distintas bases de datos, donde extraeremos los útimos 2 caracteres del texto constante “texto ejemplo”:
SQLite:
SELECT SUBSTR(‘texto ejemplo’, -2);
Postgresql:
SELECT SUBSTRING(‘texto ejemplo’ from ‘.{2}$’);
SELECT RIGHT(‘texto ejemplo’,2);
MySQL o MariaDB:
SELECT SUBSTRING(‘texto ejemplo’,-2);
SELECT RIGHT(‘texto ejemplo’,2);
SQL Server:
SELECT RIGHT(‘texto ejemplo’, 2);
ORACLE:
SELECT SUBSTR(‘texto ejemplo’, -2) FROM dual;
Todas las sentencias sql descritas anteriormente ejecutadas en la respectiva base de datos regresarán la cadena de caractares ‘lo’.
Dependiendo del número de caracteres a la derecha que queramos obtener sólo tendremos que cambiar el número dos por el número de caracteres deseado en cada una de las sentencias anteriores.
Espero y les séa de utilidad este artículo. Agradezco sus comentarios. ¡Saludos!
En la sección anterior vimos la cláusula INNER JOIN que combina dos tablas por un campo en común. En sesta sección veremos el uso de la cláusula LEFT JOIN para encontrar filas de una tabla que no tengan filas coincidentes en otra con la que se relaciona.
Revisa la estructura de las tablas “Artist” que contiene datos de los artistas y la tabla “Album” que contienen datos de los álbumes que han hecho los artistas.
Estructura de la tabla “Artist”.
Estructura de la tabla “Album”.
Como puedes observar, ambas tablas se relacionan por el campo o columna “ArtistId”. En la tabla “Artist” este campo es su identificador o llave primaria (“primary key” en inglés) , mientras que en la tabla “Album” la misma columna se le conoce como “llave foránea” (“foreign key” en inglés). Las llaves foráneas de una tabla “apuntan” a las llaves primarias de otras tablas. En la sección pasada vimos estos casos y relacionamos o combinamos las tablas por este tipo de columnas.
Antes de entrar de lleno al la cláusula LEFT JOIN vamos a unir ambas tablas con la cláusula INNER JOIN para destacar que cuando usamos INNER JOIN sólo obtenemos las filas de ambas tablas que tienen relación. Escribe y ejecuta la siguiente sentencia SQL:
“Inner Join” entre “Artist” y “Album”.
La consulta anterior combina o une las tablas “Artist” y “Album” mediante la columna “ArtistId” de ambas tablas siempre y cuando sus valores coincidan o sean iguales. Al final ordena los resultados por la columna “ArtistId” de “Artist”. Obtenemos 347 filas.
Deslízate hacia adelante en los resultados hasta que encuentres los registros que se muestran en la imagen anterior que muestra la consulta y los resultados. Aunque los resultados están ordenados por el identificador de la tabla “Artist”, de menor a mayor, vemos que los valores no son consecutivos, los encerrados en con rojo en la imagen. Del identificador 24 se “salta” al 27, del 27 se “salta” al 36 y así. ¿Por qué existen esos saltos? ¿No existen filas con esos identificadores en la tabla “Artist”? Estas preguntas las contestaremos con la siguiente consulta, escríbela y ejecútala:
“Left Join” entre “Artist” y “Album”.
Ahora unimos ambas tablas con la cláusula LEFT JOIN y usamos la misma condición en su cláusula ON. Conservamos el orden por la columna identificador de la tabla “Artist”. Si observas ahora el resultado nos arroja más filas, 418, a diferencia de cuando aplicamos el INNER JOIN que arrojó 347.
Deslízate hacia abajo en los resultados hasta ubicarte en las filas marcadas con rojo en la imagen de los resultados de la consulta que hicimos con INNER JOIN.
Resultados del “Left Join”.
Puedes observar que si existen filas en la tabla “Artist” con los valores 25, 26, 28 al 36, etc. en su columna “ArtistId” que no aparecieron en la consulta INNER JOIN que ejecutamos. También observa que las columnas de los resultados que pertenecen a la tabla “Album” no tienen valores, es decir tienen valores nulos (“NULL”). Estas columnas aparecen marcadas con rojo en la imagen.
Aquí radica la diferencia entre la cláusula INNER JOIN y LEFT JOIN, que aunque las dos unen o combinan dos tablas, INNER JOIN sólo mostrará las filas que cumplan con la condición especificada en su cláusula ON mientras que LEFT JOIN, como su nombre lo indica, mostrará todas las filas de la tabla que se encuentre a la izquierda (“left” en inglés”) cumpla o no cumpla con la condición de su cláusula ON. Si las filas de la tabla a la izquierda no tienen coincidencia con alguna fila de la tabla a la derecha de la cláusula LEFT JOIN, de todas forma aparecerán en los resultados de la consulta y las columnas pertenecientes a la tabla a la izquierda de la cláusula se rellenarán con valores nulos como observamos.
Conociendo el comportamiento de la cláusula LEFT JOIN, queremos encontrar aquellos artistas que no tenga un álbum de música asociado, es decir, las filas en la tabla “Artist” que el valor de su identificador, columna “ArtistId”, no se encuentre en la tabla “Album”. Escribe y ejecuta la siguiente sentencia SQL:
Mostrando artistas sin álbum.
Observa que a la consulta original le agregamos la cláusula WHERE para que sólo nos muestre en los resultados las filas que regrese con valor nulo en la columna “al.AlbumId”. Esto lo logramos comparando dicha columna con el operador “IS NULL”.
El operador “IS NULL” es usado cuando queremos sabe si el valor de un campo no tiene valor o esta nulo. No use el operador de comparación “=” (igual) con valores nulos.
Tomamos la columna identificador de “Album” ya que las columnas que son identificadores o llaves primarias, nunca tienen valor nulo.
Existen 71 artistas o filas que no tienen un álbum asociado en la base de datos. Como ejercicio, modifica la consulta para que sólo muestre las columnas de la tabla “Artist” ya que como vemos en los resultados de la consulta anterior las columnas de la tabla “Album” tienen todas valores nulos. ¡Hasta pronto!
Este curso básico de SQL no pretende ser técnico, pretende ser más práctico, así que no usaré conceptos técnicos para explicar como se relacionan las tablas entre sí. Los conceptos teóricos y técnicos en diseño de base de datos, lo dejaré para un curso posterior. Lo importante aquí es cómo consultar varias tablas relacionadas entre sí.
En la sección anterior vimos como consultar varias tablas separando las tablas con comas o usando la cláusula CROSS JOIN en la cláusula FROM de una sentencia SELECT lo cual tenía como resultado un producto cruzado de las filas de las tablas listadas.
Para entender el INNER JOIN necesitamos conocer como relacionar la información contenida en varias tablas dentro de una base de datos, vaya, relacional.
Relaciones entre tablas.
Para no ocupar tanto espacio o tener datos repetitivos dentro de una base de datos, cuando se diseña ésta, se opta por dividir la información en varias tablas. Pongamos un ejemplo: en la base de datos Chinook, su diseñador optó por dividir la información contenida en una factura en tres tablas: “Invoice” (factura), “InvoiceLine” (linea de factura) y “Customer” (cliente).
La primera tabla contiene los datos generales de la factura, la segunda, los datos de los artículos adquiridos pertenecientes a una factura, y la tercera, los datos del cliente que hizo la compra. ¿Y cómo se sabe que líneas de factura pertenecen a que factura y que a que cliente se hizo la factura?
Veamos la estructura de cada tabla, es decir que columnas o campos tienen. Empecemos por la tabla “Invoice”. Para ello seleccionamos la pestaña “Database Structure” y expandimos la Sección “Tables”, si no se encuentra expandida dando clic en la flecha a su izquierda, localizamos la tabla “Invoice” en la lista de tablas y damos clic en la flecha a su izquierda para que nos muestre las columnas o campos que contiene:
Estructura de la tabla “Invoice”.
La primera columna de la tabla es “InvoiceId”, esta columna contiene un valor único, no repetible, para cada fila de la tabla. A esta columna se le conoce como el identificador de la tabla y se le suele nombrar colocando primero el nombre de la tabla, seguido por “Id” (identificador): “Invoice” + “Id” = “InvoiceId”. Asignarle el nombre con esta regla es muy común en el diseño de una base de datos. Esto para la pronta identificación del identificador. Este tipo de columnas sirven como identificador único de una fila o registro en una tabla. Se les conoce, en el diseño de una base de datos, como llave primaria o primary key. Aunque una llave primaria puede constar de una o más columnas.
En base de datos, es importante contar con un campo o combinación de campos, en cada tabla, que nos permita obtener una fila única de dicha tabla. Si esto no se cumple, se dice que la tabla esta mal diseñada. Anteriormente se optaba por asignar como identificador o llave primaria alguna columna o columnas que sabíamos que su valor iba a ser único para cada fila. Por ejemplo, para una tabla que contuviera datos de empleados, se podía optar usar su RFC o CURP (en México) como su campo identificador, ya que, en teoría, no puede haber dos personas con el mismo RFC o CURP.
Últimamente y debido a la automatización de los procesos, se ha optado por usar una columna de tipo numérico entero como su identificador uno. Este es el caso de la base de datos Chinook que estamos usando. Todas sus tablas contienen un identificador de tipo entero. Específicamente de la tabla que estamos tratando, “Invoice”, su identificador único es la columna “InvoiceId”.
La segunda columna, “CustomerId”, que por la explicación de como se nombran las columnas que actúan como identificador único, deducimos que es es un campo cuyos valores identifican una fila única en la tabla “Customer” (cliente), nos permite relacionar “Invoice” con “Customer”. Lo veremos más adelante cuando escribamos las consultas.
Las columnas restantes son datos que complementan los datos generales de la factura: fecha de la factura (“InvoiceDate”), dirección de envío (“BillingAdress”), etc.
Localizamos ahora la tabla “Customer”, que contiene la información de los clientes, y desplegamos su estructura como lo hicimos anteriormente con la tabla “Invoice”:
Estructura de la tabla “Customer”.
¿Cuál es el campo identificador de esta tabla? Si, “CustomerId”. Esta columna es su identificador o llave primaria. Las siguientes columnas contienen los datos generales del cliente como sus nombres (“FirstName”), apellidos (“LastName”), compañía para la que trabaja (“Company”), etc. Por último esta la columna “SupportRepId” que hace referencia a un identificador de otra tabla, es decir que se relaciona con otra tabla.
Veamos ahora la tabla “InvoiceLine”. Esta tabla contiene la información de los artículos que se adquirieron en la factura.
Estructura de tabla “InvoiceLine”.
Nuevamente vemos que tiene, como las tablas anteriores, su columna identificador o llave primaria llamada “InvoiceLineId”. Vemos también que contiene el campo “InvoiceId” que relaciona esta tabla con la tabla “Invoice”. El siguiente campo o columna “TrackId” nos servirá para relacionar esta tabla con la tabla llamada “Track” que contiene la información de la pista de música.
Despliega la estructura del resto de la tablas que aparecen en la lista de tablas de la base de datos y trata de deducir cómo se relacionan las tablas entre sí.
Relacionar y consultar tablas con la cláusula Inner Join.
Una vez que vimos como es que se relacionan las tablas “Invoice”, “InvoiceLine” y “Customer” pasamos a hacer unas consultas que las relacionen entre sí. Primero vamos a relacionar ambas tablas como lo hicimos con CROSS JOIN y limitaremos el número de columnas a mostrar en el resultado para ver con más claridad, en un ejercicio posterior, como funciona el INNER JOIN:
CROSS JOIN limitando columnas.
Recuerda que en lugar de la cláusula CROSS JOIN pudimos usar simplemente una coma (“,”) y hubiéramos obtenido el mismo resultado. La consulta nos da como resultado 922,880 filas, que resulta de la multiplicación de las 412 filas que contiene “Invoice” por las 2240 filas de “InvoiceLine”. Observa que cada fila de “Invoice” se combina con cada una de las filas de “InvoiceId”. La primera columna de los resultados contiene el identificador de “Invoice”, su columna “InvoiceId”, mientras que la última columna de los resultados muestra los valores de la columna “InvoiceId” de la tabla “InvoiceLine” que como vimos anteriormente indica a que factura o “Invoice” pertenece cada línea de factura, es decir a que factura se relaciona o pertenece.
Observa que en los resultados de esta consulta la fila de “Invoice” con identificador o “InvoiceId” con valor 1, se combinan con todas las filas de “InvoiceLine” sin importar si la columna “InvoiceId” de esta última tabla se relaciona o no, es decir si tienen el mismo valor o no. Vamos a modificar esta consulta para que sólo nos muestre la combinación de las filas de ambas tablas pero sólo las que se relacionan, es decir que tengan en sus campos “InvoiceId” el mismo valor:
CROSS JOIN con condición.
Hemos limitado los resultados de la primera consulta agregando la cláusula WHERE que ya hemos explicado anteriormente en el curso. Por cada fila que mostraba originalmente, ahora solamente nos muestra aquellas donde el valor de la columna “InvoiceId” de la tabla “Invoice” es igual al valor de la columna “InvoiceId” de la tabla “InvoiceLine”. Ahora el número de filas que regresa la consulta es de 2,240, mucho menos que las que regresaba la consulta original.
Ahora la consulta nos muestra cada factura con sus líneas de factura correspondientes. Ahora vamos a ver como obtener los mismos resultados pero empleando la cláusula INNER JOIN. Escribe y ejecuta la siguiente consulta:
Consulta con cláusula INNER JOIN.
Observa que eliminamos la cláusula CROSS JOIN y la sustituimos por la cláusula INNER JOIN, así también eliminamos la cláusula WHERE pero la condición de ésta la trasladamos a la cláusula ON de INNER JOIN. El resultado es idéntico a la de la consulta anterior: el número de filas regresadas es el mismo 2,240 y sólo nos muestra las filas que cumplen la condición que hemos especificado. Si con ambas consultas obtenemos los mismos resultados ¿por qué usar INNER JOIN en lugar de CROSS JOIN o la coma (“,”)? Lo usamos para mejor claridad de la sentencia SELECT cuando unimos o relacionamos más de dos tablas.
Escribe y ejecuta la siguiente consulta donde mostramos todas las columnas de ambas tablas que se estamos relacionando o uniendo:
Usando INNER JOIN.
En la consulta, hemos asignado el alias “i” ala tabla “Invoice” y el alias “il” a la tabla “InvoiceLine”. La cláusula INNER JOIN se especifica dentro de la cláusula FROM de la sentencia SELECT y se coloca entre las tablas que se quieren relacionar o que tienen relación, en este caso entre las tablas “Invoice” e “InvoiceLine”. Enseguida se especifica la palabra ON y después de esta los campos o columnas por los que se va a hacer la relación, en este caso la columna “InvoiceId” de la tabla “Invoice” y la columna con el mismo nombre de la tabla “InvoiceLine”, especificando que queremos que los valores entre ambas columnas de ambas tablas sean iguales: “ON i.InvoiceId = il.InvoiceId”. Agregamos la clásula ORDER BY, para ordenar los resultados por la columna “InvoiceId” de la tabla “Invoice”.
Si observamos los resultados vemos que primero aparecen las columnas de la primera tabla que especificamos en la cláusula FROM, la tabla “Invoice” y enseguida las columnas de la tabla “InvoiceLine” que es con la que estamos relacionando la primera. Podemos observar también que filas de las columnas que pertenecen a la tabla “Invoice” en los resultados están duplicadas. Por ejemplo, en las dos primeras filas de los resultados, vemos que las columnas que pertenecen a la tabla “Invocie” tienen los mismos valores. Ambas filas tienen el identificador de la factura, es decir la columna “InvoiceId”, con valor 1 y el resto de las columnas los mismos valores en ambas filas.
Sin embargo, si nos desplazamos hacia la derecha, hasta llegar al final las columnas, veremos que los valores de las columnas que pertenecen a la tabla “InvoiceLine” no son exactamente iguales en esas dos primeras filas:
Encerradas con rojo vemos las dos primeras filas con las columnas y sus valores, pertenecientes a la tabla “InvoiceLine” o líneas de factura. Subrayados con rojo vemos los valores de la columna “InvoiceId” de esas dos filas. Vemos que tiene el valor 1. Eso indica que esas dos filas pertenecen a la factura con identificador 1, el mismo identificador que tienen los datos correspondientes a la factura (“Invoice”). Vamos a tratar de clarificar esto un poco más. Escribe y ejecuta las siguientes sentencias y observa los resultados.
Datos de factura con identificador, “InvoiceId” igual a uno.
Datos de la linea de factura pertenecientes a la factura con identificador uno.
Como pueden observar, en “Invoice” sólo hay una fila que tiene su identificador, “InvoiceId”, igual a 1. Mientras que en “InvoiceLine” existen dos filas que cumplen la condición “InvoiceId = 1”. Esto significa que esas dos lineas de factura , son parte o pertenecen a la factura 1. ¿Por qué al hacer la combinación o unión de ambas tablas mediante la cláusula INNER JOIN se duplican los valores de las columnas pertenecientes a los valores de “Invoice”? Porque se cumple la condición especificada en la cláusula ON: que el valor de la columna “InvoiceId” de “Invoice” sea igual a la columna “InvoiceId” de “InvoiceLine”.
Si vuelves a revisar los resultados obtenidos con la consulta donde empleamos el INNER JOIN verás que todas las filas cumplen la condición especificada en la cláusula ON de la cláusula INNER JOIN.
Veamos un ejemplo práctico. Hemos dicho anteriormente en este curso que, ya en la práctica, es raro hacer consultas sin especificarle una condición para limitar el número de filas o registros a mostrar, sobre todo cuando las tablas contienen miles o millones de filas o registros. También en ocasiones, también limitamos las columnas a mostrar. Así que agregaremos una condición a nuestra consulta original y limitaremos las columnas a mostrar:
Consulta INNER JOIN limitada.
La consulta anterior asume que nos pidieron obtener todos los artículos facturados al cliente que tiene en su identificador el valor 2, “CustomerId = 2” y que muestre los resultados ordenados por el identificador de la factura, “InvoiceId”. Recuerda que los datos de los artículos facturados se encuentran en la tabla “InvoiceLine”, pero dicha tabla no contiene el identificador del cliente. La columna identificador del cliente, “CustomerId”, lo contiene “Invoice”, la factura, por lo cual es necesario hacer la unión de ambas tablas a través de la cláusula INNER JOIN para saber a que cliente pertenecen los artículos o lineas de factura comprados. Dicha unión la hacemos por medio de la columna común y que relaciona ambas tablas: el campo “InvoiceId”. Eso se lo indicamos en la cláusula ON.
Los resultados de la anterior consulta no son presentables del todo. Tenemos un identificador de cliente que nos regresa un valor numérico y un identificador de la pista de música adquirida, “TrackId”, pero no sabemos el nombre del cliente ni el nombre de la pista que adquirió.
Vamos a modificar la consulta para hacer más presentables los resultados de la misma. Escribe y ejecuta la siguiente consulta:
Consulta INNER JOIN modificada.
Observa la sintaxis cuando se unen varias tablas con la cláusula INNER JOIN. Las condiciones especificadas en la cláusula ON deben incluir los nombres de columnas cuyas tablas hayan sido especificadas anteriormente y la columna o columnas de la tabla especificada en el INNER JOIN. Por ejemplo, la cláusula ON del segundo INNER JOIN que especifica que vamos a unir la tabla “Customer”, la tabla que contiene los datos de los clientes y que le asignamos el alias “c”, hace referencia a la columna “i.CustomerId” que corresponde a la columna “CustomerId” de la tabla “Invoice” que anteriormente ya habíamos especificado en el primer INNER JOIN. Si en la cláusula ON especificamos un campo de una tabla que no ha sido nombrada o especificada anteriormente nos marcará error y no se ejecutará.
Como ejercicio, observa la estructura de la tabla “Track”, deduce a que tablas se relaciona observando los campos que terminan en “Id” y haz una consulta SELECT utilizando la cláusula INNER JOIN para relacionar dichas tablas.
En la siguiente sección veremos la cláusula LEFT JOIN que otro tipo de unión de tablas. ¡Hasta pronto!
