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(4) references 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 e JOIN Empleado as m

ON e.Superv_Emp=m.No_Emp order by m.Nombre_Emp