Character Data types in Oracle

Oracle character data types / Character data types used in Oracle / Character data types supported by Oracle / Various character type variable declaration using Oracle

Character Datatypes

Oracle Character Datatypes are used to store alphanumeric character data. The following datatypes are the Character datatypes supported in Oracle.

CHAR Datatype                                   -CHAR[(size[BYTE | CHAR])]

VARCHAR2 and VARCHAR Datatypes      - VARCHAR2(size [BYTE | CHAR])

NCHAR and NVARCHAR2 Datatypes       - NVARCHAR2(size)

LOB Character Datatypes                     – CLOB and NCLOB

LONG Datatype                                   – LONG

Data Types in Oracle

Oracle Datatypes / Supported datatypes in Oracle / Datatypes in Oracle (Up to Oracle 12c) / Various datatypes that are used in Oracle variable declaration / List of datatypes supported by Oracle up to Oracle 12c

Complete Guide to Oracle Datatypes (Up to Oracle 12c)

Oracle uses and supports the following built-in Datatype categories in creating tables, declaring variables in PL/SQL etc.

Character Datatypes           – To store and manipulate character based values / strings

Numeric Datatypes             – To store and manipulate numbers

Date Datatypes                  – To store and manipulate date and time

LOB Datatypes                   – To store and manipulate large objects like text based files, images, videos etc.

RAW Datatypes                  - To store graphics, sound, documents etc.

ROWID Datatypes               - To store the addresses of every row in a database.

INTERSECT Set Operator

SQL Set Operator INTERSECT and INTERSECT ALL / Supported Set Operators in various DBMSs / INTERSECT and INTERSECT ALL Set Operators with Examples

Let us use the following tables for explaining the concept of the INTERSECT set operator;

Regno	Name	Phone
10BS0123	Madhavan	9965235412
10BC0234	Jerry	8569326541
11BM0023	Malar	9658236542
11BC0003	Kumar	9653268954

Table 1 - UG_Student

Regno	Name	Phone
10MS0434	Noel	9856452314
10MS0012	Kumar	9653268954
11MC0070	Ramkumar	9965235412

Table 2 - PG_Student

INTERSECT operator is used to join two or more tables vertically, and displays only the records which are common for all the tables. That is, the INTERSECT operator filters the records which can be commonly found in all the tables according to the given expressions. For example, consider the query given below;

(SELECT Name, Phone FROM UG_Student) INTERSECT (SELECT Name, Phone FROM PG_Student);

----- Query 1

The result of this query will be;

Name	Phone
Kumar	9653268954

Table 3 - Result of  "(SELECT Name, Phone FROM UG_Student) INTERSECT (SELECT Name, Phone FROM PG_Student);"

The result shows the only common record according to the given expressions 1 (SELECT Name, Phone FROM UG_Student) and 2 (SELECT Name, Phone FROM PG_Student) of Query 1.

The INTERSECT operator can be used in two ways;

1. INTERSECT [Distinct] – Here Distinct keyword need not be specified.

2. INTERSECT ALL – Removes the duplicate tuples (records) from the result set. For intersect all, we will get the result as Table 4.

Name	Phone
Kumar	9653268954
Kumar	9653268954

Table 4 - Result of  "(SELECT Name, Phone FROM UG_Student) INTERSECT ALL (SELECT Name, Phone FROM PG_Student);"

Support for SQL Set Operator INTERSECT in Various DBMSs:

INTERSECT operator in MySQL

MySQL does not support INTERSECT. It can be achieved through other ways using the keywords IN or EXISTS. Query 1 is rewritten using the keyword IN as follows to achieve intersection in MySQL.

SELECT Name, Phone FROM UG_Student WHERE (Name, Phone) IN (SELECT Name, Phone FROM PG_Student);

 

INTERSECT operator in Oracle

Oracle supports INTERSECT operator as discussed above. The above example (Query 1) can be treated as example for Oracle INTERSECT operator.

INTERSECT operator in DB2

DB2 supports INTERSECT operator. Above examples can be treated as examples for DB2 too.

Related Articles

SQL Set Operators
UNION Operator
EXCEPT/MINUS Operator