Geek Answers Handbook

How do constant values ​​affect the ON clause for Joins?

I recently discovered that the ON LEFT JOIN clause may contain values ​​such as (1 = 1).

This upsets me as it disturbs my perception of how the union functions work.

I came across a more complex version of the following situation:

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

This is completely legal. What does "Manager.ID = -1" do if anything? How can this affect Join?

+7
source share
3 answers

If the face table:

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

If the managers table

 id name -1 Admin 2 Manager One 3 Manager Two

if request:

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

Then the result:

 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 user lines are attached to the -1 Admin element (in the manager table). And if the same identifier exists in the manager table, another connection occurs.

+7
source

If there is no row in the Manager table where id is -1, it does nothing. If there is such a line, then this line will always be connected to each line of the face table. Therefore, for each line of Person you potentially get two ros in the query output, one with the manager. Id = for the identifier of the faces and the other using Manager.ID = -1 row

+5
source

You will also see the AND clause used to further filter records. This is extremely important when working with outer joins, since adding these filtering actions to the where clause will turn the join from the left join to the inner join (unless that's where t.idfield is zero).

Below I will show how this works, and why it is important to correctly position the filtering conditions.

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 (provided that you select a field that will never be NULL) to get records in the first table, but not in the second, for example, like this:

 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
+5
source

More articles:


All Articles