"SQL Joins Explained: Inner, Left Outer, and Right Outer Joins"

When working with relational databases, SQL Joins are essential for combining data from multiple tables. This blog focuses on three commonly used joins: INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN, using a clear scenario to demonstrate how they work and how many rows they return.

Scenario: Two Tables

We’ll use two simple tables, Table A and Table B, to demonstrate.

Table A

Value
1
1
1

Table B

Value
1
1
NULL

What Happens When You Join These Tables?

Joins combine rows from both tables based on a condition (typically a column match). Let’s explore how each join behaves in this case:

1. INNER JOIN

An INNER JOIN returns only rows where there is a match between the two tables. Rows without a match are excluded.

Query:

sql

SELECT A.Value AS TableA_Value, B.Value AS TableB_Value FROM TableA A INNER JOIN TableB B ON A.Value = B.Value;

Result:

TableA_Value	TableB_Value
1	1
1	1
1	1
1	1
1	1
1	1

Explanation:

Table A has three 1s, and Table B has two 1s.
Each 1 in Table A matches with both 1s in Table B, producing 6 rows.
The NULL in Table B does not match any value in Table A, so it is ignored.

2. LEFT OUTER JOIN

A LEFT OUTER JOIN returns all rows from the left table (Table A) and their matches from the right table (Table B). If no match exists, the result includes the left table’s values with NULL for the right table’s columns.

Query:

sql

SELECT A.Value AS TableA_Value, B.Value AS TableB_Value FROM TableA A LEFT OUTER JOIN TableB B ON A.Value = B.Value;

Result:

TableA_Value	TableB_Value
1	1
1	1
1	1
1	1
1	1
1	1
1	NULL
1	NULL
1	NULL

Explanation:

Each 1 in Table A matches with both 1s in Table B, creating 6 rows.
The NULL in Table B does not match any values in Table A, so 3 rows from Table A remain unmatched, resulting in NULL for TableB_Value.
Total rows = 6 (matches) + 3 (unmatched rows from Table A) = 9 rows.

3. RIGHT OUTER JOIN

A RIGHT OUTER JOIN returns all rows from the right table (Table B) and their matches from the left table (Table A). If no match exists, the result includes the right table’s values with NULL for the left table’s columns.

Query:

sql

SELECT A.Value AS TableA_Value, B.Value AS TableB_Value FROM TableA A RIGHT OUTER JOIN TableB B ON A.Value = B.Value;

Result:

TableA_Value	TableB_Value
1	1
1	1
1	1
1	1
1	1
1	1
NULL	NULL

Explanation:

Each 1 in Table A matches with both 1s in Table B, producing 6 rows.
The NULL in Table B does not match any values in Table A, resulting in 1 additional row with NULL for TableA_Value.
Total rows = 6 (matches) + 1 (unmatched rows from Table B) = 7 rows.

Summary of Rows Returned by Each Join

Join Type	Total Rows Returned	Explanation
INNER JOIN	6	Only matching rows from both tables.
LEFT OUTER JOIN	9	All rows from Table A, with unmatched rows filled with `NULL`.
RIGHT OUTER JOIN	7	All rows from Table B, with unmatched rows filled with `NULL`.

Visual Representation of the Joins

Join Type	Matching Rows	Extra Rows (Unmatched)	Total Rows
INNER JOIN	6	0	6
LEFT OUTER	6	3 (from Table A)	9
RIGHT OUTER	6	1 (from Table B)	7

Inbound and Outbound Links

Inbound Link: SQL Joins With Practical Examples
Outbound Link: Advanced SQL Joins Explained

When to Use Each Join

INNER JOIN: Use when you only need rows with matching data in both tables.
LEFT OUTER JOIN: Use when you need all rows from the left table, including those without matches.
RIGHT OUTER JOIN: Use when you need all rows from the right table, including those without matches.

Conclusion

Understanding SQL joins is critical for effective data analysis. By mastering INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN, you can intelligently combine data and retrieve the specific information you need.

Let me know if you’d like SEO-friendly metadata next!