"Learning Notes" Oracle comparison operators, logical operators, special operators, determining null values, case sensitivity

Comparison operator:> greater than,< less than

>= greater than or equal, <= less than equals

= equals,!=,<>,^= not equal to

Precedence of logical operator operations: not > and > OR ( parentheses can be used if you want to change the order of precedence )

Special operators:

[NOT] Between ... And ...: Used to test whether it is within range;

[NOT] In (...): Used to test whether in the list;

[NOT] Like: used for pattern matching;

is [NOT] NULL: Used to test for null values;

Any SOME: Compare each value in the list or query, test for a satisfying one, the operators that must be used before include =,! =, >=, <=, >, <, and all: compare each value in the list or query to test whether all values are met, The operators you must use earlier include =,! =, >=, <=, >, <, and so on;

[NOT] EXISTS: Tests whether a subquery returns at least one row;

In usage: You can display the results of a particular collection by using the following form of operation: [NOT] in (...) displays employee information for "salesman", "clerk", and "MANAGER". Enter and execute the query:

SELECT * from EMP WHERE job in (' salesman ', ' Clerk ', ' MANAGER ');

Note: If you add not to the in front, the employee who does not appear in the collection list is displayed. The above usage also applies to numeric collections.

Multiline subquery:

If the subquery returns the result of multiple rows, we call it a multiline subquery. Multiline subqueries use different comparison operation symbols, which are in, any, and all. Query employee information for wages below any one "clerk" wage. Execute the following query:

Select Empno, ename, job,sal from EMP where Sal < No (SELECT sal from emp where job = ' clerk ') and job <> ' clerk ‘;

Note: 4 of the employees in the EMP table are "clerk" and their salaries are 800, 1100, 950, 1300, respectively. There are 2 records that meet wages less than any one "clerk", where the any operator is used to represent less than any one wage in a subquery. Note: Conditional job <> ' clerk ' excludes jobs that are clerk employees themselves.

Inquire about the number, name, and salary of employees whose wages are higher than all "salesman". Execute the following query:

Select Empno, ename, Sal from EMP
where Sal > All (select Sal from emp where job = ' salesman ')

Note: 4 of the employees in the EMP table are "salesman" and their salaries are 1600, 1250, 1250, 1500, respectively. The all operator is used here, which means that it is larger than all the wages in the query.

Query the employee information for department 20 in the same position as the department 10 employees. Execute the following query:

Select Empno, ename, job from Empwhere job in (SELECT job from EMP WHERE deptno=10) and deptno = 20;

Description: In this training, the in operator is used to indicate that the job title is any one of the subquery results. There are 3 jobs in Department 10: MANAGER, President and clerk, and the above query is for employees in department 20 who are in these 3 positions.

Dolez queries If a subquery returns multiple columns, then multiple columns should also appear in the corresponding comparison criteria, which is called a Dolez query. The following is a training instance of a multi-column subquery. Query information about the job title and department's employees who are the same as Scott. Execute the following query:

Select Empno, ename, Sal from Empwhere (job,deptno) = (select Job,deptno from emp WHERE empno=7788);

Note: The above content excerpt WNFY's blog essay-5, article-1, only as a study note.

"Learning Notes" Oracle comparison operators, logical operators, special operators, determining null values, case sensitivity