Any one can help me out to find the differences between the SQL Command Operators
=, in,any
Can u tell me some web sites to find these type of help for SQL Commands(Advanced)
Thankx
M.Jeyaseelan
Printable View
Any one can help me out to find the differences between the SQL Command Operators
=, in,any
Can u tell me some web sites to find these type of help for SQL Commands(Advanced)
Thankx
M.Jeyaseelan
=
Simple equality operator, returns TRUE if the left and the right side of the expression are identical. I'm sure everyone knows its meaning, but here is an example anyway:
SELECT * FROM emp WHERE ename = 'ADAMS';
IN
Returns TRUE if the left side is identical to at least one member of the list on the right side.
SELECT * FROM emp WHERE ename IN ('ADAMS','KING');
ANY
It is used in conjunction with (non)equality operator ("=ANY", ">=ANY", "!=ANY"). Reterns TRUE if left side of the expression meets the condition in at least one element from the right side list of vaues.
Examples:
SELECT * FROM emp WHERE ename =ANY ('ADAMS','KING');
is the same as
SELECT * FROM emp WHERE ename IN ('ADAMS','KING');
SELECT * FROM emp WHERE sal <= ANY (SELECT sal FROM emp WHERE deptno=20);
is the same as
SELECT * FROM emp e WHERE EXISTS (SELECT NULL FROM emp WHERE deptno=20 AND e.sal <= sal);
and (functionaly, not how it is executed) as
SELECT * FROM emp WHERE sal <= (SELECT MAX(sal) FROM emp WHERE deptno=20);
Among the less frequently used operators are also SOME (identical meaning as ANY) and ALL (the opposite of ANY/SOME). ANY, SOME and ALL are rearly used, although they are part of SQL standard. We are much more acquainted to use the combination of other frequently used operators (like IN and EXISTS) instead of them.
All those operators are described in Oracle SQL Reference manual, available also on the web, for example on the technet.oracle.com
HTH,
Thank u very much jmodic