Chapter 10

JOIN

JOIN ဆိုတာကတော့ ကျွန်တော်တို့တွေ Table တစ်ခု ထက်ပိုပြီး query ဆွဲထုတ်ချင်သည့် အခါမှာ အသုံးပြုပါတယ်။

JOIN အမျိုးအစားတွေကတော့

INNER JOIN
OUTER JOIN

ဆိုပြီး ရှိပါတယ်။

OUTER JOIN မှာ

LEFT JOIN
RIGHT JOIN
FULL JOIN

ဆိုပြီး ရှိပါတယ်။ အောက်ပါ ပုံလေးကို ကြည့်ရင် ပိုရှင်းပါလိမ့်မယ်။

Table ၂ ခုကို ချိတ်ဆက်သည့် အခါမှာတော့ လိုသည့် data တွေကို ဆွဲယူဖို့ အတွက် အသုံးပြုပါတယ်။

Relationship

Join ကို မစခင်မှာ ကျွန်တော်တို့တွေ school database တစ်ခု တည်ဆောက်ရအောင်။

create database myschool;
use myschool;

အခု teacher table ကို တည်ဆောက်ပါမယ်။

create table teachers (
	id int auto_increment primary key,
	name varchar(255) not null);

Teacher table ပြီးတော့ department table ကို တည်ဆောက်ပါမယ်။ department မှာ head teacher အနေနဲ့ teacher နာမည်မထည့်ပဲ teacher table နဲ့ ချိတ်ဆက်ပါမယ်။ teacher id က department table မှာ foreign key ဖြစ်ပါမယ်။

create table departments (
	dep_code varchar(255) not null primary key,
	name varchar(255) not null,
	teacher_id int not null,
	foreign key (teacher_id) references teachers (id) ON DELETE CASCADE
);

အခု ဆိုရင် departments မှာ teacher id နဲ့ ချိတ်ထားပြီးပါပြီ။ teacher_id ဟာ int ဖြစ်ရပါမယ်။

ON DELETE CASCADE ကတော့ teacher table မှာ teacher_id ကို ဖျက်လိုက်ရင် department table မှာ တစ်ခါတည်း ပျက်သွားဖို့ပါ။

အခု student table ဆောက်ပါမယ်။

create table students (
	student_id int auto_increment primary key,
	name varchar(255) not null,
	dep_code varchar(255) not null,
	foreign key (dep_code) references departments (dep_code)
);

အခု student table ကို ဆောက်ပြီးပါပြီ။ student table မှာ department code နဲ့ ချိတ်ထားပါတယ်။ foreign key ထည့်ထားတာ တွေ့နိုင်ပါတယ်။ Department code က ဖျက်လိုက်တာနဲ့ student တွေ အလိုလို မပျက်သွား စေချင်လို့ ON DELETE CASCADE ကို မထည့်ထားပါဘူး။

ပထမဆုံး teachers ကို ထည့်ပါမယ်။ ပြီးရင် department ကို ထည့်ပါမယ်။ နောက်ဆုံးမှ students ကို ထည့်ပါမယ်။

INSERT INTO teachers (name) VALUES 
('U Aung Gyi'),
('U Maung Maung Myint'),
('Daw Aung Aung')
;

teachers (name) ဆိုတာကတော့ teachers table ထဲက name column ကို ထည့်မယ် ပြောတာပါ။ ဘာကြောင့်လဲဆိုတော့ auto_increment ကို id မှာ ထည့်သွင်းထားတာကြောင့်ပါ။

အခု teachers table ထဲက နေ ထုတ်ကြည့်ရအောင်။

SELECT * FROM teachers;

+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | U Aung Gyi          |
|  2 | U Maung Maung Myint |
|  3 | Daw Aung Aung       |
+----+---------------------+

အခု ဆိုရင် teachers တွေက id နဲ့ ရပါပြီ။

အခု ကျွန်တော် တို့ department ထည့်ပါမယ်။

INSERT INTO departments (dep_code,name,teacher_id) VALUES 
('CS_101','CS 101',1),
('CS_102','CS 102',2),
('CT_101','CT 101',3),
('CT_102','CT 102',3)
;

အခု ဆိုရင် departments table ထဲကို data တွေ ဝင်သွားပါပြီ။ ON DELETE CASCADE ကို အသုံးပြုထားသည့် အတွက် ကြောင့် teacher id 3 ကို teacher ထဲက ဖျက်လိုက်ရင် departments ထဲမှာ ပျက်သွားမှာပါ။

အရင်ဆုံး data ထုတ်ကြည့်ရအောင်။

 select * from departments;

+----------+--------+------------+
| dep_code | name   | teacher_id |
+----------+--------+------------+
| CS_101   | CS 101 |          1 |
| CS_102   | CS 102 |          2 |
| CT_101   | CT 101 |          3 |
| CT_102   | CT 102 |          3 |
+----------+--------+------------+

အခု teacher ID 3 ကို ဖျက်ပါမယ်။

DELETE FROM teachers WHERE id = 3;

အခု ပြန်ပြီး ထုတ်ကြည့်ရအောင်။

 SELECT * FROM departments;

+----------+--------+------------+
| dep_code | name   | teacher_id |
+----------+--------+------------+
| CS_101   | CS 101 |          1 |
| CS_102   | CS 102 |          2 |
+----------+--------+------------+

ON DELETE CASCADE ထည့်ထားသည့် အတွက်ကြောင့် teachers table မှာ ဖျက်လိုက်တာနှင့် departments မှာလည်း တစ်ခါတည်း ပျက်သွားပါမယ်။

အခု students table မှာ ထည့်ပါမယ်။

INSERT INTO students(name,dep_code) VALUES
('Mg Mg','CS_101'),
('Aung Gyi','CS_101'),
('Yang Aung','CS_101'),
('Kyaw Kyaw','CS_102'),
('Moe Moe','CS_102')
;

အခု students table ကို ထုတ်ကြည့်ရအောင်။

 select * from students;

+------------+-----------+----------+
| student_id | name      | dep_code |
+------------+-----------+----------+
|          1 | Mg Mg     | CS_101   |
|          2 | Aung Gyi  | CS_101   |
|          3 | Yang Aung | CS_101   |
|          4 | Kyaw Kyaw | CS_102   |
|          5 | Moe Moe   | CS_102   |
+------------+-----------+----------+

အခု department က CS_102 ကို ဖျက်ကြည့်ရအောင်။

DELETE FROM departments WHERE dep_code = 'CS_102';

students table မှာ dep_code foreign key ရှိသည့် အတွက် ဖျက်လို့ရမှာ မဟုတ်ပါဘူး။

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`myschool`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`dep_code`) REFERENCES `departments` (`dep_code`))

Students table မှာ ဖျက်ချင်သည့် CS_102 မရှိတော့မှသာ departments မှာ ဖျက်လို့ရပါမယ်။

INNER JOIN

အခု Inner Join ကို စမ်းကြည့်ရအောင်။ Student တွေ တက်ရောက်နေသည့် department name ကို သိချင်တယ်။ အခု ပုံစံ အရ department name ကို dep_code နဲ့ ချိတ်ထားတယ်။ Student တစ်ယောက်ဆီမှာ dep_code ရှိတယ်။ Table ၂ ခု ကို Join ပြီးစမ်းကြည့််ရအောင်။

SELECT students.name,departments.name 
FROM students
INNER JOIN departments
ON students.dep_code = departments.dep_code;

+-----------+--------+
| name      | name   |
+-----------+--------+
| Mg Mg     | CS 101 |
| Aung Gyi  | CS 101 |
| Yang Aung | CS 101 |
| Kyaw Kyaw | CS 102 |
| Moe Moe   | CS 102 |
+-----------+--------+

student name နဲ့ department ထွက်လာပါပြီ။ students မှာလည်း name ပါသလို departments မှာလည်း name ပါသည့် အတွက် ဘယ် table က data ယူမလဲ ဆိုတာကို ရေးသားလိုသည့် အတွက် [table].[column] ကို အသုံးပြုပါတယ်။ students.name ဆိုရင် students table ထဲက name ကို ဖော်ပြရန် ဖြစ်ပြီး departments.name ကတော့ departments table ထဲက name ကို ဖော်ပြရန် ဖြစ်ပါတယ်။

INNER JOIN ဖြစ်သည့် အတွက်ကြောင့် students table မှာ ရှိသည့် data နှင့် departments မှာ ရှိသည့် data ၂ ခုလုံး ချိတ်ဆက်ထားသည့် data ကို သာပြပေးမှာပါ။

INNER JOIN ရဲ့ syntax က

SELECT [table1].columns,[table2].columns
FROM [table1]
INNER JOIN [table2]
ON [table1].column = [table2].column

JOIN လုပ်သည့်အခါမှာတော့ table ၂ ခု မှာ တူညီသည့် columns နှင့်သာ ချိတ်ဆက်လို့ရပါတယ်။ နောက်ပြီး WHERE CONDITION ပါ ထပ်ဖြည့်နိုင်ပါတယ်။

အထက်ပါ select ကို အနည်းငယ် ပြန်ပြင်ပါမယ်။

SELECT students.name AS student_name,departments.name as dep_name
FROM students
INNER JOIN departments
ON students.dep_code = departments.dep_code
WHERE departments.dep_code = 'CS_102';

+--------------+----------+
| student_name | dep_name |
+--------------+----------+
| Kyaw Kyaw    | CS 102   |
| Moe Moe      | CS 102   |
+--------------+----------+

အခုဆိုရင် name, name အစား student_name , dep_name ဆိုပြီး ပေါ်လာပါပြီ။ dep_code က CS_102 ဖြစ်သည့် information တွေသာ ပေါ်လာပါမယ်။

LEFT JOIN

အခု table design အရ left join အတွက် အဆင်မပြေပါဘူး။ LEFT JOIN အနေနဲ့ table 1 မှာ table 2 data မပါပဲ ထည့်သွင်းဖို့ ဦးစွာ table ကို ပြင်ပါမယ်။

Studetns Table ကို create လုပ်ခဲ့တုန်းက dep_code ကို not null လို့ ထည့်ခဲ့ပါတယ်။

dep_code varchar(255) not null,

dep_code ကို ကျွန်တော်တို့ nullable ဖြစ်ဖို့ ဦးစွာပြင်ပါမယ်။

ALTER TABLE students MODIFY COLUMN dep_code varchar(255);

အခုဆိုရင် dep_code က NOT NULL ကို ဖြုတ်ပြီးပါပြီ။

အခု students table ထဲကို data ဖြည့်ပါမယ်။

INSERT INTO students (name) VALUES ("Zu Zu"),("Gone Tint");

အခု student table ထဲမှာ dep_code မရှိသည့် name ၂ ခု ထည့်ပြီးပါပြီ။

SELECT * FROM students;

+------------+-----------+----------+
| student_id | name      | dep_code |
+------------+-----------+----------+
|          1 | Mg Mg     | CS_101   |
|          2 | Aung Gyi  | CS_101   |
|          3 | Yang Aung | CS_101   |
|          4 | Kyaw Kyaw | CS_102   |
|          5 | Moe Moe   | CS_102   |
|          6 | Zu Zu     | NULL     |
|          7 | Gone Tint | NULL     |
+------------+-----------+----------+

အခု INNER JOIN ကို ပြန် စမ်းကြည့်ရအောင်။

SELECT students.name as student_name,departments.name as dep_name
FROM students
INNER JOIN departments
ON students.dep_code = departments.dep_code;

+--------------+----------+
| student_name | dep_name |
+--------------+----------+
| Mg Mg        | CS 101   |
| Aung Gyi     | CS 101   |
| Yang Aung    | CS 101   |
| Kyaw Kyaw    | CS 102   |
| Moe Moe      | CS 102   |
+--------------+----------+

dep_code null ဖြစ်နေသည့် student တွေမပါလာပါဘူး။ student အကုန်လုံးပါချင်တယ်။ department name ကိုလည်း ပေါ်အောင် INNER JOIN လုပ်ချင်တယ်ဆိုရင်တော့ LEFT JOIN ကို အသုံးပြုနိုင်ပါတယ်။

SELECT students.name as student_name,departments.name as dep_name
FROM students
LEFT JOIN departments
ON students.dep_code = departments.dep_code;

+--------------+----------+
| student_name | dep_name |
+--------------+----------+
| Mg Mg        | CS 101   |
| Aung Gyi     | CS 101   |
| Yang Aung    | CS 101   |
| Kyaw Kyaw    | CS 102   |
| Moe Moe      | CS 102   |
| Zu Zu        | NULL     |
| Gone Tint    | NULL     |
+--------------+----------+

LEFT JOIN က table 1 က data တွေ အကုန်ဖော်ပြပေးသည့် သဘောပါ။

RIGHT JOIN

RIGHT JOIN ကတော့ table 2 ဘက်က data တွေ အကုန် ဖော်ပြပေးပါတယ်။ အရင် ဆုံး department ဘက်မှာ data အရင် ဖြည့်ပါမယ်။

INSERT INTO departments (dep_code,name,teacher_id) VALUES ("CT_101","Computer Technology 101",1);

CT_101 က student table ထဲမှာ မရှိသေးပါဘူး။

အခု RIGHT JOIN ကို ကြည့်ရအောင်။

SELECT students.name as student_name,departments.name as dep_name
FROM students
RIGHT JOIN departments
ON students.dep_code = departments.dep_code;

+--------------+-------------------------+
| student_name | dep_name                |
+--------------+-------------------------+
| Mg Mg        | CS 101                  |
| Aung Gyi     | CS 101                  |
| Yang Aung    | CS 101                  |
| Kyaw Kyaw    | CS 102                  |
| Moe Moe      | CS 102                  |
| NULL         | Computer Technology 101 |
+--------------+-------------------------+

departments မှာ data ရှိပြီး students table ဘက်မှာ departments နဲ့ မချိတ်ထားသည့် data တွေပါ ထုတ်ထားပါတယ်။

FULL OUTER JOIN

အခု နှစ်ဘက်လုံး က data ကို ဆွဲထုတ်ပါမယ်။ နှစ်ဘက်လုံးက ပါသည့် data ကို လိုချင်ရင်တော့ FULL OUTER JOIN ကို သုံးနိုင်ပါတယ်။ သို့ပေမယ့် MySQL မှာ FULL OUTER JOIN မရှိပါဘူး။

LEFT JOIN နဲ့ RIGHT JOIN ကို UNION ကို သုံးပြီး ထုတ်မှ ရပါမယ်။ Union ဆိုတာကတော့ SQL data ကို ပေါင်းပြီး ပြသ ပေးတာပါ။

SELECT students.name as student_name,departments.name as dep_name
FROM students
LEFT JOIN departments
ON students.dep_code = departments.dep_code

UNION

SELECT students.name as student_name,departments.name as dep_name
FROM students
RIGHT JOIN departments
ON students.dep_code = departments.dep_code;

+--------------+-------------------------+
| student_name | dep_name                |
+--------------+-------------------------+
| Mg Mg        | CS 101                  |
| Aung Gyi     | CS 101                  |
| Yang Aung    | CS 101                  |
| Kyaw Kyaw    | CS 102                  |
| Moe Moe      | CS 102                  |
| Zu Zu        | NULL                    |
| Gone Tint    | NULL                    |
| NULL         | Computer Technology 101 |
+--------------+-------------------------+

UNION ကို OUTER JOIN အတွက်သာ မက အခြား query result ၂ ခု ကိုပေါင်းပြီး ထုတ်ချင်သည့် အခါတွေမှာလည်း သုံးနိုင်ပါတယ်။

Database Basic