Clausula JOIN en Lenguaje SQL

Los JOINs en SQL sirven para combinar filas de dos o más tablas basándose en un campo común entre ellas, devolviendo por tanto datos de diferentes tablas. Un JOIN se produce cuando dos o más tablas se juntan en una sentencia SQL.

En el gráfico que se muestra a continuación, cada tabla se representa como un conjunto (diagrama de Venn) lo que nos da una idea de las filas de que tabla devuelve cada JOIN diferente.

Unión de varias tablas.

Este es el JOIN más sencillo posible.

SELECT * FROM tabla1, tabla2;

SQL toma cada fila de la tabla1 y la une con todas las filas de la tabla2. Así que si tenemos 8 filas en la tabla 1 y 5 en la tabla2 tendremos 8 * 5 = 40 filas. Es decir hace un producto cartesiano.

¿Por qué ocurrió esto? SQL no sabe qué hacer con los resultados de las dos tablas, así que, devolvió todas las combinaciones posibles. ¿Cómo podemos cambiar este resultado? 

SELECT * FROM tabla1, tabla2 WHERE tabla1.id_tabla1 = tabla2.id_tabla1;

Establecimos una nueva condición en la cláusula WHERE. Ahora, solo vemos las conexiones en las que el id de la tabla1  es el mismo que el id_tabla1 de la tabla 1 definido en la tabla2.

Si hay varias tablas, hay que referirse a las columnas indicando el nombre de la tabla y de la columna separados por un punto (.). Así, la columna id_tabla1 de la tabla2 se convierte en tabla2.id_tabla1 y, así, sucesivamente.

Se debe relacionar la clave primaria de cada tabla, así por ejemplo, el id de la tabla1  aparece en la tabla2 en el campo id_tabla1.

Combinar dos tablas es una operación tan popular y frecuente que SQL proporciona una palabra especial para ello: JOIN. Existen varios tipos de JOIN

Combinar tablas con JOIN

Observa el siguiente ejemplo:

SELECT * FROM Tabla_persona JOIN Tabla_coche ON Tabla_persona.id = Tabla_coche.id_propietario;

Queremos combinar las tablas Tabla_persona y Tabla_coche, por lo que utilizamos la palabra clave JOIN entre sus nombres.

SQL también necesita saber cómo combinar las tablas, así que, usamos otra palabra clave (ON). Tras ella, establecemos nuestra condición: combinar solamente aquellas filas en las que el id de persona sea el mismo que el id_propietario de coche.

INNER JOIN

JOIN es, en realidad, solo uno de los diferentes métodos que existen para hacer combinaciones en SQL. Es el más común, por lo que siempre se aplica de forma predeterminada cuando se escribe la palabra clave JOIN en las consultas de SQL. Pero, técnicamente hablando, su nombre completo es INNER JOIN.

La consulta del ejercicio anterior también puede escribirse de la siguiente manera:

SELECT * FROM Tabla_persona INNER JOIN Tabla_coche  ON Tabla_persona.id = Tabla_coche.id_propietario;

Las INNER JOIN (o JOIN) solo muestran las filas de las dos tablas en las que las columnas coinciden. Solo se devuelven las personas que son propietarias de un coche, y viceversa. Las personas que no son propietarios de un coche no aparecen en el resultado.

Las personas que tienen el valor NULL en la columna id_propietario no aparecen en el resultado de la INNER JOIN.

LEFT JOIN

Las LEFT JOIN devuelven todas las filas de la tabla izquierda (la primera tabla de la consulta) más todas las filas que coinciden de la tabla derecha (la segunda tabla de la consulta).

SELECT * FROM tabla1 LEFT JOIN tabla2   ON tabla1.id_tabla1 = tabla2.id_tabla1;

RIGHT JOIN

Las RIGHT JOIN devuelven todas las filas de la tabla derecha (la segunda tabla de la consulta) más todas las filas que coinciden de la tabla izquierda (la primera tabla de la consulta).

SELECT * FROM tabla1 RIGHT JOIN tabla2  ON tabla1.id_tabla1 = tabla2.id_tabla1;

La RIGHT JOIN devuelve todas las filas de la tabla anterior. El orden de las tablas en las LEFT y RIGHT JOIN es importante. En otras palabras, tabla1 RIGHT JOIN tabla2 es lo mismo que tabla2 LEFT JOIN tabla1. 

FULL JOIN

Este tipo de JOIN devuelve todas las filas de ambas tablas y combina las filas cuando hay una coincidencia. Una FULL JOIN es una unión de una LEFT JOIN y una RIGHT JOIN.

SELECT * FROM tabla1 FULL JOIN tabla2   ON tabla1.id_tabla1 = tabla2.id_tabla1;

OUTER

Las tres combinaciones que acabamos de mencionar LEFT JOIN, RIGHT JOIN y FULL JOIN son abreviaturas. Todas ellas son, en realidad, tipos de OUTER JOIN (LEFT OUTER JOIN, RIGHT OUTER JOIN y FULL OUTER JOIN). Se las puede añadir la palabra clave OUTER y los resultados de las consultas seguirán siendo los mismos.

Por ejemplo, para la LEFT JOIN se podría escribir lo siguiente:

SELECT * FROM tabla1 LEFT OUTER JOIN tabla2 ON tabla1.id_tabla1 = tabla2.id_tabla1;

NATURAL JOIN

No requiere la cláusula ON con la condición de la combinación:

SELECT * FROM tabla1 NATURAL JOIN tabla2;

Para los NATURAL JOIN no hace falta escribir los nombres de columnas, porque siempre combinan las dos tablas en base a las columnas que tienen el mismo nombre.

En este ejemplo, la tabla1 y la tabla2 se combinan en base a sus respectivas columnas de id, lo que no tiene mucho sentido.

SELECT * FROM tabla1 NATURAL JOIN tabla2; 

devuelve el mismo resultado que la siguiente consulta:

SELECT * FROM tabla1 JOIN tabla2 ON alumno.id_tabla1= habitacion.id_tabla2;

Sin embargo, podemos crear las tablas de forma que las NATURAL JOIN sean útiles. Si tuvieramos las siguientes tablas:

coche(id_coche, marca, modelo) 

propietario(id_del_propietario, nombre, id_coche)

Entonces, tendría mucho sentido utilizar NATURAL JOIN, porque combinaría las dos tablas en base a la columna id_coche. Por lo que, habría que escribir menos para combinar las dos tablas.

Clausula WHERE en Lenguaje SQL

En una instrucción SQL, la cláusula WHERE especifica criterios que tienen que cumplir los valores de campo para que los registros que contienen los valores se incluyan en los resultados de la consulta.

Aparece obligatoriamente después de SELECT [lista de campos] FROM [lista de tablas] WHERE

Después de WHERE podemos añadir los operadores necesarios

Operadores condicionales: =, !=, <, >, <= , >=

Símbolo de distinto puede ser  != o <>

Por ejemplo

SELECT * FROM nombre_tabla WHERE campo <> 1999

SELECT * FROM nombre_tabla WHERE campo != 1999

Devolverán el mismo resultado.

Operadores lógicos: OR, AND

Los operadores AND, OR y NOT son operadores lógicos importantes en SQL. Ayudan a combinar las condiciones utilizadas para filtrar los registros.

SELECT campo4 FROM nombre_tabla WHERE campo1 < 2005 OR campo2 < 10000

Operador BETWEEN  en Oracle incluye los extremos

SELECT * FROM nombre_tabla WHERE campo BETWEEN 2 AND 4;

Saca 2,3 y 4

Operador NOT

Se quitará de los resultados todo lo que se indique después de NOT

SELECT campo1, campo2, campo3 FROM nombre_tabla WHERE campo3 NOT BETWEEN 1995 AND 2005;

Combinación de condiciones

Para combinar condiciones se utilizan paréntesis

SELECT campo1 FROM nombre_tabla WHERE (campo2 < 1999 OR campo2 > 2005) AND ( campo3 < 4000 OR campo3 > 10000);

Operador LIKE y %

¿qué pasa si no sabemos exactamente qué letras estamos buscando? Con los valores tipo texto, siempre se puede utilizar el operador LIKE en lugar del signo igual

Con LIKE se puede usar del signo de porcentaje (%). El signo de porcentaje que se aplica en el ejemplo equivale a cualquier número (cero o más) de caracteres desconocidos.

Como resultado, obtendremos todos los valores cuyo nombre empiece por la letra "F". Es posible que no recordemos el nombre exacto de un valor, pero sabemos que empieza por F.

SELECT campo1, campo2, campo3 FROM nombre_tabla WHERE campo1 LIKE 'F%';

El signo de porcentaje (%) se puede poner en cualquier lugar dentro de las comillas simples y tantas veces como sea necesario.

SELECT * FROM nombre_tabla WHERE campo LIKE '%A%';

Seleccionará cualquier valor cuyo nombre contenga al menos una "A".  El signo de porcentaje (%) también puede equivaler a no tener a ningún carácter, por lo que el nombre también puede empezar o terminar con la "A".

Carácter de subrayado (_)

Es posible que, a veces, no recordemos solo una letra de un nombre concreto. Imagina que queremos encontrar a una chica que se llama... ¿Catherine? ¿Katherine?

SELECT * FROM nombre_tabla WHERE nombre LIKE '_atherine';

El carácter de subrayado (_) coincide exactamente con un solo carácter. Ya sea Catherine o Katherine, la expresión devolverá una fila.

NULL

NULL es un valor especial. No se puede utilizar el signo de igual para comprobar si algo es NULL. Básicamente, no funcionará, se utiliza  IS NULL.

SELECT campo1, campo2, campo3 FROM nombre_tabla WHERE campo IS NULL;

Esta consulta devolverá solo las filas cuyo campo es null, es decir es desconocido.

SELECT * FROM nombre_tabla WHERE campo IS NULL;

Si se establece una condición en una columna concreta, por ejemplo,  campo < 70, las filas en las que la edad sea NULL quedarán siempre excluidas de los resultados.

En ningún caso NULL es igual a cero.

SELECT * FROM nombre_tabla WHERE campo > 0;

No devolverá los registros con campo NULL

NOT NULL

Para comprobar si una columna tiene un valor, utilizamos la instrucción especial IS NOT NULL.

SELECT campo1 FROM nombre_tabla WHERE campo2 IS NOT NULL;

Este código selecciona solo las filas que tienen cuyo campo2 tiene un valor no nulo.

Operadores matemáticos básicos

SELECT * FROM nombre_tabla WHERE (campo * 12) > 50000;

En el ejemplo anterior, utilizamos el asterisco (*) para multiplicar valor del campo por 12 y, así, si campo es un salario mensual, obtendremos el salario anual. A continuación, podemos hacer lo que queramos con el nuevo valor; en este caso, lo comparamos con 50.000 €.

De este modo, se pueden usar signos para sumar (+), restar (-), multiplicar (*) y dividir (/) números.

Ejemplo: Seleccionar los artículos para los que se pagan más de 2000 € en impuestos. El importe de los impuestos de todos los artículos es el 20 % de su precio. Se multiplica que el precio por 0.2 para obtener el importe de los impuestos.

SELECT * FROM artículos WHERE precio * 0.2 > 2000;

Ejercicio

Seleccionar todas las columnas de artículos que cumplan lo siguiente:

se fabricaron entre 1999 y 2005;

no son marca ACME;

su modelo que empieza por "P" o "F";

tienen fijado un precio;

SELECT * FROM coche WHERE fecha_de_produccion BETWEEN 1999 AND 2005 AND marca != 'ACME' AND (modelo LIKE 'P%' OR modelo LIKE 'F%') AND precio IS NOT NULL;

Buscar una cadena de texto en múltiples archivos en Notepad++

Hay veces que queremos buscar una palabra en muchos archivos diferentes y no podemos estar abriendo y cerrando archivos porque pueden incluso ser miles. Para ello hay una función muy útil en Notepad++ que se llama buscar en archivos y permite buscar una cadena de texto en todos los archivos de una carpeta o incluso en sus subcarpetas.

Para ello desde Notepad++ pulsamos Buscar-> Buscar en archivos.

Esto nos abre un cuadro de diálogo donde le indicaremos el texto a buscar, los filtros de archivo (si queremos que busque en todos pondremos *.*, si queremos que busque solo en .txt podemos poner *.txt) En carpeta, le indicaremos la carpeta en la que queremos que realice la búsqueda, si queremos incluir subcarpetas, podemos chequear el checkbox "En todas las subcarpetas" que está a la derecha.

Pulsamos el botón buscar todo y buscará la cadena de texto en todos los archivos dentro de la carpeta o/y subcarpetas.

AL finalizar la búsqueda abrirá una ventana de resultados, podremos ver en que archivos y líneas se encontró la cadena de texto.

Podemos hacer clic en los resultados para ir directamente a esa ubicación en el archivo. Con el botón derecho del ratón desde la ventana de resultados podemos abrir los ficheros que contengan la cadena buscada.

Seleccionar hasta el cursor con Notepad++

Hay veces que tenemos archivos muy grandes y no queremos seleccionar todo, sino solo una parte, pero arrastrando el ratón podemos tardar horas. La mejor opción para esto es seleccionar hasta el cursor. 

En Notepad++ se hace del siguiente modo:

Con las teclas de atajo podemos seleccionar desde nuestra posición hasta el final con 

Ctrl+ Shift+Fin

O desde el principio hasta nuestra posición con 

Ctrl+ Shift+Inicio

Si queremos seleccionar entre dos posiciones intermedias tenemos este método:

Nos colocamos en la parte del archivo hasta la que queremos seleccionar y pulsamos

Editar-> Inicio/Fin selección

 

Esto deja la opción chequeada

 

Ahora vamos hacia arriba o hacia abajo hasta la parte superior del documento  que deseamos seleccionar nos colocamos con el cursor donde queramos que comience o termine la selección y deschequeamos  de nuevo.

 

Con estos sencillos pasos podemos seleccionar sectores concretos de un documento.

Resumiendo:

1- Nos colocamos al principio/final de la zona que queremos seleccionar

2- Editar-> Inicio/Fin selección 

3- Nos colocamos al final/principio de la zona que queremos seleccionar

4- Editar-> Inicio/Fin selección 

Cargar rápidamente scripts de SQL con PLSQL Developer

Hay veces que tenemos un script con miles de instrucciones INSERT INTO o similar en un  fichero de Texto y queremos ejecutarlo.  Si lo copiamos y lo pegamos en nuestra ventana SQL Window (Ventana roja)  de PLSQL Developer. 

puede tardar bastante tiempo en ejecutarse, eso sin contar con lo problemático que puede ser abrir el fichero para copiarlo todo y pegarlo en la ventana.

Hay otra forma más cómoda y algo más rápida que no nos obliga a abrir el fichero para copiar su contenido y pegarlo en la ventana. Para ello basta con abrir una Command Window (recuadro azul con flecha de la figura anterior).

Una vez hemos abierto esta ventana simplemente tenemos que escribir el nombre y la ruta del fichero que contiene el script con un @ delante y pulsar Enter.

 

SQL> @C:\Ruta\subdirectorio\FICHERO_CON_SCRIPT_ORACLE.sql

Conviene insertar en nuestro script un comando COMMIT; cada cierto número de líneas para evitar saturar la base de datos.

Redes neuronales para predicciones

Veremos el concepto de implementación de modelos predictivos y predicciones utilizando redes neuronales. El análisis predictivo es una forma de análisis en el que trabajamos con datos históricos para construir modelos predictivos. Podemos utilizar varios algoritmos matemáticos, estadísticos y de redes neuronales para construir modelos predictivos. Identificar y capturar tendencias importantes a partir de los datos históricos y luego aplicar los modelos predictivos en los datos actuales para predecir eventos futuros o proporcionar sugerencias prácticas para obtener resultados óptimos. Cuando construimos modelos de análisis predictivo utilizando redes neuronales, se pueden clasificar en dos tipos diferentes, clasificación y regresión.

                             

Exploraremos las características esenciales de cada una de las categorías de modelos de análisis predictivo para comprender sus diferentes características e implementaciones. La predicción mediante el modelo de clasificación se basa en etiquetas de clase categóricas. Usando el modelo de clasificación, podemos predecir a que categoría pertenece cada dato de entrada,  tiene opciones de salida finitas, y no  están disponibles en un orden dado en particular. La predicción basada en la clasificación implica dos pasos diferentes. Primero construir el modelo clasificador. El segundo paso es utilizar el modelo para la clasificación.

La construcción del modelo clasificador  se conoce como fase de aprendizaje. En este paso, generalmente utilizamos un algoritmo de clasificación para construir el modelo clasificador. Después de construir el modelo, podemos comenzar a usar el modelo para la clasificación. Alimentamos con datos de prueba el modelo para estimar la precisión de las reglas de clasificación. En este paso en particular, realizamos dos análisis diferentes para identificar y segregar los atributos irrelevantes y relevantes. Realizamos un análisis de correlación para identificar los atributos relacionados seguido de un análisis de relevancia para segregar los atributos irrelevantes.

                                

Un modelo de regresión, a diferencia del modelo de clasificación, es esencialmente una metodología estadística que utilizamos a menudo para la predicción numérica. Generalmente utilizamos un modelo de regresión para predecir aquellos valores que son continuos a través de cantidades numéricas. Los valores predichos son típicamente infinitos y están ordenados. Podemos utilizar el análisis de regresión para modelar las relaciones entre una variable de respuesta y una o más variables predictoras. Los modelos de regresión son muy útiles para comprender la asociación entre los cambios en los valores predictores y los cambios en la media de respuesta. Los modelos de regresión pueden incluso ayudar a facilitar las predicciones basadas en los valores de los predictores.

                     

¿Cuáles son los diferentes mecanismos y algoritmos que podemos utilizar para construir modelos predictivos y facilitar las predicciones? Algunos de los mecanismos y algoritmos destacados que podemos utilizar para construir modelos predictivos incluyen la regresión lineal y las redes neuronales. La regresión lineal es la forma más simple de modelo de regresión que podemos usar para construir modelos predictivos con el supuesto de que existen relaciones entre las variables de entrada y salida. También podemos utilizar redes neuronales para construir modelos predictivos. 

La construcción de modelos y la predicción mediante redes neuronales  implican varios pasos iterativos. Comenzamos creando el modelo y usamos ciertos algoritmos y reglas para entrenar el modelo. Luego usamos el modelo en conjuntos de datos de prueba para verificar el nivel de precisión del modelo comparando los valores de salida con los valores reales. Y finalmente, después de haber alcanzado el nivel de precisión deseado, comenzamos a utilizar el modelo con datos actuales para sugerir acciones futuras basadas en los resultados. Aparte de los modelos lineales y las redes neuronales, existen otros algoritmos y métodos que podemos utilizar para construir modelos predictivos.

Algunos de los modelos predictivos destacados que se implementan comúnmente en la actualidad incluyen modelos lineales, árboles de decisión, máquinas de vectores de soporte o SVM y Naive Bayes. Veremos los cuatro modelos predictivos diferentes que acabamos de mencionar. 

Comenzaremos con el modelo lineal y los árboles de decisión. Los modelos lineales son uno de los algoritmos estadísticos más utilizados para construir modelos de relación entre dos variables diferentes. La primera variable se conoce generalmente como variable predictora y la segunda variable se conoce como variable de respuesta. El árbol de decisiones es una forma o tipo de gráfico. Usamos la representación gráfica del árbol de decisiones para representar posibilidades y resultados. Los nodos representan eventos, mientras que los bordes representan condiciones o reglas de decisión. SVM, que es la abreviatura de máquina de vectores de soporte, también se conoce como vectores de soporte.

Este modelo predictivo en particular se basa en la búsqueda de los puntos más cercanos. Es una forma de algoritmo de aprendizaje supervisado y se puede utilizar para construir modelos predictivos para clasificación y regresión. El algoritmo final del modelo predictivo, Naive Bayes, se usa comúnmente para problemas de clasificación. Naive Bayes se basa en el teorema de probabilidad de Bayes, que también se conoce como reglas de Bayes o ley de Bayes. Este algoritmo en particular se usa comúnmente para facilitar la clasificación de texto que involucra conjuntos de datos de entrenamiento de alta dimensión.  

Las redes neuronales brindan la capacidad de procesar datos históricos y actuales para estimar valores futuros. Identifican y extraen cualquier correlación compleja en patrones que estén ocultos en los datos. Los datos que se alimentan como entrada en la capa de entrada pueden tener formatos diversificados de fuentes diversificadas. Por ejemplo, la entrada pueden ser números, textos, imágenes, videos, audios, transmisión de datos, entre otros. Podemos usar los datos de entrada y dividirlos en conjuntos de datos de prueba y de entrenamiento. Usamos los conjuntos de datos de entrenamiento para entrenar la red, y usamos los conjuntos de datos de prueba para probar la precisión de la salida proporcionada por la red. La capa oculta es responsable de las tareas computacionales.

Y en términos de predicción, esta capa puede utilizar funciones de predicción para facilitar la predicción y transmitir los valores predichos a la capa de salida para proporcionar conocimientos útiles o sugerir cursos de acción futuros. Veremos cómo diseñar y construir modelos de predicción de redes neuronales, el proceso de diseño y construcción implica varios pasos iterativos que se repiten hasta que se produce el modelo deseado. El primer paso es identificar el problema o el escenario del caso de uso y, en consecuencia, construir el enunciado del problema o la descripción del caso.

Después de derivar la descripción del caso, podemos comenzar a analizar y revisar la descripción del caso para comprender el resultado esperado del diseño.