What is Inner join and Left Outer Join
Upasana | May 24, 2019 | 2 min read | 196 views
What is Inner Join?
INNER JOIN is the most common and the default join operation. This join creates a resultset by combining the column values of two tables (L and R) based upon the predicate. Each row of L (left table) is compared with each row of R (right table) to find all pairs of rows that satisfy the join predicates. When the join-predicate is satisfied, column values for each matched pair of rows of L and R are combined into a result row. Example query is shown below.
SELECT * FROM employee E INNER JOIN department D ON E.DepartmentID = D.DepartmentID;
SELECT * FROM employee E, department D WHERE E.DepartmentID = D.DepartmentID;
SELECT c.name, c.contact, c.email FROM Customer c INNER JOIN Orders o ON c.id = o.c_id;
What is LEFT OUTER JOIN?
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists.
LEFT OUTER JOIN is the result of a left outer join (or simply left join) for table L and R always contains all records of the "left" table (L), even if the join-condition does not find any matching record in the "right" table ®. This means that if the ON clause matches 0 (zero) records in R (for a given record in L), the join will still return a row in the result (for that record)—but with NULL in each column from R. A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table.
SELECT * FROM TableL L LEFT OUTER JOIN TableR R ON L.key = R.key;
Another variation of LEFT OUTER JOIN where Right Table Key is null.
SELECT * FROM TableL L LEFT OUTER JOIN TableR R ON L.key = R.key WHERE R.key IS NULL;
Top articles in this category:
- SQL - Write a query to find customers who have no orders yet
- RBS Java Programming Interview Questions
- Citibank Java developer interview questions
- Markit Java Interview Questions
- Morgan Stanley Java Interview Questions
- Cracking core java interviews - question bank
- Multi-threading Java Interview Questions for Investment Bank