SENTENCIA JOIN

Join
La sentencia JOIN en SQL permite combinar registros de dos o más tablas en una base de datos relacional.

En el Lenguaje de Consultas Estructurado (SQL), hay tres tipo de JOIN: interno, externo, y cruzado. En casos especiales una tabla puede unirse a sí misma, produciendo una auto-combinación, SELF-JOIN. Matemáticamente, JOIN es composición relacional, la operación fundamental en el álgebra relacional, y generalizando es una función de composición.

Productos Cartesianos

• Un producto Cartesiano se forma cuando:– Una condición de unión está omitida.– Una condición de unión no es válida.– Todas las filas de la primera tabla se unen a todaslas filas de la segunda tabla.• Para evitar un producto Cartesiano, incluya siempreuna condición de unión válida en una cláusula WHERE.

De equivalencia (equi-join) Se usa para comparaciones de igualdad en el predicado JOIN. Cuando se usan operadores, tales como <> no se puede clasificar en este rango.

Ejemplo:
SELECT *FROM empleado INNER JOIN departamento ON empleado.IDDepartamento = departamento.IDDepartamento

Natural (Natural join) Es una combinación de equivalencia se comparan todas las columnas que tengan el mismo nombre en ambas tablas. La tabla resultante contiene sólo una columna por cada par de columnas con el mismo nombre.

SELECT *FROM empleado NATURAL JOIN departamento
Sintaxis de Uniones Externas

• También puede utilizar una unión externa para ver filasque no cumplen la condición de unión.

• El operador de unión externa es el signo más (+).

SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column(+) = table2.column;SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column(+) = table2.column;SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column = table2.column(+);

Creación de Uniones con la Cláusula ON

• La condición de unión para la unión natural esbásicamente una unión de igualdad de todas las columnas con el mismo nombre.• Para especificar condiciones arbitrarias o especificarcolumnas para unir, se utiliza la cláusula ON.• La condición de unión se separa de otras condicionesde búsqueda.• La cláusula ON facilita la comprensión del código.

En este caso se recuperaran datos de la tabla empleados y departamentos haciendo caso espeficio ON sobre los campos en comun que tenga las tablas.

SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id);

A continuacion se veran mas ejemplos de Joins

1. SELECT e.last_name, e.department_id, D.department_nameFROM employees e , departments dWHERE e.department_id = d.department_id;SELECT e.last_name, e.department_id, d.department_nameFROM employees e JOIN departments dON(e.department_id = d.department_id);

2. SELECT e.job_id, l.location_idFROM employees e, departments d, locations lWHERE e.department_id = d.department_idAND d.location_id = l.location_idAND d.department_id=30;SELECT e.job_id, l.location_idFROM employees e JOIN departments dON(e.department_id = d.department_id)JOIN locations lON(d.location_id = l.location_id)AND d.department_id=30;

3. SELECT e.last_name, d.department_name, l.city, l.location_idFROM employees e, departments d, locations lWHERE e.department_id = d.department_idAND d.location_id = l.location_idAND e.commission_pct is not null;SELECT e.last_name, d.department_name, l.city, l.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id)JOIN locations lON (d.location_id = l.location_id)AND e.commission_pct is not null;

4. SELECT e.last_name, d.department_nameFROM employees e, departments dWHERE e.department_id = d.department_idAND e.last_name LIKE '%a%';SELECT E.last_name, D.department_nameFROM employees e JOIN departments dON (e.department_id = d.department_id)AND e.last_name LIKE '%a%';

5. SELECT e.last_name, e.job_id, e.department_id, d.department_nameFROM employees e, departments d, locations lWHERE e.department_id = d.department_idAND d.location_id = l.location_idAND l.city LIKE '%Toronto%';SELECT e.last_name, e.job_id, e.department_id, d.department_nameFROM employees e JOIN departments dON(e.department_id = d.department_id)JOIN locations lON(d.location_id = l.location_id)AND l.city LIKE '%Toronto%';

6. SELECT e.last_name, e.employee_id "EMP#", j.last_name
"Manager",j.employee_id "Mgr#"FROM employees e, employees jWHERE e..manager_id = j.employee_id;SELECT e.last_name, e.employee_id "EMP#", j.last_name
"Manager",j.employee_id "Mgr#"FROM employees e JOIN employees jON(e.manager_id = j.employee_id);

7. SELECT e.last_name, e.employee_id "EMP#", j.last_name "Manager",j.employee_id "Mgr#"FROM employees e, employees jWHERE e..manager_id = j.employee_id(+)ORDER BY e.employee_id ASC;SELECT e.last_name, e.employee_id "EMP#", j.last_name "Manager",j.employee_id "Mgr#"FROM employees e. JOIN employees jON(e.manager_id = j.employee_id(+))ORDER BY e.employee_id ASC;

8. SELECT e.last_name, d.department_id, c.last_nameFROM employees e, employees cWHERE e.department_id = c.department_idAND e.employee_id <> c.employee_id;SELECT e.department_id, e.last_name,c.last_nameFROM employees e JOIN employees cON(e.department_id = c.department_id)AND e.employee_id <> c.employee_id;

9. SELECT e.last_name, j.job_id, d.department_name, e.salary,
j.job_titleFROM jobs j, employees e, departments dWHERE j.job_id = e.job_idAND e.department_id = d.department_id;SELECT e.last_name, j.job_id, d.department_name, e.salary,
j.job_titleFROM jobs j JOIN employees eON (j.job_id = e.job_id) JOIN departments dON (e.department_id = d.department_id);

10. SELECT e.last_name, e.hire_dateFROM employees e, employees cWHERE c.last_name = 'Davies'AND e.hire_date > c.hire_date;SELECT e.last_name, e.hire_dateFROM employees e JOIN employees cON (c.last_name = 'Davies')AND e.hire_date > c.hire_date;

11. SELECT e..last_name "Emp", E.hire_date, J.last_name"Manager",
J.hire_dateFROM employees e , employees j.WHERE e.manager_id = j.employee_idAND e.hire_date < j.hire_date;SELECT e.last_name "Emp", e.hire_date, j.last_name "Manager",
j.hire_dateFROM employees e JOIN employees jON (e.manager_id = j.employee_id)AND e.hire_date <>