SELECT {*|<Col1>}, <Col2>, <Col3>…
FROM <table name>
WHERE <column name> [NOT] IN (<list of values>)
For example, to select employees from a list of department numbers, use the following SQL.
SELECT First_Name, Last_Name
FROM employee
WHERE dept_no IN (210,652,555,871)
Remember that a result set would be returned even if there is only on matching dept_no found. As mentioned before if you have a NULL operator in the list then there would be no rows returned. To find out rows not matching the list of values, prefix the NOT operator before IN as follows;
SELECT First_Name, Last_Name
FROM employee
WHERE dept_no NOT IN (210,652,555,871)
If you want to use another alternative to this, you can go for ANY, but this has to be used with a comparison operator as follows.
SELECT {*|<Col1>}, <Col2>, <Col3>…
FROM <table name>
WHERE <column name> = ANY (<list of values>)
To negate you have to swap the word with ALL and prefix with the NOT operator before the equal sign, i.e. NOT = ALL <list of values>
Usage of NULL and NOT Operator
The word NULL is a reserved word in any SQL. As you know NULL represents nothing, more accurately missing data or empty cell values. A comparison with NULL always returns NULL. To find out NULL values we can either have a condition with a logical operator with two NOT operators or we can use COALESE function.
Usage of NOT operator
It is a wondering truth that sometimes in life, we do NOT want something. And to satisfy this need in SQL, there is a NOT operator provided. Just prefix this before any comparison operator and you will get what you do NOT want. Here is the syntax.
SELECT {*|<Col1>}, <Col2>, <Col3>…
FROM <table name>
WHERE <column name> NOT <operator> <another column name or constant value>
Our Random Articles
- Somdev Devvarman ousts Xavier Malisse 6-1, 3-6,7-6(5)
- How To Choose A Good Online Traffic School
- New Year Wishes 2010
- New Year Wishes
- How to use EXTRACT function with date and time columns
More Links
4 Comments
Material that you are providing is very easy to learn. But it is much better than this , if you ll give the full material of this at ONE LINK.
Thank you
Suresh.
That is right Suresh. This is the reason I have given all the links in one page, please visit this
http://readvitamin.com/teradata/
Hope this helps.
Hi. Can anyone tell me how to extract time from timestamp?
For Example
timestamp=’2008-03-28 14:11:04′
i want time ’14:11:04′ from timestamp through query.
Thanks in advance.
Hi Vaideesh,
If you need only time, you can probably try the following;
SELECT time;
To answer your question you should do something like this.
SELECT CAST(VaideeshTime as time(0)) from timestamp;
VaideeshTime
——–
14:11:04
Hope that helps.