Distinguish between nested, correlated subquery and join operation-oracle SQL plsql interview question and answer

-

 


DIFFERENCE:

Inner query result needs to be compared with the result of the outer query, nested subquery is the solution.

NESTED

CORRELATED

JOIN OPERATION

It is the type of subqueries used in the SQL.

It is the type of subqueries used in the SQL.

It is to combine data from the multiple tables.

It is running independently.

It depends on the outer query.

It combines the data which is based on the common field.

It is also known as an inner query or nested query.

It depends on the values from the outer query.

It is the statement which is used to combine data or rows from two or more tables which is based on the common field between them.

Inner query is executed first and the result is used by the outer query.

Inner query is executed for each row of the outer query.

It will combine the data from the multiple tables into the single result set.

It is used for filtering the data, performing calculations and joining the datasets indirectly.

It is used when the inner query needs to reference values from the outer query.

It is used when you need to combine the data from more than one table or when the where clause is not enough.

select * from emp where sal>(select avg(sal) from emp);

select e.ename from emp e where e.sal>(select avg(sal) from emp where deptno=e.deptno);

select e. ename,d.dname from emp e join dept d ON e.deptno=d.deptno;

It is used for the complex calculations or filtering.

It is used for the complex calculations or filtering.

It is the best way to combine the data from the multiple tables.

SYNTAX:

Select column from table where column in (select column from table);

SYNTAX:

Select column from table where column= (select column from table where column=value);

SYNTAX:

Select column from table1 join table2 ON table1.column=table2.column;

In terms of performance, it works better than the correlated query but is slower than the join operation.

In terms of performance, it is slower than the nested and join operation. Both inner and outer queries will be going to run.

In terms of performance, it is faster than the nested and correlated.

Execution of an inner query does not depend on the outer query.

Inner query needs the outer query for running. Dependency exists.

Neither inner nor outer query and there is no dependency exists.

It is suitable when the result of the inner query is compared with the outer query.

It is suitable when the inner query is get executed for each and every row of the outer query.

It is suitable when the data from the multiple tables are combined and queries with the where clause are not sufficient.

Inner query run first and only once in the bottom-up method and the result from the inner query is used to run the outer query.

Outer query runs first and for each row of the outer query, the inner query runs.

Cross product strategy is applied when each row of one table is multiplied with another table row.

 

 

 

Comments

Post a Comment

Popular posts from this blog

Difference between union and union all in oracle sql plsql

-
Image
"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea, IBM and Mphasis etc." INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results. In other words, multiple select statements of similar column name in same order and having same data type to produce the unified result. It merges the content of the two structurally compatible organized tables into single table.  In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. There are several alternatives like bulk collect,join,set operators like intersect & minus for combining the data from...
Read more

Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

-
Image
 Oracle PLSQL Data Types : PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types. PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types. Pre-defined data types are categorized into the four types: Composite LOB Reference Scalar Scalar Datatypes: It stores the values without the internal components. It holds the single ...
Read more

Difference between full join and cross join-oracle sql plsql

-
Image
 Difference between full join and cross join in sql:- They behave differently to combine the rows from the two tables. FULL OUTER JOIN: It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows. Syntax: select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id; select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno; When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL. CROSS JOIN: It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows. Syntax: select c.*.d.* from tableC c CROSS JOIN tableD d; select e.ename,d.dname from emp e CROSS JOIN dept d; ENAME ...
Read more