I have two tables called outer_table & inner_table
Logically the JOIN is processed first creating an intermediate result set which is then filtered by the WHERE. Any comparison to NULL results in UNKOWN, thus "WHERE b.status<>'UNKNOWN'" removes the previously created rows with NULLs.
When you read the 2nd Explain you'll notice that the optimizer is smart and removed the Outer join and replaced it by an Inner join.
Depending on the expected result set you probably need to
- move the condition into the ON-clause
- or add "OR b.Status IS NULL"
Do a global search in the manuals for "outer join case study", this chapter is a rewritten from an article in the old Teradata Magazine named "A lesson on Outer Joins. Learned the hard way" :-)
Any comparison to NULL results in UNKOWN. This is how teradata works? Or any logical reason behind this? Btw, i will have to go through manual too.
There's a three-way logic in SQL: TRUE, FALSE, UNKNOWN.
Any comparison (other than IS NULL) involving a NULL results in UNKNOWN, which is quite similar to FALSE in most cases (but NOT UNKNOWN is still UNKNOWN).
This is not how Teradata works, it's how each and every relational DBMS works, it's one of the principles of the relational model.
I have a question on another query.
CREATE TABLE t1
x CHAR(7) ,
y CHAR(7) ,
z CHAR(4) );
CREATE TABLE sr.t2
y CHAR(7) ,
Replace VIEW v_t2
,CASE WHEN z < '20110530' THEN 'D'
ELSE '0' END
insert sr.t1 ('1234567','1234','1234');
sr.t1 AS A
LEFT OUTER JOIN sr.t2 AS B
ON ( A.y=B.y ) where
Can you please let me know whether the query should return any rows.
How will be the order of evaluation here .
first will it evaluate join and then apply where clause or vice versa.
Note that the second table/view does not have any rows.
Can anyone help in this.
Why don't you simply run the query?
The logical order of evaluation is always the same:
JOIN -> WHERE -> GROUP BY -> HAVING -> OLAP -> QUALIFY -> SAMPLE -> ORDER BY
Your select will return no rows.
Intermediate set after JOIN:
WHERE removes the row as NULL<>'D' results in UNKNOWN.
I don't know what you actually wanted to know, as you create a view, which is not used?
Did you mean to access the view instead of the table?
Then the result is still empty, logically the view's result set is created first and then used (just like a Derived Table).
Actually my query returns a row in V2R6 base. In TD12, same query returnsno rows.
Can you please explain the V2R6 scenario.Is it giving incorret results in V2R6.
It's a logical order. As long as the result set is the same the optimizer might apply filters in an earlier step.
Which query, the one you posted or the one based on the view?
IIRC there was an issue with Case in Outer Joins in older releases.
Could you compare the explains if they are different?