Deskripsi
Untuk mengetahui selak beluk query pada database oracle. Content Summarize from Oracle.com
* Level Priority (semakin kecil semakin didahulukan)
| level 1 | Deskripsi Unary + - arithmetic operators, PRIOR operator |
| 2 | * / arithmetic operators |
| 3 | Binary + - arithmetic operators, || character operators |
| 4 | All comparison operators |
| 5 | NOT logical operator |
| 6 | AND logical operator |
| 7 | OR logical operator |
** Arithmetic Operators
| Operator | Description | Example |
|---|---|---|
| + (unary) | Makes operand positive | SELECT +3 FROM DUAL; |
| - (unary) | Negates operand | SELECT -4 FROM DUAL; |
| / | Division (numbers and dates) | SELECT SAL / 10 FROM EMP; |
| * | Multiplication | SELECT SAL * 5 FROM EMP; |
| + | Addition (numbers and dates) | SELECT SAL + 200 FROM EMP; |
| - | Subtraction (numbers and dates) | SELECT SAL - 100 FROM EMP; |
** Character Operator
| Operator | Description | Example |
|---|---|---|
| || | Concatenates character strings | SELECT 'Your name: ' || ENAME FROM EMP; |
** Comparison Operator
Comparison operators are used in conditions that compare one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN.
| Operator | Description | Example |
|---|---|---|
| = | Equality test. | SELECT ENAME "Employee" FROM EMP WHERE SAL = 1500; |
| !=, ^=, <> | Inequality test. | SELECT ENAME FROM EMP WHERE SAL ^= 5000; |
| > | Greater than test. | SELECT ENAME "Employee", JOB "Title" FROM EMP WHERE SAL > 3000; |
| < | Less than test. | SELECT * FROM PRICE WHERE MINPRICE < 30; |
| >= | Greater than or equal to test. | SELECT * FROM PRICE WHERE MINPRICE >= 20; |
| <= | Less than or equal to test. | SELECT ENAME FROM EMP WHERE SAL <= 1500; |
| IN | "Equivalent to any member of" test. Equivalent to "= ANY". | SELECT * FROM EMP WHERE ENAME IN ('SMITH', 'WARD'); |
| ANY/ SOME | Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to FALSE if the query returns no rows. | SELECT * FROM DEPT WHERE LOC = SOME ('NEW YORK','DALLAS'); |
| NOT IN | Equivalent to "!= ANY". Evaluates to FALSE if any member of the set is NULL. | SELECT * FROM DEPT WHERE LOC NOT IN ('NEW YORK', 'DALLAS'); |
| ALL | Compares a value with every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to TRUE if the query returns no rows. | SELECT * FROM emp WHERE sal >= ALL (1400, 3000); |
| [NOT] BETWEEN x and y | [Not] greater than or equal to x and less than or equal to y. | SELECT ENAME, JOB FROM EMP WHERE SAL BETWEEN 3000 AND 5000; |
| EXISTS | TRUE if a sub-query returns at least one row. | SELECT * FROM EMP WHERE EXISTS (SELECT ENAME FROM EMP WHERE MGR IS NULL); |
| x [NOT] LIKE y[ESCAPE z] | TRUE if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. The character "_" matches any single character. Any character following ESCAPE is interpretted litteraly, useful when y contains a percent (%) or underscore (_). | SELECT * FROM EMP WHERE ENAME LIKE '%E%'; |
| IS [NOT] NULL | Tests for nulls. This is the only operator that should be used to test for nulls. | SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL > 1500; |
** Logical Operator
| Operator | Description | Example |
|---|---|---|
| NOT | Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN. | SELECT * FROM EMP WHERE NOT (job IS NULL)SELECT * FROM EMP WHERE NOT (sal BETWEEN 1000 AND 2000) |
| AND | Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise returns UNKNOWN. | SELECT * FROM EMP WHERE job='CLERK' AND deptno=10 |
| OR | Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise, returns UNKNOWN. | SELECT * FROM emp WHERE job='CLERK' OR deptno=10 |
** Set Operator
| Operator | Description | Example |
|---|---|---|
| UNION | Returns all distinct rows selected by either query. | SELECT * FROM(SELECT ENAME FROM EMP WHERE JOB = 'CLERK'UNIONSELECT ENAME FROM EMP WHERE JOB = 'ANALYST'); |
| UNION ALL | Returns all rows selected by either query, including all duplicates. | SELECT * FROM(SELECT SAL FROM EMP WHERE JOB = 'CLERK'UNIONSELECT SAL FROM EMP WHERE JOB = 'ANALYST'); |
| INTERSECT and INTERSECT ALL | Returns all distinct rows selected by both queries. | SELECT * FROM orders_list1INTERSECTSELECT * FROM orders_list2 |
| MINUS | Returns all distinct rows selected by the first query but not the second. | SELECT * FROM (SELECT SAL FROM EMP WHERE JOB = 'PRESIDENT'MINUSSELECT SAL FROM EMP WHERE JOB = 'MANAGER'); |
** Other Operator
| Operator | Description | Example |
|---|---|---|
| (+) | Indicates that the preceding column is the outer join column in a join. | SELECT ENAME, DNAME FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO (+); |
| PRIOR | Evaluates the following expression for the parent row of the current row in a hierarchical, or tree-structured query. In such a query, you must use this operator in the CONNECT BY clause to define the relationship between the parent and child rows. | SELECT EMPNO, ENAME, MGR FROM EMP CONNECT BY PRIOR EMPNO = MGR; |