Introduction

The real power of SQL comes from working with data from multiple tables at once. The term relational database refers to the fact that the tables relate to each other. Thus they contain common identifiers that allow information from multiple tables to be combined easily.

Types of Joins

There are five types of joins. They are;

• Inner Join

• Outer Join

• Left Join

• Right Join

• Self Join

Inner Join

SQL inner join returns rows from both tables that satisfy the join condition set in the 'ON' statement. In mathematical terms, it is referred to as the intersection of both tables.

Inner join can also be referred to as join. I am going to use two tables from the "flights" and "flight_revenue" datasets to illustrate the joins.

select fl.airline_name,fl.origin_city,fl.destination_state, fr.destination_airport  
from public.flight fl
inner join public.flights_revenue fr 
on fl.destination_airport =fr.destination_airport ;

Left Join

This returns all rows from the left table and matched rows from the right table. It is also referred to as outer left join.

select fl.airline_name,fl.origin_city,fl.destination_state, fr.destination_airport,fr.cargo_rev  
from public.flight fl
left join public.flights_revenue fr 
on fl.destination_airport =fr.destination_airport;

Right Join

The right join is the opposite of the left join. It returns all the rows of the right table and the matched rows on the left table. It can also be referred to as outer right join.

select fl.airline_name,fl.origin_city,fl.destination_state, fr.destination_airport,fr.cargo_rev  
from public.flight fl
right join public.flights_revenue fr  
on fl.destination_airport =fr.destination_airport;

Outer Join

This is also referred to as outer full join or full join. It returns both matched and unmatched rows from both tables.

select fl.airline_name,fl.origin_city,fl.destination_state, fr.destination_airport,fr.cargo_rev  
from public.flight fl
full join public.flights_revenue fr  
on fl.destination_airport =fr.destination_airport;

Outer Joins vs Inner Join

Inner join returns only matched rows from both tables while outer joins return unmatched rows in one or both tables.

Self Join

A self-join in SQL is a specific type of join where a table is joined with itself. In other words, you use a self-join when you want to combine rows from the same table based on a related column within that table.

select fl.airline_name,fl.origin_city,fl.destination_state, fr.destination_airport 
from public.flight fl
join public.flight fr  
on fl.destination_airport =fr.destination_airport
and fl.airline_code ='MQ'
where fr.origin_airport ='SNA';