SQL Operators

Precedence defines the order that Oracle uses when evaluating different operators in the same expression. Every operator has a predefined precedence. Oracle evaluates the operators with the highest precedence first before evaluating the operators of lower precedence. Operators of equal precedence are evaluated from left to night.

-SQL*Plus continuation command.
&Parameter prefix
&,&&Substitution prefix
:Prefix for host variable
,Variable separator
()Surrounds subqueries
'Surrounds a literal
"Surrounds a table or column alias
"Surrounds literal text
@Precedes a database name
()Overrides normal operator precedence
+,-Positive and negative signs
*,/Multiplication and division
+,-Addition and subtraction
| |Character value concatenation
NOTReverses the result of an expression
ANDTrue if both conditions are true
ORTrue if either condition is true
UNIONReturns all data from both queries
INTERSECTReturns only the rows that match both quires
MINUSReturns only the row's that do not match both queries

Other Operators

Oracle supports two more operators that do not fall into any of the previous groups. They are the prior and the outer join operators.

Prior evaluates an expression for its hierarchy in a connect by clause. This operator when used in a select statement forces the results of the query to be presented in a hierarchical format with the root being displayed first and its child displayed second.

How to Use Conditions ?

Conditions are used in evaluations. They are used as expressions whose value equates to either true or false. Conditions always return a value of True, False, or Unknown. An example of a very simple condition is
deptno=deptno
Conditions can have one of several forms, depending on their usage. The following is a series of examples that illustrate the various forms of a condition statement.

Form 1 - A comparison of expressions
amt 1 + amt2> 1500; Form 2 - A condition that combines other operators in a single conditional statement.
(deptno = 15) OR (city = 'Boston') Form3 - A conditional test for inclusion in a range
pts_scored between 15 and 20; Form4 - A test for nulls
equip_desc is null; Form5 - A test for membership in a list of subquery
select "from master_parts
where not (part_code class = '35A');

Form6
A test for existence of rows in a subquery
select *from dept
where exists
(select dept no, dept_name from emp
where dept. dept no = emp_dept no)
Usage of Conditions

Conditions can be used in numerous situations. You can use a condition in the WHERE statements :

DELETE
UPDATE
SELECT

In the SELECT command, you can use a condition with these clauses:

WHERE
HAVING
START WITH

Conditions can be used in simplistic statements such as part_1 part 2. They can also be used in more complex situations where logical operators combine several conditions into a single condition. For example, the following statement uses the OR logical operator to combine two different conditions into one conditional statement.

(curr_salary >= max Salary) or (hire_date <'01 JAN 1975');

Domain Name Search