Understand sql SELF, FULL, LEFT, RIGHT, INNER, NATURAL, CROSS joins.

join_examples.sql

CREATE TABLE IF NOT EXISTS continents ( continent_code VARCHAR(2) PRIMARY KEY, continent_name VARCHAR(25) NOT NULL );
INSERT IGNORE INTO continents VALUES ('AS', 'Asia'), ('AF', 'Africa'), ('NA', 'North America'), ('SA', 'South America'), ('EU', 'Europe'), ('AU', 'Australia')
ON DUPLICATE KEY UPDATE continent_name = VALUES(continent_name);

CREATE TABLE IF NOT EXISTS countries ( country_code VARCHAR(2) PRIMARY KEY, country_name VARCHAR(25), continent_code VARCHAR(2) );
INSERT IGNORE INTO countries VALUES ('IN', 'India', 'AS'), ('ZA', 'South Africa', 'AF'), ('US', 'United States', 'NA'), ('BR', 'Brazil', 'SA'), ('AU', 'Australia', 'AU'), ('AQ', 'Antarctica', 'AN')
ON DUPLICATE KEY UPDATE country_name = VALUES(country_name);

/* "SELF" JOIN trick. */
SELECT cr1.country_name "Name Self", cr1.country_code "Country Self", cr1.continent_code "Continent Self"
FROM countries cr1
JOIN countries cr2 
ON cr1.country_code = cr2.continent_code; /* Join by a row !COUNTRY code equal to its !CONTINENT code. */

/* MariaDB does not support FULL JOIN so there is a trick. */
SELECT left_tbl.continent_name "Continent Full", right_tbl.country_name "Country Full"
FROM continents left_tbl /* FIrst selected */
LEFT JOIN countries right_tbl  /* Second selected */
ON left_tbl.continent_code = right_tbl.continent_code
UNION /* <--- See. */
SELECT left_tbl.continent_name, right_tbl.country_name
FROM continents left_tbl /* Second selected */
RIGHT JOIN countries right_tbl /* FIrst selected */
ON left_tbl.continent_code = right_tbl.continent_code;

/* LEFT JOIN defines the data selection order as from left to right:
First selected all NOT NULL data from the left_tbl and then anything what could be found in the right_tbl; If no respective records found in the right_tbl, display NULL placeholder. */
SELECT left_tbl.continent_name "Continent Left", right_tbl.country_name "Country Left"
FROM continents left_tbl /* FIrst selected */
LEFT JOIN countries right_tbl /* Second selected */
ON left_tbl.continent_code = right_tbl.continent_code;

/* RIGHT JOIN defines the data selection order as from right to left:
First select all NOT NULL data from the right_tbl and then anything what could be found in the left_tbl; If no respective records found in the left_tbl, display NULL placeholder. */
SELECT left_tbl.continent_name "Continent Right", right_tbl.country_name "Country Right"
FROM continents left_tbl /* Second selected */
RIGHT JOIN countries right_tbl /* FIrst selected */
ON left_tbl.continent_code = right_tbl.continent_code;

/* NATURAL JOIN is the same as the INNER JOIN but without "ON" or "USING" clause. */
SELECT cr.country_name "Country NATURAL", ct.continent_name "Continent NATURAL"
FROM continents ct
NATURAL JOIN countries cr;

/* The same as the INNER JOIN but with "USING(column_to_join_by)" clause. */
SELECT cr.country_name "Country Inner Using", ct.continent_name "Continent Inner Using"
FROM continents ct
/* INNER */ JOIN countries cr 
USING (continent_code);

/* INNER JOIN display all NOT NULL records in both tables. */
SELECT cr.country_name "Country Inner On", ct.continent_name "Continent Inner On"
FROM continents ct
/* INNER */ JOIN countries cr 
ON ct.continent_code = cr.continent_code;

/* CROSS JOIN is a Cartesian product of all the records in both of the tables (countries X continents). Another words, it will display all possible unique combinations of a country name with all available continents' names.*/
SELECT cr.country_name "Country Cross", ct.continent_name "Continent Cross"
FROM continents ct
CROSS JOIN countries cr;

See more: https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join