INNER JOIN vs OUTER JOIN in SQL: Key Differences – JOIN in SQL is the process of merging two or more tables into one. One unique kind of connection that has one or more main keys is a linked table.

INNER JOIN vs OUTER JOIN in SQL:

The main difference between an INNER JOIN and an OUTER JOIN is that the inner join will keep only the information from both tables that’s related to each other (in the resulting table). An Outer Join, on the other hand, will also keep information that is not related to the other table in the resulting table.

What is a JOIN in SQL?

Joins in SQL are used to combine data and rows from two or more tables based on a common column or a field. There are two main types of joins in SQL : inner joins and outer joins.

Inner joins return common information between tables and an outer join returns information in the resulting table that the inner join returns along with the information that is not common with the other table.

What is INNER JOIN?

The inner join in SQL will return the common or the matching records between the tables in the resulting table. It can be represented as:

How INNER JOIN Works:

When the database runs an INNER JOIN, it does this internally:

1. Reads rows from one table

2. Searches for matching rows in the other table

3. Keeps only matching pairs

4. Immediately ignores rows with no match

5. Outputs the matched result set

Memory & Execution Insight

• Matches are processed in memory

• Temporary structures (hash tables or sorted buffers) may be used

• The engine stops checking once a match fails

The syntax for INNER JOIN in SQL is:

SELECT *
FROM table_name_1
INNER JOIN table_name_2
ON table_name_1.common_field = table_name_2.common_field;

An Example For INNER JOIN:

Create Tables:

CREATE TABLE countries_europe (
country_id INT PRIMARY KEY,
country_name VARCHAR(50),
region VARCHAR(30)
);

CREATE TABLE cities_europe (
city_id INT PRIMARY KEY,
city_name VARCHAR(50),
country_id INT,
population INT
);

Insert Data:

INSERT INTO countries_europe VALUES
(1,’Germany’,’Western Europe’),
(2,’France’,’Western Europe’),
(3,’Italy’,’Southern Europe’),
(4,’Spain’,’Southern Europe’),
(5,’Netherlands’,’Western Europe’),
(6,’Belgium’,’Western Europe’),
(7,’Sweden’,’Northern Europe’),
(8,’Norway’,’Northern Europe’),
(9,’Poland’,’Eastern Europe’),
(10,’Austria’,’Central Europe’);

INSERT INTO cities_europe VALUES
(101,’Berlin’,1,3600000),
(102,’Munich’,1,1500000),
(103,’Hamburg’,1,1800000),
(104,’Paris’,2,2140000),
(105,’Lyon’,2,520000),
(106,’Marseille’,2,870000),
(107,’Rome’,3,2870000),
(108,’Milan’,3,1350000),
(109,’Naples’,3,960000),
(110,’Madrid’,4,3200000),
(111,’Barcelona’,4,1600000),
(112,’Valencia’,4,800000),
(113,’Amsterdam’,5,870000),
(114,’Rotterdam’,5,650000),
(115,’Brussels’,6,1200000),
(116,’Stockholm’,7,980000),
(117,’Oslo’,8,700000),
(118,’Warsaw’,9,1800000),
(119,’Krakow’,9,770000),
(120,’Vienna’,10,1900000);

INNER JOIN with IF EXISTS (Backend Filter):

IF EXISTS (
SELECT 1 FROM cities_europe
)
BEGIN
SELECT
c.country_name,
ci.city_name,
ci.population
FROM countries_europe c
INNER JOIN cities_europe ci
ON c.country_id = ci.country_id;
END;

OUTPUT:

country_name	city_name	population
Germany	Berlin	3600000
Germany	Munich	1500000
France	Paris	2140000
Italy	Rome	2870000
Spain	Madrid	3200000
Netherlands	Amsterdam	870000
Poland	Warsaw	1800000
Austria	Vienna	1900000

Backend Execution Logic of INNER join:

Step-by-Step:

1. IF EXISTS check

   • Confirms table has data

   • Avoids unnecessary execution

2. Optimizer chooses join strategy

   • Likely Hash Join (medium dataset)

3. Build hash table

   • Smaller table: countries_europe

4. Scan cities_europe

   • Lookup matching country_id

5. Match found → output row

6. No match → discard

What is OUTER JOIN?

An outer join returns a set of records (or rows) that includes those returned by the inner join, along with other rows for which no corresponding match was found in the other table.

There are three types of outer joins:

1. Left outer join (or left join)
2. Right outer join (or right join)
3. Full Outer Join (or Full Join)

Outer joins are used when you want to return all data, not just the interrelated data. The next few sections of this article will describe each of the outer joins.

What is LEFT OUTER JOIN?

The left outer join is one of the types of outer join which returns all the records from the left table and only the matching and related records from the right table. The left join or the left outer join is the combination of the result of the inner join plus all the records of the left table.

The syntax for LEFT OUTER JOIN:

SELECT student_details_1.name, student_details_2.city
FROM student_details_1
LEFT JOIN student_details_2
ON student_details_1.roll_number = student_details_2.roll_number;

An Example For LEFT OUTER JOIN:

SELECT
c.country_name,
ci.city_name,
ci.population
FROM countries_europe c
LEFT OUTER JOIN cities_europe ci
ON c.country_id = ci.country_id;

How LEFT OUTER JOIN Works:

• Database scans countries_europe first

• Tries to find matching rows in cities_europe

• If match found → return data

• If no match → return NULL for city columns

OUTPUT:

country_name	city_name	population
Germany	Berlin	3600000
Germany	Munich	1500000
France	Paris	2140000
Austria	Vienna	1900000
Belgium	NULL	NULL

What is RIGHT OUTER JOIN?

Right join is another type of outer join, which returns all the records from the right table and only the matching records from the left table. The right join or the right outer join is the combination of the result of the inner join plus all the records of the right table.

The syntax for RIGHT OUTER JOIN:

SELECT *
FROM table_name_1
RIGHT JOIN Table_name_2
ON Table_name_1.Common_column_Name=table_name_2.Common_column_name;

An Example For RIGHT OUTER JOIN:

SELECT
c.country_name,
ci.city_name,
ci.population
FROM countries_europe c
RIGHT OUTER JOIN cities_europe ci
ON c.country_id = ci.country_id;

How RIGHT OUTER JOIN Works:

• Database scans cities_europe first

• Tries to find matching rows in countries_europe

• If no match → country columns become NULL

OUTPUT: