Scribd
Upload
939 views

Co-Related Sub-Query A Correlated Subquery Is Evaluated

A correlated subquery is evaluated once for each row processed by the outer query. The inner query is dependent on and executed once for each row of the outer query. It follows a top-down approach where the main query executes first and then the subquery uses values from the main query. Example correlated subqueries include finding employees whose salaries exceed the department average and returning departments that have employees with a specific department number.

Uploaded by

gateschowdary
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
939 views

Co-Related Sub-Query A Correlated Subquery Is Evaluated

A correlated subquery is evaluated once for each row processed by the outer query. The inner query is dependent on and executed once for each row of the outer query. It follows a top-down approach where the main query executes first and then the subquery uses values from the main query. Example correlated subqueries include finding employees whose salaries exceed the department average and returning departments that have employees with a specific department number.

Uploaded by

gateschowdary
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 3

Co-related Sub-query

A correlated subquery is evaluated once for each row processed by the parent statement.
Sub query is one in which inner query is evaluated only once and from that result
outer query is evaluated.
Correlated Sub query is one in which Inner query is evaluated for multiple times for
getting one row of that outer query.
Co-related query is a query in which sub query depends on execution of main query
In this query main query will executes first. Sub-query will depends on the main query. It
fallows top down approach.
In normal sub queries sub query will executes first. It follows down to top approach.
The main difference between sub query and co-related sub query is that in sub query
child query is executed first n then parent but in co-related sub query main query is
executed first(even though parenthesis are present) and then child query.
Example of co-related sub query
1. Select dname from dept where exists (select deptno from emp where dept.deptno
emp.deptno);
2. Select deptno, ename, sal from emp a
where sal = (select max(sal) from emp
where deptno = a.deptno)
order by deptno
The inner query is executed for each candidate row in the outer statement.

The following statement returns data about employees whose salaries exceed their
department average. The following statement assigns an alias to employees, the table
containing the salary information, and then uses the alias in a correlated subquery:

SELECT department_id, last_name, salary


FROM employees x
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE x.department_id = department_id)
ORDER BY department_id;

Indexes:
An index is a performance-tuning method of allowing faster retrieval of records.
An index creates an entry for each value that appears in the indexed columns.
By default, Oracle creates B-tree indexes.

Syntax:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];
Finding n the salary:

SELECT TOP 1 salary


FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

SELECT SAL,R FROM (SELECT ROWNUM R,SAL FROM (SELECT SAL FROM
EMP ORDER BY SAL DESC)) WHERE R=&N

Rownum:

rownum is a pseudo column. It numbers the records in a result set. The first record that
meets the where criteria in a select statement is given rownum=1, and every subsequent
record meeting that same criteria increases rownum.

JOINS

A join is a query that combines rows from two or more tables, views, or materialized
views. Oracle Database performs a join whenever multiple tables appear in the FROM
clause of the query. The select list of the query can select any columns from any of these
tables. If any two of these tables have a column name in common, then you must qualify
all references to these columns throughout the query with table names to avoid
ambiguity.

TYPES OF JOINS:

1) EQUI JOIN(OR) INNER JOIN


2) NON-EQUI JOIN
3)OUTER JOIN
3.1) LEFT OUTER JOIN
3.2) RIGHT OUTER JOIN
3.3) FULL OUTER JOIN
4)SELF join

The INNER JOIN keyword return rows when there is at least one match in both tables.
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there
are no matches in the right table (table_name2).
The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if
there are no matches in the left table (table_name1).
The FULL JOIN keyword return rows when there is a match in one of the tables.
A SELF JOIN is a type of sql join which is used to join a table to itself, particularly when
the table has a FOREIGN KEY that references its own PRIMARY KEY. It is necessary
to ensure that the join statement defines an alias for both copies of the table to avoid
column ambiguity.
A CROSS JOIN (or Cartesian Product join) will return a result table where each row
from the first table is combined with each row from the second table. The number of
rows in the result table is the product of the number of rows in each table. If the tables
involved are large, this join can take a very long time.

A THETA join that links tables based on a relationship other than equality between two
columns.

You might also like