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.
Red colored area in diagram reflects the result of INNER JOIN on two Tables L & R
Explicit Join Notation
SELECT
*
FROM
employee E
INNER JOIN
department D ON E.DepartmentID = D.DepartmentID;Implicit Join Notation
SELECT
*
FROM
employee E,
department D
WHERE
E.DepartmentID = D.DepartmentID;Example usecase
SQL - Find Customers who have placed at least one order
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.
Left Outer Join SQL
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.
SQL Query for LEFT OUTER JOIN
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
Recommended books for interview preparation:
Book you may be interested in..
Book you may be interested in..
