Как постоянные значения влияют на предложение ON в Joins?

Question

Недавно я обнаружил, что предложение ON в LEFT JOIN может содержать такие значения, как (1 = 1).

Это огорчает меня, так как нарушает мое представление о том, как функционирует соединение.

Я столкнулся с более сложной версией следующей ситуации:

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

Это совершенно законно. Что делает "Manager.ID = -1", если что? Как это может повлиять на объединение?

Answer 1

Если личный стол:

id  name

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

Если таблица менеджера

id  name
-1  Admin
2   Manager One
3   Manager Two

если запрос:

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

Тогда результат:

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

Здесь все строки с людьми объединяются с -1 Admin (в таблице менеджера) И, если такой же идентификатор существует в таблице менеджера, происходит еще одно соединение.

Answer 2

Если в таблице Manager нет строки, в которой идентификатор равен -1, он вообще ничего не делает. Если есть такая строка, эта строка всегда будет присоединена к каждой строке в таблице person. Таким образом, для каждой строки Person вы могли бы получить два ro в выводе запроса, один с manager.id = к идентификатору людей, а другой с строкой Manager.ID = -1

Answer 3

Также вы увидите предложение AND, используемое для дальнейшей фильтрации записей. Это чрезвычайно важно при работе с внешними объединениями, так как добавление этих фильтрующих действий в оператор caluse превратит соединение из левого соединения во внутреннее соединение (если только это не то, где t.idfield равен нулю).

Ниже я покажу, как это работает, и почему важно правильно расположить классы фильтрации.

создать таблицу #test ( test1id int, test varchar (10)) создать таблицу #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

Вы можете использовать, где какое-то поле имеет значение NULL (при условии, что вы выбрали поле, которое никогда не будет иметь значение NULL), чтобы получить записи в первой таблице, но не во второй, например, так:

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