Joins are a very important part of SQL. In order to understand JOINS better, let's take two tables 'continent' and 'county' containing some records.



Table Name: Continent

id continent_code continent_name
1 AS Asia
2 AF Africa
3 NA North America
4 SA South America
5 EU Europe
6 AU Australia


Table Name: Country

id country_code country_name continent_code
1 IN India AS
2 ZA South Africa AF
3 US United States of America NA
4 BR Brazil SA
5 AU Australia AU
6 AQ Antarctica AN



INNER JOIN

An Inner Join will fetch only those records which are present in both the joined tables. The matching of the column is based on the columns used for joining these two tables. Inner Join can also be used as Join with Select query.

Let's implement INNER JOIN on the continent  table with the country table:

SELECT cr.country_name, ct.continent_name 
FROM continent ct 
INNER JOIN country cr 
              ON ct.continent_code = cr.continent_code;


continent_name country_name
India Asia
South Africa Africa
United States of America North America
Brazil South America
Australia Australia



LEFT JOIN

Left Join will fetch all records from the left table. All the records from the left side of the table will be fetched whether the value is present or not present in the right table. If the value is not present on the right side of the table then null is displayed. LEFT JOIN can also be represented as LEFT OUTER JOIN in the select query.

 Let's implement LEFT JOIN on the continent  table with the country table:

SELECT cr.country_name, ct.continent_name 
FROM continent ct 
LEFT JOIN country cr 
              ON ct.continent_code = cr.continent_code;




continent_name country_name
India Asia
South Africa Africa
United States of America North America
Brazil South America
Null Europe
Australia Australia


RIGHT JOIN

Right Join will fetch all records from the right table. All the records from the right side of the table will be fetched whether the value is present or not present in the left table. If the value is not present on the left side of the table then null is displayed. RIGHT JOIN can also be represented as RIGHT OUTER JOIN in the select query.

 Let's implement RIGHT JOIN on the continent  table with the country table:

SELECT cr.country_name, ct.continent_name 
FROM continent ct 
RIGHT JOIN country cr 
              ON ct.continent_code = cr.continent_code;


continent_name country_name
India Asia
South Africa Africa
United States of America North America
Brazil South America
Australia Australia
Antarctica Null


FULL JOIN

FULL Join will fetch records from the left and right tables. FULL Join is the combination of INNER, LEFT, and RIGHT Join. 

FULL Join will fetch all the matching records in the left and right table + all the records from the left table even if there are no records present in the right table + all the records from the right table even if there is no record present in the left table. 

FULL Join can also be represented as FULL OUTER JOIN in your select query.


Let's implement FULL JOIN on the continent  table with the country table:

SELECT cr.country_name, ct.continent_name 
FROM continent ct 
FULL OUTER JOIN country cr 
              ON ct.continent_code = cr.continent_code;



continent_name country_name
India Asia
South Africa Africa
United States of America North America
Brazil South America
Null Europe
Australia Australia
Antarctica Null