Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

Why SQL has two types of outer joins? mean 1) left outer join 2)right outer join

user-image
Question added by chaitanya kumar k , ETL Developer , EXL Service
Date Posted: 2017/03/21
Mohammad Imran
by Mohammad Imran , SAP BODS Developer , KAY KAY Technologies

Left Outer Join - Entire rows from left table but only matched rows from right table.

Right Outer Join - Entire rows from right table but only matched rows from left table.

Saleem Salik
by Saleem Salik , Team Lead / Project Manager , ManticSol

As you might know why an outer join used for, when 1 of the joining table may not have referencing records but we have to get records from main table (master table in a master child relation). 

So in a table we join table1 with table2 with having all records from table 1 but only existing referenced records from table2 we can use left outer join (table1 left outer join table2 on table1.column = table2.column)

And if we need to get all records from table2 and referencing records from table1 we can use right outer join in the same syntax. But when we are dealing with 2,3 tables, best practice is to use left outer join if possible.

Malik Mohd Maroof
by Malik Mohd Maroof , assistant consultant , Tata Consultancy Services

If we join two tables like Table1 and Table2

SELECT * FROM Table1 A

LEFT JOIN Table2 B ON A.EmployeeID=B.EmployeeID

Above Query come with output matching both table column and remaining record of left table,that record didn't match with table colum ,left one table is(Table1 ).Similarly with right join

Usman Saleem
by Usman Saleem , Software Engineer , Square63

Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join

More Questions Like This