Hi,
I have been shown a technique of using left joins on two tables and then using one of the table's columns and test if for nulls. I am not really understanding why or how it works. Can anyone explain or direct me to a book or reading material that explains this concept?
For example.
Select t1.col1, t2.col1 from table1 as t1
left join table2 as t2 on t1.idcol=t2.idcol
where t2.col1="something" and t2.idcol is null.

Thanks

A LEFT JOIN operation returns all of the rows in "left" or first table. It will return rows from the "right" or second table if the matching condition in the ON clause is met, or if there are no matching rows in the "right" table there will be one row returned from the "left" table with all NULL values for the columns from the "right" table.

By checking a column from the "right" table that is in the ON clause for a NULL value, the result set will only return rows from the "left" table that have no matching row in the "right" table.

-PatP

Read the cookbook located at:

DB2 SQL Cookbook

Andy

I don't think this query will return anything at all, because t2.col1 will be null in every case when t2.idcol is null, so the predicate t2.col1='something' will always be false.

Thank you all for the helpful hints. I could not get my head wrapped around this concept and your ideas have helped to clarify it.

I applied a bit of "creative reading" skills and tried to understand what ofilha meant as opposed to what they literally posted. If we assume that the t2.col1 was really meant to be another abstract condition in the WHERE clause, then the whole thing makes sense. As you pointed out, if the sample SQL is read literally and t2.colid actually is NULL, then all of the columns in t2 will be NULL.

-PatP

Yes, i am glad you applied your creative reading skills rather than a literal reading of my post. Since the issue is confusing to me my question was likely confused, but the answer Pat gave is what i was looking for. I just needed an explanation of the usage of the is null with this kind of query.

Just so i understant it better.
Now if the example i posted was changed to:

Select t1.col1, t2.col1 from table1 as t1
left join table2 as t2 on t1.idcol=t2.idcol
where t1.col1="something" and t2.idcol is null.

The result set would then would bring all the rows from t1 that do not match t2.idcol but match "something" in t1.col1 ? Am i going in the right direction?

And thanks you all for the help.

Yes, your revised query would return the result test rows that had a value of "something" in t1.col1 where there was no row in t2 which made the ON condition t1.idcol = t2.idcol. It is important to note that if t1.idcol is NULL, it doesn't matter if there are any rows with NULL in t2.colid or not for two different reasons...

Most importantly, NULL is by definition not comparable so it is never "equal to" anything, not even another NULL (which is why you must use the "IS NULL" predicate instead of using = NULL in its place). Even if that weren't the case, the value would still be NULL if the join succeeded, so the code wouldn't be able to tell any difference!

-PatP