martes, 7 de mayo de 2013

Joins en SQL Server


Los joins son utilizados para extraer información de dos o más tablas. Por razones de performance se debe de limitar el número de tablas utilizados en un join, entre más tablas se utilicen más tiempo va a tardar SQL Server en procesar la información. Los join de preferencia deben de estar basados entre la llave primaria y la llave foránea de las tablas.
SQL Server soporta tres tipos de joins: inner, outer y cross, además del self join, el cual es un join entre la misma tabla. Se describen a continuación:
Inner
Se usa cuando se quieren ligar dos tablas que tienen valores en común en una o más columnas.
Outer
Se usa cuando se quieren ligar dos tablas juntas, pero se quiere que el resultado no solo contenga los registros que se cumplen con la condición del join, sino también cualquier registro que no cumpla de una tabla o de las demás.
Cross
Se usa cuando se quierenquieran ligar todos los registros de una tabla con cada registro de otra tabla.
Self 
Se usa cuando se quiereligar una tabla así misma. Esto en ocasiones en que las columnas de una tabla están relacionadas.

EJEMPLOS

NOTA: estos ejemplos están realizados en SQL Server 2008.
--Creación de la base de datos Personal
CREATE DATABASE Personal
GO

--usamos la base datos creada previamente
USE Personal
GO

--creamos la tabla Personal_A con los campos ID_PA
--como llave primaria y Nombre_PA como nombre del Departamento
CREATE TABLE Personal_A
(
ID_PA INT PRIMARY KEY IDENTITY(1,1),
Nombre_PA VARCHAR(30)
);

--ahora la tabla empleado
CREATE TABLE Personal_B

ID_PB INT PRIMARY KEY IDENTITY(1,1),
Nombre_PB VARCHAR(30)
);

--insertamos cuatro personas en la tabla Personal_A
--en SQL Server se pueden hacer múltiples insert de la siguiente manera
insert into Personal_A(Nombre_PA)values('José'),('Hugo'),('Victor'),('Edaena')

--ahora insertamos 4 personas en la tabla Personal_B
insert into Personal_B(Nombre_PB)values('Omar'),('José'),('Diana'),('Hugo')

--inner join
select *from Personal_A inner join Personal_B on Nombre_PA=Nombre_PB
--El resultado son sólo el conjunto de registros en los que
--el nombre de la persona coincide en ambas tablas.


--full  outer join
select *from Personal_A full outer join Personal_B on Nombre_PA=Nombre_PB
--El resultado es el conjunto total de registros de ambas tablas,
--coincidiendo aquellos registros cuando sea posible. Si no hay
--coincidencias, se asignan nulos.

--full outer join where
select *from Personal_A full outer join Personal_B on Nombre_PA=Nombre_PB
where Nombre_PA is null or Nombre_PB is null
--El resultado es un conjunto de records únicos en la Tabla Personal_A y en la Tabla Personal_B,
--hacemos el Full Outer Join y excluimos los registros que no queremos con el Where, en este caso
--fueron los que tienen una coincidencia nula entre ambas tablas.

--left outer join
select *from Personal_A left outer join Personal_B on Nombre_PA=Nombre_PB
--El resultado son todos los registros de la tabla Personal_A, y si es posible las
--coincidencias con la tabla Personal_B. Si no hay coincidencias, el lado derecho mostrará nulos.

--left outer join where
select *from Personal_A left outer join Personal_B on Nombre_PA=Nombre_PB
where Nombre_PB is null
--El resultado es un conjunto de registros que sólo están en la Tabla Personal_A, no en la Tabla Personal_B.
--Hacemos lo mismo que en un Left Outer Join, pero eliminamos los registros que no queremos de la Tabla Personal_A
--con el Where, en este caso fueron los Nombres de la tabla Personal_B donde la coincidencia con los nombres
--de la tabla Personal_A es nula.

--right outer join
select*fromPersonal_ArightouterjoinPersonal_BonNombre_PA=Nombre_PB
--El resultado son todos los registros de la tabla Personal_B, y si es posible las
--coincidencias con la tabla Personal_A. Si no hay coincidencias, el lado izquierdo mostrará nulos.

--right outer join where
select *from Personal_A right outer join Personal_B on Nombre_PA=Nombre_PB 
where Nombre_PA is null
--El resultado es un conjunto de registros que sólo están en la Tabla Personal_B, no en la Tabla Personal_A.
--Hacemos lo mismo que en un Right Outer Join, pero eliminamos los registros que no queremos de la Tabla Personal_A
--con el Where, en este caso fueron los Nombres de la tabla Personal_A donde la coincidencia con los nombres
--de la tabla Personal_B es nula.








--cross join
select *from Personal_A cross join Personal_B
--Existe también la posibilidad de cruzar todos los registros con todos (producto cartesiano)
--la consulta arroja un número proporcional al producto de los registros de la tabla Personal_A
--con los de la tabla Personal_B, nosostros tenemos 4 registros en cada tabla 4 x 4 = 16.
--Se debe de tener CUIDADO al hacerlo con tablas con gran cantidad de registros
Para tener un ejemplo del self join necesitaremos dos tablas más, Departamento y Empleado, en la tabla Empleado se registran los empleados y entre sus datos se encuentra su supervisor el cual está registrado en la misma tabla.
create table Departamento
      (
            No_Dpto varchar(2) not null primary key,
            Nombre varchar(20)not null,
            Localizacion varchar(30)
            );
createtableEmpleado
      (
            No_Emp varchar(4) not null primary key,
            Nombre_Emp varchar(15) not null,
            Trabajo varchar(9) not null,
            Superv_Emp varchar(4references Empleado(No_Emp),
            Fecha_reg date not null,
            Salario money,
            No_Dpto varchar(2) not null references Departamento(No_Dpto)
            );
insert into Departamento(No_Dpto,Nombre,Localizacion)values('10','ACCOUNTING','NEW YORK');
insert into Departamento(No_Dpto,Nombre,Localizacion)values('20','RESEARCH','DALLAS');
insert into Departamento(No_Dpto,Nombre,Localizacion)values('30','SALES','CHICAGO');
insert into Departamento(No_Dpto,Nombre,Localizacion)values('40','OPERATIONS','BOSTON');

insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7839','KING','PRESIDENT',null,'17/11/81',5000,'10');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7566','JONES','MANAGER','7839','02/04/81',2975,'20');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7698','BLAKE','MANAGER','7839','01/05/81',2850,'30');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7782','CLARK','MANAGER','7839','09/06/81',2450,'10');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7499','ALLEN','SALESMAN','7698','20/02/81',1600,'30');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7521','WARD','SALESMAN','7698','22/02/81',1250,'30');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7844','TURNER','SALESMAN','7698','09/08/81',1500,'30');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7654','MARTIN','SALESMAN',NULL,'28/09/81',1250,'30');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7902','FORD','ANALYST','7566','03/12/81',3000,'20');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7788','SCOTT','ANALYST','7566','09/12/82',3000,'20');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7876','ADAMS','CLERK','7788','12/01/83',1100,'20');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7900','JAMES','CLERK','7698','03/12/81',950,'30');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7934','MILLER','CLERK',NULL,'23/01/82',1300,'10');
insert into Empleado(No_Emp,Nombre_Emp,Trabajo,Superv_Emp,Fecha_reg,Salario,No_Dpto)
values('7369','SMITH','CLERK','7902','17/12/80',800,'20');

--self join
SELECT e.Nombre_Emp as Empleado,m.Nombre_Emp as Jefe
FROM Empleado as JOIN Empleado as m
ON e.Superv_Emp=m.No_Emp order by m.Nombre_Emp




No hay comentarios:

Publicar un comentario