sql - tablas - ¿Cómo afectan los valores constantes a la cláusula ON de Joins?

tipos de join mysql

Question

Recientemente descubrí que la cláusula ON de una JOIN IZQUIERDA puede contener valores como (1 = 1).

Esto me molesta, ya que rompe mi percepción de cómo funcionan las uniones.

Me he encontrado con una versión más elaborada de la siguiente situación:

SELECT DISTINCT Person.ID, ...
FROM Person LEFT JOIN Manager 
ON (Manager.ID = Person.ID OR Manager.ID = -1))
WHERE (...)

Es perfectamente legal. ¿Qué logra "Manager.ID = -1", en todo caso? ¿Cómo puede esto afectar el Join?

Answer 1

A menos que haya una fila en la tabla Manager donde el id sea igual a -1, no hace nada en absoluto. Si existe tal fila, esa fila siempre se unirá a cada fila en la tabla de personas. Por lo tanto, para cada fila de Persona, potencialmente obtendría dos ros en la salida de la consulta, uno con manager.id = para el id de las personas y otro con la fila Manager.ID = -1

Answer 2

También verá la cláusula AND utilizada para filtrar aún más los registros. Esto es extremadamente importante al tratar con uniones externas como agregar esas acciones de filtrado al punto en que caluse convertirá la unión de una unión izquierda a una unión interna (a menos que sea algo así como donde t.idfield es nulo).

A continuación muestro cómo funciona esto y por qué es importante colocar los filtros en el lugar correcto.

create table #test (test1id int, test varchar (10)) create table # test2 (test2id int, test1id int, test2 varchar (10))

insert into #test (test1id, test)
select 1, ''Judy''
union all
select 2, ''Sam''
union all 
select 3, ''Nathan''

insert into #test2 (test2id, test1id, test2)
select 1,1,''hello''
union all 
select 2,1,''goodbye''
union all 
select 3,2,''hello''

select * from #test t
left join #test2 t2 on t.test1id = t2.test1id
where test2 = ''goodbye''
--result set
--test1id   test    test2id test1id test2
--1 Judy    2   1   goodbye

select * from #test t
left join #test2 t2 on t.test1id = t2.test1id
and test2 = ''goodbye''
--result set 
--test1id   test    test2id test1id test2
--1 Judy    2   1   goodbye
--2 Sam NULL    NULL    NULL
--3 Nathan  NULL    NULL    NULL

Puede utilizar el campo nulo (suponiendo que elija un campo que nunca será nulo) para tomar los registros en la primera tabla, pero no en la segunda como sigue:

select * from #test t
left join #test2 t2 on t.test1id = t2.test1id
where test2id is null
--result set 
--test1id   test    test2id test1id test2
--3 Nathan  NULL    NULL    NULL

Answer 3

Si la tabla de persona es:

id  name

1   Person One
2   Person Two
3   Person Three
4   Person Four
5   Person Five

Si la mesa del gerente es

id  name
-1  Admin
2   Manager One
3   Manager Two

si la consulta es:

SELECT DISTINCT *
FROM Person LEFT JOIN Manager 
ON (Manager.id = Person.id OR Manager.id = -1)

Entonces el resultado es:

Person One  -1  Admin
Person Two  -1  Admin
Person Two  2   Manager One
Person Three    -1  Admin
Person Three    3   Manager Two
Person Four -1  Admin
Person Five -1  Admin

Aquí todas las filas de personas se unen con el administrador -1 (en la tabla de administradores) Y si existe la misma identificación en la tabla de administradores, se produce una unión más.