How do constant values effect the ON clause of Joins?

Question

I've recently discovered that the ON clause of a LEFT JOIN may contain values such as (1 = 1).

This is upsetting to me, as it breaks my perception of how joins function.

I've encountered a more elaborate version of the following situation:

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

It's perfectly legal. What does "Manager.ID = -1" accomplish, if anything? How can this effect the Join?

Sizar

I came to this page with the same question. You mention that the ON expression using a constant is "perfectly legal." How did you determine that? Are you saying it's "legal" (sanctioned by a specification) or that it just happens to work in a given implementation? – Jorry

Answer 1

If person table is:

id  name

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

If manager table is

id  name
-1  Admin
2   Manager One
3   Manager Two

if the query is:

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

Then the result is:

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

Here all person rows joins with the -1 Admin (on manager table) AND if the same id exist in manager table one more join occurs.

Intervene

Answer 2

Unless there a row in the Manager table where the id is equal to -1, it does nothing at all. If there is such a row, then that row will always be joined to every row in the person table. So for each Person row you would potentially get two ros in query output, one with manager.id = to the persons' id, and another with the Manager.ID = -1 row

Suburbicarian

Simpler than I had imagined. Thanks. – Sizar

Answer 3

Also you will see the AND clause used to further filter the records. This is extremely important in dealing with outer joins as adding those filtering actions to the where clause will turn the join from a left join to an inner join (unless it is something like where t.idfield is null).

Below I show how this works and why it important to put the filtering clauses in the right place.

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

You can use where some field is null (assuming you pick a field that will never be null) to grab the records in the first table but not the second like so:

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

Emptyhanded