Tutorials on Left, Right and Full Outer Join on MSC Notes

Asked By 40 points N/A Posted on -
qa-featured

Hi guys,

John Sena here. I am preparing my MCS notes and during studies I am facing few problems and I want to share them with all of you.

Can anyone help me in this case. What is outer join?

Explain Left outer join, Right outer join and Full outer join.

Dear you answer should be relevant to my question.

So please send me correct answer.

Thanks in advance.

I am waiting for a good answer.

SHARE
Best Answer by mabz143
Answered By 40 points N/A #128369

Tutorials on Left, Right and Full Outer Join on MSC Notes

qa-featured

 I also faced that type of problems but I studied different types of books and get their answers.

Ok now the answer of your question is that OUTER JOIN: In An outer join, rows are returned even when there are no matches through the JOIN criteria on the second table.

LEFT OUTER JOIN: A left outer join or a left join returns results from the table mentioned on the left of the join irrespective of whether it finds matches or not. If the ON clause matches 0 records from a table on the right, it will still return a row in the result – But with NULL in each column. RIGHT OUTER JOIN: A right outer join or a right join returns results from the table mentioned on the right of the join irrespective of whether it finds matches or not.

If the ON clause matches 0 records from the table on the left, it will still return a row in the result but with NULL in each column.

FULL OUTER JOIN: A full outer join will combine results of both left and right outer join.

Hence the records from both tables will be displayed with a NULL for missing matches from either of the tables.

Answered By 0 points N/A #128370

Tutorials on Left, Right and Full Outer Join on MSC Notes

qa-featured

Only when there is a minimum of one row from both tables that fulfill's the join condition, inner joins return rows, and eliminate the ones that do not match with a row from the other table.

On the other hand, outer joins return all rows from at least one of the views or tables mentioned in the FROM clause, as long as those rows meet any HAVING or WHERE search conditions.
 
The rows that are regained from the left table cited with a left outer join and the rows from the right table cited in a right outer join.
 
All rows from both tables are retrieved in a full outer join. 
Best Answer
Best Answer
Answered By 0 points N/A #128372

Tutorials on Left, Right and Full Outer Join on MSC Notes

qa-featured

Hope this can help.

SQL Outer Join

This lesson covers both the left outer join, the right outer join, and the full outer join, and explains the differences between them. There are some occasions where you would need to use a left outer join or a right outer join, and others where you would need a full outer join. The join type you use will depend on the situation and what data you need to return.

Left Outer Join

Use this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS IND

LEFT JOIN Publisher AS Pub

ON IND. Individualized = Pub. Individually

Right Outer Join

Use this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.

Example SQL statement

SELECT * FROM Individual AS IND
RIGHT JOIN Publisher AS Pub
ON IND. IndividualId = Pub. Individually

Full Outer Join

Use this when you want to all rows, even if there's no matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS IND
FULL JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId 
Answered By 0 points N/A #128373

Tutorials on Left, Right and Full Outer Join on MSC Notes

qa-featured

When in SQL we are joining two tables and if one of these tables, one of the tables has such rows that do not have related rows in the other joined table and we still wanted to display all records we use outer joins.

Let for example we have two table students and courses, On the student table there are some student who have not yet taken any course and we still wanted to display all the students, even if the student has not course, we will use an outer join.

Left outer join Return all rows from the left table, even if there are no matches in the right table.

Left outer join Return all rows from the right table, even if there are no matches in the left table.

Full outer on display all the rows from both the tables, for example if there are some students having no courses and some courses that are not associated with any of the students , then all the students and all the courses will be displayed.

Answered By 0 points N/A #128375

Tutorials on Left, Right and Full Outer Join on MSC Notes

qa-featured

Outer join: An outer join is a join that does not require the two joined tables to have a matching record. There are three types of outer join: 

1. Left outer join: The Left outer join returns all rows from the left table, even if there are no matches in the right table. 
 
2. Right outer join: The Right outer gives back all the rows from the right table, even if there are no matches in the left table. 
 
3. Full outer joins: A full outer join combines the effect of applying both left and right outer joins.
 
The FULL JOIN keyword return rows when there is a match in one of the tables.
 
Answered By 0 points N/A #128374

Tutorials on Left, Right and Full Outer Join on MSC Notes

qa-featured

Hi,

For everybody, this is my solution.

If there any wrong then inform me.

Answered By 0 points N/A #128376

Tutorials on Left, Right and Full Outer Join on MSC Notes

qa-featured

Hi,

Simply join is used to join content which is present in the two tables. But there should be one same column in both the tables.

Full outer joins:

It joins the both the tables,and it does not look for any matching.

Left outer join:

It gives only matching rows in both the tables and not matching rows in the left table means

( The table which is used first in the joint statement.)

Right outer join:

Reverse of the left.

Thanks

Anilkumar.

Answered By 40 points N/A #128377

Tutorials on Left, Right and Full Outer Join on MSC Notes

qa-featured

Thank you all for this awesome help.

I clearly understand this now.

You guys rock.

Login/Register to Answer

Related Questions