Tablename can be deifned in where clause

Database
Enthusiast

Tablename can be deifned in where clause

I just noticed something in Teradata. We can define a table in the WHERE clause. For example the below query run without any error from the p****r: -

select *
from personnel
where personnel1.user_id = 1;

Where personnel and personnel1 are two different tables.

The result seems to be the cartisan product of table personnel and subquery (select * from personnel1 where user_id = 1) and in the output it displays only the columns of the table defined in the from clause (i.e. table personnel here).

Is this a known thing in Teradata. If yes than why it is so. Is there any advantage by this.

Regards,
Sunil
2 REPLIES
Enthusiast

Re: Tablename can be deifned in where clause

This is something we should be very careful while writing queries.
what happened in the query is personnel1 is considered as an alias for personnel and it works with out error.
similarly if we have a query like

select e.name, d.department_name
from employee e, department d
where employee.dept_id = department.dept_id

the above query will work but it will run into issues because of the wrong alias.
It is a known thing but we learned it the hard way

hh
Enthusiast

Re: Tablename can be deifned in where clause

I would not say this is bad but just a timesaving feature. To prevent this from happening again, try changing your defaultdatabase to a database contains nothing. The default database is the where Teradata trying to locate the table and if it could not find one, it will fail the query instead.