What is the difference between "INNER JOIN" and "OUTER JOIN"?

sql

join

sql-queries

database-operations

byAlex Kataev·Aug 30, 2024

An INNER JOIN returns only the rows where there is a match in both tables.

SELECT a.id, b.value
FROM TableA a
INNER JOIN TableB b ON a.key = b.key;
-- If match-making was a SQL command, INNER JOIN would be your go-to!

On the flip side, an OUTER JOIN fetches all records from one table (and matched ones from another) or all records from both tables. There are three types: LEFT (all from left), RIGHT (all from right), and FULL (all from both).

-- Left outer join
SELECT a.id, b.value
FROM TableA a
LEFT JOIN TableB b ON a.key = b.key;
-- LEFT JOIN, not politically biased, just a SQL command!

-- Right outer join
SELECT a.id, b.value
FROM TableA a
RIGHT JOIN TableB b ON a.key = b.key;
-- RIGHT JOIN is LEFT JOIN's mirror twin. They're not quarrelling, just oriented differently!

-- Full outer join
SELECT a.id, b.value
FROM TableA a
FULL JOIN TableB b ON a.key = b.key;
-- FULL JOIN, because why settle for half?

In essence, an INNER JOIN is like a picky eater, only taking in matched pairs, while OUTER JOIN is like a food enthusiast, ensuring no data is left unexplored.

Mechanics of JOIN operations

Under the hood of your SQL script, every JOIN operation you choose builds an architectural framework that shapes your output data.

INNER JOIN: The Matchmaker

Focus: To pair up corresponding data from both tables.
When to use: When your data demands stringent matches.
Output: A compact result set leaving no room for unlinked data.

OUTER JOIN: The Includer

LEFT JOIN: It pledges complete loyalty to the left-side table.
RIGHT JOIN: The right-side table is its undeniable favorite.
FULL JOIN: The unifier of both realms.
Point of caution: The crafting of the ON clause is crucial - a slip here could result in unintended record exclusions.
Post-Join Filter effects: A post OUTER JOIN WHERE clause can potentially strip off its inclusiveness, reducing it to an INNER JOIN nature.

Special JOIN conditions

OUTER JOIN ON 1=0: This odd case behaves much like 'UNION ALL'. It returns all records but introduces NULL values where matches are missing.
INNER JOIN ON 1=1: An undercover CROSS JOIN it is! It results in all possible row combinations.

Say it with animations and SQL Fiddles

Demonstrating JOIN operations with animated visuals and SQL Fiddle simulations allows SQL novices to grasp the essence of the operations in a much more interactive way.

Choosing your JOIN type

Your choice of JOIN type is a strategic play in your SQL landscape. It influences the inclusiveness of your SQL query results. Choose wisely!

Demonstrations in SQL

Now, let's switch gears and delve into some practical SQL queries to demonstrate the usage of different JOIN types:

Neatly paired with an INNER JOIN

-- Listing employees and their respective departments
SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.ID;
-- INNER JOIN, uniting employees with their department homes since 1986!

Embrace the unlinked with a LEFT JOIN

-- Displaying all products, even those gathering dust!
SELECT p.ProductName, s.SaleDate
FROM Products p
LEFT JOIN Sales s ON p.ID = s.ProductID;
-- LEFT JOIN, because unsold products have feelings too!

Remember, utilizing a post-JOIN WHERE clause in an OUTER JOIN may unintentionally axe the result's inclusivity.

Joining unconventional

You didn't think SQL was limited to just normal JOINs, did you? Understand some edge cases to make your SQL script more versatile and adaptable.

The LEFT JOIN with a twist

-- Uncovering employees with no assigned departments
SELECT a.id, b.value
FROM TableA a
LEFT JOIN TableB b ON a.key = b.key
WHERE b.key IS NULL;
--Even SQL feels for those employees not assigned to a department.

The FULL OUTER JOIN's panorama

-- Exposing orphaned records from both sides
SELECT a.id AS 'A_id', b.id AS 'B_id', a.value, b.value
FROM TableA a
FULL OUTER JOIN TableB b ON a.key = b.key
WHERE a.key IS NULL OR b.key IS NULL;