What is the difference between INNER JOIN vs OUTER JOIN, and how do LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN fit in?

General Discussions
1

What is the difference between INNER JOIN vs OUTER JOIN, and how do LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN fit in?

I often see INNER JOIN and OUTER JOIN used in SQL queries, but I want to understand their differences more clearly. Specifically, how does an INNER JOIN compare to an OUTER JOIN, and where do LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN come into play? Could someone break it down with examples?

2

INNER JOIN returns only rows that have matching values in both tables.

OUTER JOIN ensures that all records from at least one table are included, even if there’s no match.

Example:

SELECT A.id, A.name, B.id, B.value  
FROM A  
INNER JOIN B ON A.id = B.id;

This query includes only matching rows from both tables.

3

LEFT OUTER JOIN (or simply LEFT JOIN) returns all records from the left table, along with matching records from the right table. Unmatched right-side records return NULL.

RIGHT OUTER JOIN (or RIGHT JOIN) returns all records from the right table, plus matching ones from the left.

Example:

SELECT customers.id, customers.name, orders.id, orders.amount  
FROM customers  
LEFT JOIN orders ON customers.id = orders.customer_id;
This query includes all customers, even if they have no orders. If switched to RIGHT JOIN, it would ensure all orders are included, even if there’s no customer.
4

Hi,

FULL OUTER JOIN includes all records from both tables, filling unmatched rows with NULLs.

Example:

SELECT A.id, A.name, B.id, B.value  
FROM A  
FULL OUTER JOIN B ON A.id = B.id;

This query retrieves all records from both tables, inserting NULLs where matches don’t exist.