Microsoft SQL Server
Unirse
Buscar..
Introducción
En el lenguaje de consulta estructurado (SQL), un JOIN es un método para vincular dos tablas de datos en una sola consulta, lo que permite que la base de datos devuelva un conjunto que contiene datos de ambas tablas a la vez, o usar datos de una tabla para usarlos como una Filtrar en la segunda mesa. Hay varios tipos de JOIN definidos dentro del estándar ANSI SQL.
Unir internamente
Inner join devuelve solo aquellos registros / filas que coinciden / existen en ambas tablas en función de una o más condiciones (especificadas mediante la palabra clave ON ). Es el tipo más común de unión. La sintaxis general para inner join es:
SELECT *
FROM table_1
INNER JOIN table_2
ON table_1.column_name = table_2.column_name
También se puede simplificar como JOIN :
SELECT *
FROM table_1
JOIN table_2
ON table_1.column_name = table_2.column_name
Ejemplo
/* Sample data. */
DECLARE @Animal table (
AnimalId Int IDENTITY,
Animal Varchar(20)
);
DECLARE @AnimalSound table (
AnimalSoundId Int IDENTITY,
AnimalId Int,
Sound Varchar(20)
);
INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpets');
/* Sample data prepared. */
SELECT
*
FROM
@Animal
JOIN @AnimalSound
ON @Animal.AnimalId = @AnimalSound.AnimalId;
AnimalId Animal AnimalSoundId AnimalId Sound
----------- -------------------- ------------- ----------- --------------------
1 Dog 1 1 Barks
2 Cat 2 2 Meows
3 Elephant 3 3 Trumpets
Uso de la combinación interna con la combinación externa izquierda (Sustituto por No existe)
Esta consulta devolverá los datos de la tabla 1, donde los campos que coinciden con la tabla 2 con una clave y los datos que no están en la Tabla 1 al comparar con la Tabla 2 con una condición y clave
select *
from Table1 t1
inner join Table2 t2 on t1.ID_Column = t2.ID_Column
left join Table3 t3 on t1.ID_Column = t3.ID_Column
where t2.column_name = column_value
and t3.ID_Column is null
order by t1.column_name;
Cruzar
A cross join es una combinación cartesiana, es decir, un producto cartesiano de ambas tablas. Esta unión no necesita ninguna condición para unir dos tablas. Cada fila de la tabla izquierda se unirá a cada fila de la tabla derecha. Sintaxis para una unión cruzada:
SELECT * FROM table_1
CROSS JOIN table_2
Ejemplo:
/* Sample data. */
DECLARE @Animal table (
AnimalId Int IDENTITY,
Animal Varchar(20)
);
DECLARE @AnimalSound table (
AnimalSoundId Int IDENTITY,
AnimalId Int,
Sound Varchar(20)
);
INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpet');
/* Sample data prepared. */
SELECT
*
FROM
@Animal
CROSS JOIN @AnimalSound;
Resultados:
AnimalId Animal AnimalSoundId AnimalId Sound
----------- -------------------- ------------- ----------- --------------------
1 Dog 1 1 Barks
2 Cat 1 1 Barks
3 Elephant 1 1 Barks
1 Dog 2 2 Meows
2 Cat 2 2 Meows
3 Elephant 2 2 Meows
1 Dog 3 3 Trumpet
2 Cat 3 3 Trumpet
3 Elephant 3 3 Trumpet
Tenga en cuenta que hay otras formas de aplicar CROSS JOIN. Esta es una combinación de "estilo antiguo" (en desuso desde ANSI SQL-92) sin condición, lo que resulta en una combinación cruzada / cartesiana:
SELECT *
FROM @Animal, @AnimalSound;
Esta sintaxis también funciona debido a una condición de unión "siempre verdadera", pero no se recomienda y debe evitarse, en favor de la sintaxis explícita de CROSS JOIN , para facilitar la lectura.
SELECT *
FROM
@Animal
JOIN @AnimalSound
ON 1=1
Unión externa
Izquierda combinación externa
LEFT JOIN devuelve todas las filas de la tabla de la izquierda, que coinciden con las filas de la tabla de la derecha donde se cumplen las condiciones de la cláusula ON . Las filas en las que no se cumple la cláusula ON tienen NULL en todas las columnas de la tabla derecha. La sintaxis de un LEFT JOIN es:
SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Unión externa derecha
RIGHT JOIN devuelve todas las filas de la tabla derecha, que coinciden con las filas de la tabla izquierda donde se cumplen las condiciones de la cláusula ON . Las filas en las que no se cumple la cláusula ON tienen NULL en todas las columnas de la tabla izquierda. La sintaxis de un RIGHT JOIN es:
SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Unión externa completa
FULL JOIN combina LEFT JOIN y RIGHT JOIN . Todas las filas se devuelven desde ambas tablas, independientemente de si se cumplen las condiciones de la cláusula ON . Las filas que no satisfacen la cláusula ON se devuelven con NULL en todas las columnas de la tabla opuesta (es decir, para una fila en la tabla izquierda, todas las columnas en la tabla derecha contendrán NULL y viceversa). La sintaxis de un FULL JOIN es:
SELECT * FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Ejemplos
/* Sample test data. */
DECLARE @Animal table (
AnimalId Int IDENTITY,
Animal Varchar(20)
);
DECLARE @AnimalSound table (
AnimalSoundId Int IDENTITY,
AnimalId Int,
Sound Varchar(20)
);
INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');
INSERT INTO @Animal (Animal) VALUES ('Frog');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpet');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (5, 'Roars');
/* Sample data prepared. */
IZQUIERDA COMBINACIÓN EXTERNA
SELECT *
FROM @Animal As t1
LEFT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Resultados para LEFT JOIN
AnimalId Animal AnimalSoundId AnimalId Sound
----------- -------------------- ------------- ----------- --------------------
1 Dog 1 1 Barks
2 Cat 2 2 Meows
3 Elephant 3 3 Trumpet
4 Frog NULL NULL NULL
JUSTE EXTERIOR DERECHO
SELECT *
FROM @Animal As t1
RIGHT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Resultados de RIGHT JOIN
AnimalId Animal AnimalSoundId AnimalId Sound
----------- -------------------- ------------- ----------- --------------------
1 Dog 1 1 Barks
2 Cat 2 2 Meows
3 Elephant 3 3 Trumpet
NULL NULL 4 5 Roars
ÚNICAMENTE EN EL EXTERIOR
SELECT *
FROM @Animal As t1
FULL JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Resultados para FULL JOIN
AnimalId Animal AnimalSoundId AnimalId Sound
----------- -------------------- ------------- ----------- --------------------
1 Dog 1 1 Barks
2 Cat 2 2 Meows
3 Elephant 3 3 Trumpet
4 Frog NULL NULL NULL
NULL NULL 4 5 Roars
Uso de unirse en una actualización
Las combinaciones también se pueden utilizar en una instrucción UPDATE :
CREATE TABLE Users (
UserId int NOT NULL,
AccountId int NOT NULL,
RealName nvarchar(200) NOT NULL
)
CREATE TABLE Preferences (
UserId int NOT NULL,
SomeSetting bit NOT NULL
)
Actualice la columna SomeSetting del SomeSetting de la tabla de Preferences por un predicado en la tabla de Users siguiente manera:
UPDATE p
SET p.SomeSetting = 1
FROM Users u
JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
p es un alias para las Preferences definidas en la cláusula FROM de la declaración. Solo se actualizarán las filas con un AccountId coincidente de la tabla Users .
Actualización con sentencias de unión externa izquierda
Update t
SET t.Column1=100
FROM Table1 t LEFT JOIN Table12 t2
ON t2.ID=t.ID
Actualizar tablas con función de unión interna y agregación
UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (select field3, sum(field2) as field2Sum
from table2
group by field3) as t2
on t2.field3 = t1.field3
Únete a una subconsulta
La incorporación a una subconsulta se usa a menudo cuando se desean obtener datos agregados (como Count, Avg, Max o Min) de una tabla secundaria / de detalles y mostrarlos junto con los registros de la tabla principal o de encabezado. Por ejemplo, es posible que desee recuperar la fila superior / primera secundaria en función de la fecha o la identificación o tal vez desee un recuento de todas las filas secundarias o un promedio.
Este ejemplo utiliza alias que facilitan la lectura de consultas cuando tiene varias tablas involucradas. En este caso, estamos recuperando todas las filas de las órdenes de compra de la tabla principal y recuperando solo la última fila secundaria (o la más reciente) de la tabla secundaria PurchaseOrderLineItems. Este ejemplo asume que la tabla secundaria usa identificadores numéricos incrementales.
SELECT po.Id, po.PODate, po.VendorName, po.Status, item.ItemNo,
item.Description, item.Cost, item.Price
FROM PurchaseOrders po
LEFT JOIN
(
SELECT l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price, Max(l.id) as Id
FROM PurchaseOrderLineItems l
GROUP BY l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price
) AS item ON item.PurchaseOrderId = po.Id
Auto unirse
Una tabla se puede unir a sí misma en lo que se conoce como una unión automática, combinando registros en la tabla con otros registros en la misma tabla. Las uniones automáticas se utilizan normalmente en consultas donde se define una jerarquía en las columnas de la tabla.
Considere los datos de muestra en una tabla llamada Employees :
| CARNÉ DE IDENTIDAD | Nombre | Boss_ID |
|---|---|---|
| 1 | Mover | 3 |
| 2 | Jim | 1 |
| 3 | Sam | 2 |
El Boss_ID cada empleado se asigna a la ID otro empleado. Para recuperar una lista de empleados con el nombre de su jefe respectivo, la tabla se puede unir sobre sí misma utilizando esta asignación. Tenga en cuenta que unir una tabla de esta manera requiere el uso de un alias ( Bosses en este caso) en la segunda referencia a la tabla para distinguirse de la tabla original.
SELECT Employees.Name,
Bosses.Name AS Boss
FROM Employees
INNER JOIN Employees AS Bosses
ON Employees.Boss_ID = Bosses.ID
La ejecución de esta consulta producirá los siguientes resultados:
| Nombre | Jefe |
|---|---|
| Mover | Sam |
| Jim | Mover |
| Sam | Jim |
Eliminar usando Join
Las combinaciones también se pueden utilizar en una sentencia DELETE . Dado un esquema de la siguiente manera:
CREATE TABLE Users (
UserId int NOT NULL,
AccountId int NOT NULL,
RealName nvarchar(200) NOT NULL
)
CREATE TABLE Preferences (
UserId int NOT NULL,
SomeSetting bit NOT NULL
)
Podemos eliminar filas de la tabla de Preferences , filtrando por un predicado en la tabla de Users siguiente manera:
DELETE p
FROM Users u
INNER JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
Aquí p es un alias para las Preferences definidas en la cláusula FROM de la declaración y solo eliminamos las filas que tienen un AccountId coincidente de la tabla Users .
Accidentalmente convirtiendo una unión externa en una unión interna
Las combinaciones externas devuelven todas las filas de una o ambas tablas, más las filas coincidentes.
Table People
PersonID FirstName
1 Alice
2 Bob
3 Eve
Table Scores
PersonID Subject Score
1 Math 100
2 Math 54
2 Science 98
A la izquierda uniéndose a las mesas:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
Devoluciones:
PersonID FirstName PersonID Subject Score
1 Alice 1 Math 100
2 Bob 2 Math 54
2 Bob 2 Science 98
3 Eve NULL NULL NULL
Si desea devolver a todas las personas, con cualquier puntaje de matemáticas aplicable, un error común es escribir:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
where Subject = 'Math'
Esto eliminaría a Eve de sus resultados, además de eliminar el puntaje de ciencia de Bob, ya que Subject es NULL para ella.
La sintaxis correcta para eliminar registros no matemáticos y conservar a todos los individuos en la tabla People sería:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
and b.Subject = 'Math'