Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Query Select

 

Using the SQL Operators

The SQL operators allow you to limit rows based on pattern matching of strings, lists of values, ranges of values, and null values. The SQL operators are listed in the following table: OperatorDescription LIKEMatches patterns in strings INMatches lists of values BETWEENMatches a range of values IS NULLMatches null values IS NANNew for Oracle10g. Matches the NaN special value, which means "not a number" IS INFINITENew for Oracle10g. Matches infinite BINARY_FLOAT and BINARY_DOUBLE values You can also use the NOT operator to reverse the meaning of LIKE, IN, BETWEEN, and IS NULL: NOT LIKE NOT IN NOT BETWEEN IS NOT NULL IS NOT NAN IS NOT INFINITE SQL> SQL> SELECT COUNT(*) num_owned, a.owner   2      FROM dba_objects a   3      WHERE 10<(SELECT COUNT(*) FROM dba_objects b   4      WHERE a.owner=b.owner)   5      GROUP BY a.owner;  NUM_OWNED OWNER ---------- ------------------------------        473 MDSYS       1143 FLOWS_020100       2769 PUBLIC        575 RNTSOFT        339 CTXSYS         34 HR         12 FLOWS_FILES        449 SYSTEM         46 DBSNMP        668 XDB       6631 SYS 11 rows selected.