CoolInterview.com - World's Largest Collection of Interview Questions & Answers, FAQs, queries, sample papers, exam papers, dumps, what, why, how, where, when questions
Our Services
Get 9,000 Interview Questions & Answers in an eBook.




Get it now !!
Send your Resume to 6000 Companies

Search Interview Questions

Question: What is outer join?Explain with examples.

Answer: The Left Outer Join returns all the rows from the Left Table with the values that match with the Key from the second table.

The Right Outer Join returns all the rows from the Right Table with the values that match with the key from the first table.



Category SQL Interview Questions & Answers - Exam Mode / Learning Mode
Rating (0.6) By 515 users
Added on 11/22/2013
Views 4948
Rate it!
Question: What is outer join?Explain with examples.
Answer:

The Left Outer Join returns all the rows from the Left Table with the values that match with the Key from the second table.

The Right Outer Join returns all the rows from the Right Table with the values that match with the key from the first table. Source: CoolInterview.com

Answered by: siva yarram | Date: 11/23/2008 | Contact siva yarram Contact siva yarram

You can perform outer join by using following keyword
Left outer join: This include all rows from first table and matching rows from second table.
Right outer join: This include all rows from second table and matching rows from first table.
Full outer join: This include all non matching rows from both the tables.

Source: CoolInterview.com

Answered by: Arvind | Date: 11/25/2008 | Contact Arvind Contact Arvind

outer joins have 3types
left outer join
right outer join
full outer join
LOJ:return all rows from the left table ,if there is no match in the right table.

ROJ:returns all rows from right tables,even if there is no match between the left table.

FOJ:returns rows from the tables any one matches in the tables.

as per my knowledge iam explaing the abt outer joins




Source: CoolInterview.com

Answered by: pushpa | Date: 11/25/2008 | Contact pushpa Contact pushpa

There have tow type of outer join
1)Left outer join
2)Right outer join

1)Left outer join -A left outer join will return all the rows that an inner join returns plus one row for each of the other rows in the first table i.e 'A' table that did not have a match in the second table.

2)Right outer join - A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table(B) that did not have a match in the first table(A). Source: CoolInterview.com

Answered by: Amar deep | Date: 11/25/2008 | Contact Amar deep Contact Amar deep

There have tow type of outer join
1)Left outer join
2)Right outer join

1)Left outer join -A left outer join will return all the rows that an inner join returns plus one row for each of the other rows in the first table i.e 'A' table that did not have a match in the second table.

2)Right outer join - A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table(B) that did not have a match in the first table(A). Source: CoolInterview.com

Answered by: Amar dee | Date: 11/25/2008 | Contact Amar dee Contact Amar dee

Outer Join: Which return all the rows from one table and only those rows from secondary table where joined fields are equal(joined condition is met).
Let's look at some data to explain how outer joins work:

We have a table called suppliers with two fields (supplier_id and name).
It contains the following data:
supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA

We have a second table called orders with three fields (order_id, supplier_id, and order_date).
It contains the following data:
order_id supplier_id order_date
500125 10000 2003/05/12
500126 10001 2003/05/13

If we run the SQL statement below:

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);


Our result set would look like this:
supplier_id supplier_name order_date
10000 IBM 2003/05/12
10001 Hewlett Packard 2003/05/13
10002 Microsoft <null>
10003 NVIDIA <null> Source: CoolInterview.com

Answered by: Mala gupta | Date: 12/1/2008 | Contact Mala gupta Contact Mala gupta

Generally "join" operation perform the cross product of two tables.It selects those rows from the cross product that satisfies the given condition.But with OUTER JOIN DBMS allows us to select those rows which are satisfies and even those rows that don't satisfies the given condition. Source: CoolInterview.com

Answered by: ramakrishna | Date: 12/15/2008 | Contact ramakrishna Contact ramakrishna

Full outer join includes all rows from both tables with empty cells marked as NULL Source: CoolInterview.com

Answered by: Abigaba | Date: 4/17/2010 | Contact Abigaba Contact Abigaba

i don't know Source: CoolInterview.com

Answered by: rubha | Date: 7/31/2010 | Contact rubha Contact rubha


If you have the better answer, then send it to us. We will display your answer after the approval

Rules to Post Answers in CoolInterview.com:-

  • There should not be any Spelling Mistakes.
  • There should not be any Gramatical Errors.
  • Answers must not contain any bad words.
  • Answers should not be the repeat of same answer, already approved.
  • Answer should be complete in itself.

Post your answer here

Inform me about updated answers to this question.
Related Questions
View Answer
What is DML,DDL?
View Answer
How we get second highest salary in
database. Please show me this coding.
View Answer
How to analyze the performance of a query using Explain Plan? Can any one explain me this in detail... if you can provide me with a link where I can get full information on it I would appreciate it...?
View Answer
Explain the concept of joins and list out the different type of joins?
View Answer
What is the difference between varchar & varchar2?
View Answer
what is analyze command how to use ?
View Answer
How you define which session run and how long time ?
View Answer
How to save the output of a query/ stored procedure to a text file using T-SQL?
View Answer
How to get the first day of the week, last day of the week and last day of the month using T-SQL date functions?
View Answer
How to programmatically find out when the SQL Server service started?
View Answer
Write a Query to find unique names of authors who have written books. (Using Pubs database)?
View Answer
Write a Query to pull out the maximum unit price for each in the database. (Using Northwind database)
View Answer
Write a SQL statement to draw the second highest salary in Employee Table (Using Sub query)
Table structure is:
Employe ID
Name
Salary
View Answer
What is a default TCP/IP socket assigned for SQL Server?
View Answer
What is a default TCP/IP socket assigned for SQL Server?
View Answer
What?s the difference between a primary key and a unique key?
View Answer
How to retrieve the image from the database?
View Answer
How to find the second highest salary from employee table? and how to write it?
View Answer
How to transfer some amount from one person's account to another's ?
View Answer
Advantages and disadvantages of attributes.
View Answer

Please Note: We keep on updating better answers to this site. In case you are looking for Jobs, Pls Click Here Vyoms.com - Best Freshers & Experienced Jobs Website.

View All SQL Interview Questions & Answers - Exam Mode / Learning Mode




India News Network
Latest 20 Questions
Payment of time- barred debt is: (a) Valid (b) Void (c) Illegal (d) Voidable
Consideration is defined in the Indian Contract Act,1872 in: (a) Section 2(f) (b) Section 2(e) (c) Section 2(g) (d) Section 2(d)
Which of the following is not an exception to the rule, "No consideration, No contract": (a) Natural love and affection (b) Compensation for involuntary services (c) Completed gift (d) Agency
Consideration must move at the desire of: (a) The promisor (b) The promisee (c) The promisor or any other party (d) Both the promisor and the promisee
An offer which is open for acceptance over a period of time is: (a) Cross Offer (b) Counter Offer (c) Standing Offer (d) Implied Offer
Specific offer can be communicated to__________ (a) All the parties of contract (b) General public in universe (c) Specific person (d) None of the above
_________ amounts to rejection of the original offer. (a) Cross offer (b) Special offer (c) Standing offer (d) Counter offer
A advertises to sell his old car by advertising in a newspaper. This offer is caleed: (a) General Offer (b) Special Offer (c) Continuing Offer (d) None of the above
In case a counter offer is made, the original offer stands: (a) Rejected (b) Accepted automatically (c) Accepted subject to certain modifications and variations (d) None of the above
In case of unenforceable contract having some technical defect, parties (a) Can sue upon it (b) Cannot sue upon it (c) Should consider it to be illegal (d) None of the above
If entire specified goods is perished before entering into contract of sale, the contract is (a) Valid (b) Void (c) Voidable (d) Cancelled
______________ contracts are also caled contracts with executed consideration. (a) Unilateral (b) Completed (c) Bilateral (d) Executory
A offers B to supply books @ Rs 100 each but B accepts the same with condition of 10% discount. This is a case of (a) Counter Offer (b) Cross Offer (c) Specific Offer (d) General Offer
_____________ is a game of chance. (a) Conditional Contract (b) Contingent Contract (c) Wagering Contract (d) Quasi Contract
There is no binding contract in case of _______ as one's offer cannot be constructed as acceptance (a) Cross Offer (b) Standing Offer (c) Counter Offer (d) Special Offer
An offer is made with an intention to have negotiation from other party. This type of offer is: (a) Invitation to offer (b) Valid offer (c) Voidable (d) None of the above
When an offer is made to the world at large, it is ____________ offer. (a) Counter (b) Special (c) General (d) None of the above
Implied contract even if not in writing or express words is perfectly _______________ if all the conditions are satisfied:- (a) Void (b) Voidable (c) Valid (d) Illegal
A specific offer can be accepted by ___________. (a) Any person (b) Any friend to offeror (c) The person to whom it is made (d) Any friend of offeree
An agreement toput a fire on a person's car is a ______: (a) Legal (b) Voidable (c) Valid (d) Illegal
Cache = 0.046875 Seconds