SQL. Объединение таблиц с помощью оператора "JOIN"

Команды UNION необходима для объединения таблиц (объединяет данные из
нескольких таблиц в одну при выборке) ,
а команды JOIN, INNER JOIN,
LEFT JOIN, RIGHT JOIN
используются для связывания таблиц по определенным полям связи.

UNION

Создадим две таблицы (покупателей и стран) :

CREATE TABLE CUSTOMERS_NAME(
  ID   INT              NOT NULL,
  NAME VARCHAR (20)     NOT NULL,      
  PRIMARY KEY (ID)
);

CREATE TABLE CUSTOMERS_СOUNTRY(
  ID   INT              NOT NULL,
  COUNTRY VARCHAR (20)     NOT NULL,      
  PRIMARY KEY (ID)
);

insert into CUSTOMERS_NAME (ID, NAME) values(
1, "Mike");
insert into CUSTOMERS_NAME (ID, NAME) values(
2, "Jhon");
insert into CUSTOMERS_NAME (ID, NAME) values(
3, "Piter");
insert into CUSTOMERS_NAME (ID, NAME) values(
4, "Sebastyan");
insert into CUSTOMERS_NAME (ID, NAME) values(
5, "White");

insert into CUSTOMERS_СOUNTRY (ID, COUNTRY) values(
1, "СOUNTRY_1");
insert into CUSTOMERS_СOUNTRY (ID, COUNTRY) values(
2, "СOUNTRY_2");
insert into CUSTOMERS_СOUNTRY (ID, COUNTRY) values(
3, "СOUNTRY_3");
insert into CUSTOMERS_СOUNTRY (ID, COUNTRY) values(
4, "СOUNTRY_4");
insert into CUSTOMERS_СOUNTRY (ID, COUNTRY) values(
5, "СOUNTRY_5");
SELECT * FROM CUSTOMERS_NAME; SELECT * FROM CUSTOMERS_СOUNTRY;

Объединим эти таблицы добавив строчку:

SELECT ID, NAME FROM CUSTOMERS_NAME UNION 
ALL SELECT ID, COUNTRY FROM CUSTOMERS_СOUNTRY;

Запустить код: https://paiza.io

LEFT JOIN

 

CREATE TABLE CUSTOMERS_NAME(
  ID   INT              NOT NULL,
  NAME VARCHAR (20)     NOT NULL,      
  PRIMARY KEY (ID)
);

CREATE TABLE CUSTOMERS_СOUNTRY(
  ID   INT              NOT NULL,
  COUNTRY VARCHAR (20)     NOT NULL,
  COUNTRY_ID INT NOT NULL,     
  PRIMARY KEY (ID)
);
insert into CUSTOMERS_NAME (ID, NAME) values(
1, "Mike");
insert into CUSTOMERS_NAME (ID, NAME) values(
2, "Jhon");
insert into CUSTOMERS_NAME (ID, NAME) values(
3, "Piter");
insert into CUSTOMERS_NAME (ID, NAME) values(
4, "Sebastyan");
insert into CUSTOMERS_NAME (ID, NAME) values(
5, "White");

insert into CUSTOMERS_СOUNTRY (ID, COUNTRY,COUNTRY_ID) values(
1, "СOUNTRY_1", 1);
insert into CUSTOMERS_СOUNTRY (ID, COUNTRY,COUNTRY_ID) values(
2, "СOUNTRY_2", 0);
insert into CUSTOMERS_СOUNTRY (ID, COUNTRY,COUNTRY_ID) values(
3, "СOUNTRY_3", 1);
insert into CUSTOMERS_СOUNTRY (ID, COUNTRY,COUNTRY_ID) values(
4, "СOUNTRY_4", 8);
insert into CUSTOMERS_СOUNTRY (ID, COUNTRY,COUNTRY_ID) values(
5, "СOUNTRY_5", 9);

SELECT
CUSTOMERS_NAME.ID as customers_name_id, 
CUSTOMERS_NAME.NAME as customers_name_name,  
CUSTOMERS_СOUNTRY.ID as customers_country_id, 
CUSTOMERS_СOUNTRY.COUNTRY as  customers_country_country, 
CUSTOMERS_СOUNTRY.COUNTRY_ID as customers_country_country_id
FROM CUSTOMERS_СOUNTRY
LEFT JOIN 
CUSTOMERS_NAME 
ON 
CUSTOMERS_NAME.ID = CUSTOMERS_СOUNTRY.COUNTRY_ID

RIGHT JOIN

SELECT
CUSTOMERS_NAME.ID as customers_name_id, 
CUSTOMERS_NAME.NAME as customers_name_name,  
CUSTOMERS_СOUNTRY.ID as customers_country_id, 
CUSTOMERS_СOUNTRY.COUNTRY as  customers_country_country, 
CUSTOMERS_СOUNTRY.COUNTRY_ID as customers_country_country_id
FROM CUSTOMERS_СOUNTRY
RIGHT JOIN 
CUSTOMERS_NAME 
ON 
CUSTOMERS_NAME.ID = CUSTOMERS_СOUNTRY.COUNTRY_ID

INNER JOIN

SELECT
CUSTOMERS_NAME.ID as customers_name_id, 
CUSTOMERS_NAME.NAME as customers_name_name,  
CUSTOMERS_СOUNTRY.ID as customers_country_id, 
CUSTOMERS_СOUNTRY.COUNTRY as  customers_country_country, 
CUSTOMERS_СOUNTRY.COUNTRY_ID as customers_country_country_id
FROM CUSTOMERS_СOUNTRY
INNER JOIN 
CUSTOMERS_NAME 
ON 
CUSTOMERS_NAME.ID = CUSTOMERS_СOUNTRY.COUNTRY_ID

Источники:
1) Базовый курс SQL. JOIN-ы, или объединения таблиц
2) Mysql JOIN, LEFT JOIN, RIGHT JOIN - отличия - какая разница. Виды джойнов
3) http://old.code.mu/sql/union.html