Inner Join in SQL vs. Outer Join in SQL: What's the Difference?
Edited by Aimie Carlson || By Janet White || Published on February 4, 2024
An inner join in SQL returns rows where there is a match in both tables, while an outer join returns all rows from one table and matched rows from the other.
Key Differences
An inner join in SQL is used to return rows where there is at least one match in both tables being joined. An outer join, however, returns all rows from one table and the matched rows from the other, including rows with no match.
Inner join focuses on the intersection of two tables and excludes rows that do not have matching values. Outer join, in contrast, includes all rows from at least one of the tables, regardless of whether they have matching rows in the other table.
Inner joins typically result in fewer rows than outer joins, as they only include rows with matching values in both tables. Outer joins can return more rows, including those with no corresponding matches in the joined table.
The inner join is often used when only the matching data from both tables is required. Outer joins are useful when it's important to retain all records from one table, displaying NULL in columns where there's no match.
In terms of data completeness, an inner join provides a subset of data from both tables, whereas an outer join provides a more comprehensive view, including unmatched records.
ADVERTISEMENT
Comparison Chart
Match Requirement
Requires matching entries in both tables.
Includes all records from one table, match or no match.
Result Set
Only rows with matches in both tables.
All rows from one table, with or without matches.
Usage
Used when only matching data is needed.
Used to retain all records from one table.
Null Handling
Excludes rows with null values in join condition.
Includes rows with null values where no match is found.
Typical Scenario
Ideal for matching related data across tables.
Useful for reports where data completeness is essential.
ADVERTISEMENT
Inner Join in SQL and Outer Join in SQL Definitions
Inner Join in SQL
"Joins two tables based on a related column and excludes non-matching rows."
SELECT * FROM TableA INNER JOIN TableB ON TableA.key = TableB.key;
Outer Join in SQL
"A join that includes rows even if they don't have a matching counterpart."
SELECT Employees.Name, Departments.Name FROM Employees RIGHT OUTER JOIN Departments ON Employees.DeptID = Departments.ID;
Inner Join in SQL
"A join that retrieves rows with common values in both tables."
SELECT Employees.Name, Departments.Name FROM Employees INNER JOIN Departments ON Employees.DeptID = Departments.ID;
Outer Join in SQL
"An SQL join that fetches records even if join condition fails in one table."
SELECT A., B. FROM TableA A FULL OUTER JOIN TableB B ON A.common_field = B.common_field;
Inner Join in SQL
"Returns rows where the join condition is true for both tables."
SELECT name, order_date FROM Customers INNER JOIN Orders ON Customers.id = Orders.customer_id;
Outer Join in SQL
"Combines rows from two tables, preserving unmatched rows from one table."
SELECT * FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Inner Join in SQL
"Combines rows from different tables when there are matching columns."
SELECT * FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Outer Join in SQL
"Returns matched rows and unmatched rows from one or both tables."
SELECT name, order_date FROM Customers FULL OUTER JOIN Orders ON Customers.id = Orders.customer_id;
Inner Join in SQL
"An SQL join that fetches records with matching values in a join column."
SELECT A., B. FROM TableA A INNER JOIN TableB B ON A.common_field = B.common_field;
Outer Join in SQL
"Joins tables while keeping the rows that don’t have matching join keys."
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.key = TableB.key;
FAQs
What does an outer join in SQL do?
An outer join in SQL returns all rows from one table and the matching rows from the other, including rows with no match.
What is a left outer join?
A left outer join returns all rows from the left table and matching rows from the right table.
Can inner joins return null values?
Inner joins do not return rows with null join keys in either table.
What is an inner join in SQL?
An inner join in SQL is a join that returns rows with matching values in both joined tables.
Is a full outer join different from left and right outer joins?
Yes, a full outer join combines the results of both left and right outer joins.
Can outer joins be nested?
Yes, outer joins can be nested with other joins.
Are inner joins faster than outer joins?
Inner joins can be faster as they return fewer rows compared to outer joins.
What's the difference between JOIN and INNER JOIN?
There's no difference; JOIN defaults to INNER JOIN in SQL.
When should I use an inner join?
Use an inner join when you need rows that have corresponding data in both tables.
What happens if the join condition is not met in an inner join?
In an inner join, rows that do not meet the join condition are not included in the result set.
Can I join more than two tables using inner or outer joins?
Yes, both inner and outer joins can be used to join multiple tables.
What's a right outer join?
A right outer join returns all rows from the right table and matching rows from the left table.
How do null values affect an inner join?
Null values in join columns prevent rows from being matched in an inner join.
What's a cross join?
A cross join is a Cartesian join that returns all possible pairs of rows from the joined tables.
How do I choose between an inner and an outer join?
Choose based on whether you need all rows from one table (outer) or only matching rows (inner).
Can outer joins result in duplicate rows?
Yes, outer joins can result in duplicate rows if there are multiple matches in the joined table.
How are unmatched rows represented in an outer join?
In an outer join, unmatched rows are shown with NULLs in columns from the table where there is no match.
Do I need to use the OUTER keyword for outer joins?
The OUTER keyword is optional as LEFT JOIN is equivalent to LEFT OUTER JOIN.
Is it possible to filter results in an outer join?
Yes, you can use the WHERE clause to filter results in an outer join.
Can I use aggregate functions with inner and outer joins?
Yes, aggregate functions can be used with both inner and outer joins.
About Author
Written by
Janet WhiteJanet White has been an esteemed writer and blogger for Difference Wiki. Holding a Master's degree in Science and Medical Journalism from the prestigious Boston University, she has consistently demonstrated her expertise and passion for her field. When she's not immersed in her work, Janet relishes her time exercising, delving into a good book, and cherishing moments with friends and family.
Edited by
Aimie CarlsonAimie Carlson, holding a master's degree in English literature, is a fervent English language enthusiast. She lends her writing talents to Difference Wiki, a prominent website that specializes in comparisons, offering readers insightful analyses that both captivate and inform.
