Difference between full join and cross join-oracle sql plsql interview question and answer for fresher and experienced

Difference between full join and cross join in sql:-

CROSS JOIN	FULL JOIN
It combines each and every row from one table with another table. Specific condition or relationship is not required to match all the rows of two tables. It will generate possibility all the combination of rows. The number of rows present in the first table are multiplied with the number of rows present in the second table.	It combines rows from the two tables when there is matched on the basis of specified condition and includes unmatched rows from the both tables with NULL values.
It is known as cartesian product or cartesian join.	It is known as full outer join.
ON clause is not present.	ON clause is present. On condition is not satisfied for those rows, it will placed NULL values for unpopulated fields.
It will produce all the combinations of the rows.	It is the combination of left outer join and right outer join. It doesn’t generate all the possible combination like cross join.
Syntax: Select * from table1 CROSS JOIN table2 CROSS JOIN table3;	Syntax: Select * from table1 FULL OUTER JOIN table2 On table1.comm_column_name=table2.comm_column_name;
If two tables A & B are present and B is empty, then AB=A0=0	If two tables A & B are present and A& B are empty, then AB=00=0.Otherwise,it will carry the value of the non-empty table.
If two joined tables contain C & D rows, it will produce C*D.	If two joined tables contain C & D rows, it will produce C+D.
Table D contain 20 rows and Table E contain 30 rows,10 rows are matching on specified columns, then result will be 20*30=600	Table D contain 20 rows and Table E contain 30 rows,10 rows are matching on specified columns, then result will be 10+10+20=40
Result set will be greater than equal to full outer join.	Result set will be less than equal to cross join.

Comments

Difference between union and union all in oracle sql plsql

"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.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. Column must be of the same datatype for these operations of one select statement with another select statements. Columns must be in the same order in this set operators in the multiple select statements. Same number of co...

Difference between decode and case oracle sql plsql interview question and answer

" This is the most accepted question for PLSQL developer role.It has been asked in many interviews like Mphasis and Kyndryl. " INTRODUCTION: Case was came after oracle database version 8.1.6 whereas decode was came before oracle database version 8.1.6 .Decode and case statements in an oracle work or acts like if then elsif conditional statements. if sal='2000000' then 'General Manager' elsif sal='3000000' then 'Senior Manager' elsif sal='1200000' then 'Manager' else 'Employee' end if; create table student_details(Student_RollNo Number,Student_Name Varchar2(20),student_marks Number,student_result varchar2(1)); insert into student_details values (100,'Rahul',90,'P'); insert into student_details values (101,'Rakesh',30,'F'); insert into student_details values (102,'Ram',60,'P'); insert into student_details values (103,'Rohan',10,'F'); insert into student_detai...

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

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 ...

Search This Blog