Is There Any Difference Between "!=" and "<>" in Oracle SQL

Is there any difference between != and in Oracle Sql?

No there is no difference at all in functionality.

(The same is true for all other DBMS - most of them support both styles):

Here is the current SQL reference: https://docs.oracle.com/database/121/SQLRF/conditions002.htm#CJAGAABC

The SQL standard only defines a single operator for "not equals" and that is <>

Oracle SQL - Difference between AND & OR operators

AND operator mean both (department_id = 10) is TRUE also (Department_ID = 20 ) is TRUE, result is TRUE

OR operator mean either (department_id = 10) is TRUE or (Department_ID = 20 ) is TRUE, result is TRUE

SQL Difference between using = and IN

Use IN if you're doing multiple or's e.g.

Where StudentID = 7 or StudentID = 6 or StudentID = 5

Would be

Where StudentID IN (5,6,7)

Otherwise just use =

what's the difference between ::= and := in oracle?

I am not sure about ::= as I have not seen that in Oracle but the wiki says about :=

In computer programming languages, the equals sign typically denotes
either a boolean operator to test equality of values (e.g. as in
Pascal or Eiffel), which is consistent with the symbol's usage in
mathematics, or an assignment operator (e.g. as in C-like languages).
Languages making the former choice often use a colon-equals (:=) or ≔
to denote their assignment operator. Languages making the latter
choice often use a double equals sign (==) to denote their boolean
equality operator.

Also check here:

The assignment operator in PL/SQL is a colon plus an equal sign
(:=). PL/SQL string literals are delimited by single quotes

Difference between USING and ON in Oracle SQL

The difference for me is that you can paint yourself into a corner with the USING clause:

CREATE TABLE roster (mgrid INTEGER, empid INTEGER);
CREATE TABLE emp (empid INTEGER, NAME VARCHAR2(20));

INSERT INTO roster VALUES (1,10);
INSERT INTO roster VALUES (1,11);
INSERT INTO roster VALUES (1,12);
INSERT INTO roster VALUES (2,20);
INSERT INTO roster VALUES (2,21);

INSERT INTO emp VALUES (10, 'John');
INSERT INTO emp VALUES (11, 'Steve');
INSERT INTO emp VALUES (12, 'Mary');
INSERT INTO emp VALUES (20, 'Ann');
INSERT INTO emp VALUES (21, 'George');
INSERT INTO emp VALUES (1, 'Pete');
INSERT INTO emp VALUES (2, 'Sally');

SELECT r.mgrid, e2.name, e1.empid, e1.name
  FROM roster r JOIN emp e1 USING(empid)
                JOIN emp e2 ON r.mgrid = e2.empid; 

In the above select, you get an ora-25154, "column part of USING clause cannot have a qualifier".

If you remove the e1.empid qualifier, as in:

SELECT r.mgrid, e2.name, empid, e1.name
  FROM roster r JOIN emp e1 USING(empid)
                JOIN emp e2 ON r.mgrid = e2.empid; 

You get an ORA-00918 error, "column ambiguously defined".

You have to use:

SELECT r.mgrid, e2.name, e1.empid, e1.name
  FROM roster r JOIN emp e1 ON r.empid = e1.empid
                JOIN emp e2 ON r.mgrid = e2.empid; 

The example is contrived, but when I was first exploring the join syntax I ran into this exact problem in a real situation. I have avoided the USING clause ever since. There is no advantage with the USING clause other than a few keystrokes.

What are the differences between Oracle SQL clause != ANY(...) and not IN (...)

I think you are misusing NOT(!) operator.

How it works:

"column_name = ANY (...)": The value must match one or more values in the
list to evaluate to TRUE.

"column_name != ANY (...)": The value must not
match one or more values in the list to evaluate to TRUE.

In your case, your column value lets say 'A' is matching with =ANY('A','B','C') but at the same time when you use !=ANY('A','B','C') then also it will evaluate to TRUE as A!=B or A!=C.

So you must use column_name !=ALL('A','B','C') or use NOT column_name =ANY('A','B','C') as following:

Either use

select * from FOO_TABLE ft where NOT ft.foo_field = any ('A','B','C'); 
 -- see the keyword NOT before column name

or

select * from FOO_TABLE ft where ft.foo_field != ALL ('A','B','C');

Cheers!!

Oracle difference between !=() and not in

Whether or not the column is indexed, I would expect both queries to perform a full scan on the table, i.e the query plan is essentially the same. The small timing difference you noted is probably insignificant - run the same query more than once and you will get different timings.

Having said that I would use <> because it is more natural.

What is exactly difference between a 'statement' and a 'function' in oracle sql?

In SQL, there is no such thing as a "CASE statement". Your example is a CASE expression. (CASE statements do exist in PL/SQL.)

The documentation states "An expression is a combination of one or more values, operators, and SQL functions that evaluates to a value." So a SQL function is not different from an expression, it is a specific type of expression.

Note that DECODE and CASE behave differently when comparing NULL values: DECODE considers two NULLs to be "the same", which is an exception to the rule that comparing a NULL to anything has an "unknown" result.

with data(a, b) as (
  select 1,1 from dual union all
  select 1,null from dual union all
  select null,1 from dual union all
  select null, null from dual
)
select a, b,
  decode(a,b,'same','different') decode_result,
  case when a = b then 'same' else 'different' end case_result
from data;

A      B      DECODE_RESULT CASE_RESULT
------ ------ ------------- -----------
1      1      same          same
1      (null) different     different
(null) 1      different     different
(null) (null) same          different

is there a difference between is and as in a PL/SQL procedure

Both keywords can be used equivalently. See the documentation:

---

Related Topics