Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

Oracle

INTERSECT in Oracle SQL – PLSQL

by

An INTERSECT in Oracle SQL / PLSQL query is used to join or combine results from 2 or more SELECT statements, an INTERSECT fetches only the common rows returned by SQL SELECT statements.
Also, each SELECT statement must have the same number of column(s)/field(s) with similar data-types for the INTERSECT to work.

Syntax for INTERSECT in Oracle SQL / PLSQL is:
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s)
INTERSECT
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s);

Suppose have a table named ‘employee’ in the database as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Also we have one more table ‘comm’ as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

Let’s take an example to understand INTERSECT, suppose we write our SQL query using INTERSECT as:

SELECT employee_id
FROM employee
INTERSECT
SELECT emp_id
FROM comm;

The result of the above query will be:

Employee_Id
102
103
104
105

Here we can see that the records for employee_id = ‘102’,’103,’104’ and ‘105’ only appear in the result set because they are present in both (‘employee’ and ‘comm’) tables.

UNION ALL in Oracle SQL – PLSQL

by

As the name suggests UNION ALL in Oracle SQL / PLSQL query is used to join or combine results from 2 or more SELECT statements, a UNION ALL does not excludes the duplicate rows returned by SQL SELECT statements.
Also, each SELECT statement must have the same number of column(s)/field(s) with similar data-types for the UNION ALL to work.

Syntax for UNION ALL in Oracle SQL / PLSQL is:

SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s)
UNION ALL
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s);

Suppose have a table named ‘employee’ in the database as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Also we have one more table ‘comm’ as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

Let’s take an example to understand UNION ALL, suppose we write our SQL query using UNION ALL as:

SELECT employee_id
FROM employee
UNION ALL
SELECT emp_id
FROM comm;

The result of the above query will be:

Emp_Id
101
102
102
103
103
104
104
105
105
106
107

Here we can see that the records for employee_id = ‘102’,’103,’104’ and ‘105’ appear twice in the result set because they are present in both (‘employee’ and ‘comm’) tables. Also note that all the ‘employee_id’ from both ‘employee’ and ‘comm’ tables are present in the result set.

UNION in Oracle SQL – PLSQL

by

As the name suggests UNION in Oracle SQL / PLSQL query is used to join or combine results from 2 or more SELECT statements, a UNION excludes the duplicate rows returned by SQL SELECT statements.

Also, each SELECT statement must have the same number of column(s)/field(s) with similar data-types for UNION to work.

Syntax for UNION in Oracle SQL / PLSQL is:

SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s)
UNION
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s);

Suppose have a table named ‘employee’ in the database as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Also we have one more table ‘comm’ as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

Let’s take an example to understand UNION , suppose we write our SQL query using UNION as:

SELECT employee_id
FROM employee
UNION
SELECT emp_id
FROM comm;

The result of the above query will be:

Employee_Id
101
102
103
104
105
106
107

Here we can see that the records for employee_id = ‘102’,’103,’104’ and ‘105’ appear only once in the result set thought they are present in ‘employee’ and ‘comm’ tables. Also note that all the ‘employee_id’ from both ‘employee’ and ‘comm’ tables are present in the result set.

JOINS in Oracle SQL – PLSQL

by

As the name suggests a join in Oracle SQL / PLSQL is used to join or combine 2 or more table rows. A join can be performed whenever we have more than one table in the FROM clause of any SQL SELECT statement.

Different kinds of JOINS are:

  1. Inner Join
  2. Outer Join

1. Inner JOIN:

Inner join is the most common type of join used in SQL queries. An INNER JOIN returns all the rows from multiple tables whenever the join condition is met. It’s also known as “EQUI JOIN”

Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID Employee_Name Salary Department
101 Emp A 10000 Sales
102 Emp B 20000 IT
103 Emp C 28000 IT
104 Emp D 30000 Support
105 Emp E 32000 Sales

Also we have one more table ‘comm’ as shown below:

Emp_ID Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

Let’s join ‘employee’ and ‘comm’ tables using INNER JOIN as:

SELECT employee_id
       ,employee_name
       ,salary
       ,department
       ,c.commission_percent
FROM employee e
     ,comm c
WHERE e.employee_id = c.emp_id;

The result of the above query will be:

Employee_ID Employee_Name Salary Department Commission_Percent
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we can see that the record for employee_id = ‘101’ from ‘employee’ table and records for emp_id = ‘106’ and ‘107’ from ‘comm’ table have not been fetched by the SELECT statement as they are not available in both the tables.

Also note that we have created alias names for tables ‘employee’ and ‘comm’ as ‘e’ and ‘c’ respectively.

2. Outer JOIN:

The outer join returns all the rows from one table and only those rows from the second table where the condition of the join is met.
Let’s take an example for easy understanding:

Assume that we have a new table namely ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department
101 Emp A 10000 Sales
102 Emp B 20000 IT
103 Emp C 28000 IT
104 Emp D 30000 Support
105 Emp E 32000 Sales

Also we have one more table ‘comm’ as shown below:

Emp_ID Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

If we write a query for OUTER JOIN as:

SELECT employee_id
       ,employee_name
       ,salary
       ,department
       ,c.commission_percent
FROM employee e
     ,comm c
WHERE e.employee_id = c.emp_id(+);

Once we have run the above code following will be the result:

Employee_ID Employee_Name Salary Department Commission_Percent
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we can see that the records for ‘employee_id = 101’ from ‘employee’ table are also fetched by query but the same was not available in ‘comm’ table.

Hence the above query returns all the rows from ‘employee’ table but returns only those rows from ‘comm’ table where the condition for join is met i.e. where ‘empoloyee_id’ from ‘employee’ table = ‘emp_id’ from ‘comm’ table.

If we change our query as shown below:

SELECT c.emp_id
       ,employee_name
       ,salary
       ,department
       ,c.commission_percent
FROM employee e
     ,comm c
WHERE e.employee_id(+) = c.emp_id;

We get the following output:

Emp_ID Employee_Name Salary Department Commission_Percent
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 15
107 25

Here we can see that the records for ‘emp_id = 106’ and ‘107’ from ‘comm’ table are also fetched by query but the same was not available in ‘employee’ table.

Hence the above query returns all the rows from ‘comm’ table but returns only those rows from ‘employee’ table where the condition for join is met i.e. where ‘empoloyee_id’ from ‘employee’ table = ‘emp_id’ from ‘comm’ table.

ORDER BY Clause in Oracle SQL – PLSQL

by

The ORDER BY clause in Oracle SQL / PLSQL is used in SELECT statement and allows us to sort the records fetched by SELECT statement.

Syntax for the ORDER BY clause in Oracle SQL / PLSQL is:

SELECT column(s)
FROM table_name
WHERE conditions
ORDER BY column(s) ASC/DESC;

The ORDER BY clause will sort the result in ascending order (if ASC is mentioned) or in descending order (if DESC is mentioned) after the column_name in ORDER BY clause.
If nothing is mentioned after the column_name in ORDER BY clause, by default ascending order (ASC) is taken into consideration.

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 1:

Suppose we want to see records from ‘employee’ table sorted by ‘department’

We can achieve the same as:

SELECT *
FROM employee
ORDER BY department;

The result of the above query will be:

Employee_ID Employee_Name Salary Department Commission
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
105 Emp E 32000 Sales 10
101 Emp A 10000 Sales 10
104 Emp D 30000 Support

Here we can see that the records have been fetched as per ascending order of the ‘department’.

Scenario 2:

Suppose we want to see the list of employees in reverse order of ‘employee_id’
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

We can achieve the same as:

SELECT *
FROM employee
ORDER BY employee_id  DESC;

Once we have run the above code following will be the result:

Employee_ID Employee_Name Salary Department Commission
105 Emp E 32000 Sales 10
104 Emp D 30000 Support
103 Emp C 28000 IT 20
102 Emp B 20000 IT 20
101 Emp A 10000 Sales 10

Here we can see that the records have been fetched as per descending order of ‘employee_id’.