Basic SQL Part 4 - JOIN

Programming, SQL

JOIN

ภาพวังอะไรสักอย่างที่จีน จำไม่ได้ละ อาจจะเป็นการไปจีนครั้งแรกและครั้งเดียวในชีวิต

ผมเขียนเกี่ยวกับ SQL ไว้ 6 ตอน คุณสามารถกด Link ด้านล่างเพื่ออ่านที่เกี่ยวกับ SQL ตอนต่างๆได้เลย

ตอนนี้เรามาพูดถึง KEYWORD สำคัญของ SQL ซึ่งนั่นก็คือ JOIN นั่นเอง ซึ่งนั่นทำให้เราสามารถเชื่อมต่อข้อมูลหลายๆตารางมาใช้งานร่วมกันได้

เพิ่มตารางใหม่เข้าไปใน Database

ส่วนต่อจากนี้คือการเพิ่มข้อมูลลง Database โดยจะมีข้อมูลตารางดังต่อไปนี้

  1. PRESIDENT เก็บข้อมูลเกี่ยวกับประธานาธิบดีท่านนั้นว่าอยู่พรรคไหน เกิดปีอะไร ชื่ออะไร ตายเมื่ออายุเท่าไหร่ เป็นคนรัฐไหน
  2. PRES_HOBBY เก็บข้อมูลเกี่ยวงานอดิเรกของประธานาธิบดี
  3. PRES_MARRIAGE เก็บข้อมูลเกี่ยวกับการแต่งงานของประธานาธิบดีว่าแต่งงานกับใคร ตอนอายุเท่าไหร่ มีลูกด้วยกันกี่คน
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
CREATE TABLE IF NOT EXISTS PRES_HOBBY (
    `PRES_NAME` VARCHAR(128) ,
    `HOBBY` VARCHAR(128) 
);
INSERT INTO PRES_HOBBY VALUES
    ('Adams J Q','Billiards'),
    ('Adams J Q','Swimming'),
    ('Adams J Q','Walking'),
    ('Arthur C A','Fishing'),
    ('Cleveland G','Fishing'),
    ('Coolidge C','Fishing'),
    ('Coolidge C','Golf'),
    ('Coolidge C','Indian Clubs'),
    ('Coolidge C','Mechanical Horse'),
    ('Coolidge C','Pitching Hay'),
    ('Eisenhower D D','Bridge'),
    ('Eisenhower D D','Golf'),
    ('Eisenhower D D','Hunting'),
    ('Eisenhower D D','Painting'),
    ('Eisenhower D D','Fishing'),
    ('Garfield J A','Billiards'),
    ('Harding W G','Golf'),
    ('Harding W G','Poker'),
    ('Harding W G','Riding'),
    ('Harrison B','Hunting'),
    ('Hayes R B','Croquet'),
    ('Hayes R B','Driving'),
    ('Hayes R B','Shooting'),
    ('Hoover H C','Fishing'),
    ('Hoover H C','Medicine Ball'),
    ('Jackson A','Riding'),
    ('Jefferson T','Fishing'),
    ('Jefferson T','Riding'),
    ('Johnson L B','Riding'),
    ('Kennedy J F','Sailing'),
    ('Kennedy J F','Swimming'),
    ('Kennedy J F','Touch Football'),
    ('Lincoln A','Walking'),
    ('McKinley W','Riding'),
    ('McKinley W','Swimming'),
    ('McKinley W','Walking'),
    ('Nixon R M','Golf'),
    ('Roosevelt F D','Fishing'),
    ('Roosevelt F D','Sailing'),
    ('Roosevelt F D','Swimming'),
    ('Roosevelt T','Boxing'),
    ('Roosevelt T','Hunting'),
    ('Roosevelt T','Jujitsu'),
    ('Roosevelt T','Riding'),
    ('Roosevelt T','Shooting'),
    ('Roosevelt T','Tennis'),
    ('Roosevelt T','Wrestling'),
    ('Taft W H','Golf'),
    ('Taft W H','Riding'),
    ('Taylor Z','Riding'),
    ('Truman H S','Fishing'),
    ('Truman H S','Poker'),
    ('Truman H S','Walking'),
    ('Van Buren M','Riding'),
    ('Washington G','Fishing'),
    ('Washington G','Riding'),
    ('Wilson W','Golf'),
    ('Wilson W','Riding'),
    ('Wilson W','Walking');
CREATE TABLE IF NOT EXISTS PRESIDENT (
    `PRES_NAME` VARCHAR(128) ,
    `BIRTH_YR` INT,
    `YRS_SERV` INT,
    `DEATH_AGE` INT ,
    `PARTY` VARCHAR(128) ,
    `STATE_BORN` VARCHAR(128) 
);
INSERT INTO PRESIDENT VALUES
    ('Washington G',1732,7,'67','Federalist','Virginia'),
    ('Adams J',1735,4,'90','Federalist','Massachusetts'),
    ('Jefferson T',1743,8,'83','Demo-Rep','Virginia'),
    ('Madison J',1751,8,'85','Demo-Rep','Virginia'),
    ('Monroe J',1758,8,'73','Demo-Rep','Virginia'),
    ('Adams J Q',1767,4,'80','Demo-Rep','Massachusetts'),
    ('Jackson A',1767,8,'78','Democratic','South Carolina'),
    ('Van Buren M',1782,4,'79','Democratic','New York'),
    ('Harrison W H',1773,0,'68','Whig','Virginia'),
    ('Tyler J',1790,3,'71','Whig','Virginia'),
    ('Polk J K',1795,4,'53','Democratic','North Carolina'),
    ('Taylor Z',1784,1,'65','Whig','Virginia'),
    ('Fillmore M',1800,2,'74','Whig','New York'),
    ('Pierce F',1804,4,'64','Democratic','New Hampshire'),
    ('Buchanan J',1791,4,'77','Democratic','Pennsylvania'),
    ('Lincoln A',1809,4,'56','Republican','Kentucky'),
    ('Johnson A',1808,3,'66','Democratic','North Carolina'),
    ('Grant U S',1822,8,'63','Republican','Ohio'),
    ('Hayes R B',1822,4,'70','Republican','Ohio'),
    ('Garfield J A',1831,0,'49','Republican','Ohio'),
    ('Arthur C A',1830,3,'56','Republican','Vermont'),
    ('Cleveland G',1837,8,'71','Democratic','New Jersey'),
    ('Harrison B',1833,4,'67','Republican','Ohio'),
    ('McKinley W',1843,4,'58','Republican','Ohio'),
    ('Roosevelt T',1858,7,'60','Republican','New York'),
    ('Taft W H',1857,4,'72','Republican','Ohio'),
    ('Wilson W',1856,8,'67','Democratic','Virginia'),
    ('Harding W G',1865,2,'57','Republican','Ohio'),
    ('Coolidge C',1872,5,'60','Republican','Vermont'),
    ('Hoover H C',1874,4,'90','Republican','Iowa'),
    ('Roosevelt F D',1882,12,'63','Democratic','New York'),
    ('Truman H S',1884,7,'88','Democratic','Missouri'),
    ('Eisenhower D D',1890,8,'79','Republican','Texas'),
    ('Kennedy J F',1917,2,'46','Democratic','Massachusetts'),
    ('Johnson L B',1908,5,'65','Democratic','Texas'),
    ('Nixon R M',1913,5,NULL,'Republican','California'),
    ('Ford G R',1913,2,NULL,'Republican','Nebraska'),
    ('Carter J E',1924,4,NULL,'Democratic','Georgia'),
    ('Reagan R',1911,3,NULL,'Republican','Illinois');
CREATE TABLE IF NOT EXISTS PRES_MARRIAGE (
    `PRES_NAME` VARCHAR(128) ,
    `SPOUSE_NAME` VARCHAR(128) ,
    `PR_AGE` INT,
    `SP_AGE` INT,
    `NR_CHILDREN` INT,
    `MAR_YEAR` INT
);
INSERT INTO PRES_MARRIAGE VALUES
    ('A','B',NULL,NULL,NULL,NULL),
    ('Washington G','Custis M D',26,27,0,1759),
    ('Adams J','Smith A',28,19,5,1764),
    ('Jefferson T','Skelton M W',28,23,6,1772),
    ('Madison J','Todd D D P',43,26,0,1794),
    ('Monroe J','Kortright E',27,17,3,1786),
    ('Adams J Q','Johnson L C',30,22,4,1797),
    ('Jackson A','Robards R D',26,26,0,1794),
    ('Van Buren M','Hoes H',24,23,4,1807),
    ('Harrison W H','Symmes A T',22,20,10,1795),
    ('Tyler J','Christian L',23,22,8,1813),
    ('Tyler J','Gardiner J',54,24,7,1844),
    ('Polk J K','Childress S',28,20,0,1824),
    ('Taylor Z','Smith M M',25,21,6,1810),
    ('Fillmore M','Powers A',26,27,2,1826),
    ('Fillmore M','McIntosh C C',58,44,0,1858),
    ('Pierce F','Appleton J M',29,28,3,1834),
    ('Lincoln A','Todd M',33,23,4,1842),
    ('Johnson A','McCardle E',18,16,5,1827),
    ('Grant U S','Dent J B',26,22,4,1848),
    ('Hayes R B','Webb L W',30,21,8,1852),
    ('Garfield J A','Rudolph L',26,26,7,1858),
    ('Arthur C A','Herndon E L',29,22,3,1859),
    ('Cleveland G','Folson F',49,21,5,1886),
    ('Harrison B','Scott C L',20,21,2,1853),
    ('Harrison B','Dimmick M S L',62,37,1,1896),
    ('McKinley W','Saxton I',27,23,2,1871),
    ('Roosevelt T','Lee A H',22,19,1,1880),
    ('Roosevelt T','Carow E K',28,25,5,1886),
    ('Taft W H','Herron H',28,25,3,1886),
    ('Wilson W','Axson E L',28,25,3,1885),
    ('Wilson W','Galt E B',58,43,0,1915),
    ('Harding W G','De Wolfe F K',25,30,0,1891),
    ('Coolidge C','Goodhue G A',33,26,2,1905),
    ('Hoover H C','Henry L',24,23,2,1899),
    ('Roosevelt F D','Roosevelt A E',23,20,6,1905),
    ('Truman H S','Wallace E V',35,34,1,1919),
    ('Eisenhower D D','Doud G',25,19,2,1916),
    ('Kennedy J F','Bouvier J L',36,24,3,1953),
    ('Johnson L B','Taylor C A',26,21,2,1934),
    ('Nixon R M','Ryan T C',27,28,2,1940),
    ('Ford G R','Warren E B',35,30,4,1948),
    ('Carter J E','Smith R',21,18,4,1946),
    ('Reagan R','Wyman J',28,25,2,1940),
    ('Reagan R','Davis N',41,28,2,1952);

เริ่มกันเลย Join กันเลย

ขอบอกเลยว่าการ Join นั้นง่ายมากๆ ถ้าสมมุติเราอยาก Join ข้อมูลระหว่างตาราง PRESIDENT กับ PRES_HOBBY เราสามารถเขียนคำสั่ง SQL ได้ดังนี้

1
2
SELECT * 
FROM PRESIDENT, PRES_HOBBY -- ตรง FROM ใส่ตารางที่อยากให้ JOIN กันลงไป

ซึ่งผลลัพธ์ที่ได้จะประมาณนี้

ซึ่งถ้าคุณสังเกตดีๆจะพบว่า PRES_NAME ที่แสดงชื่อมันไม่ตรงกัน ซึ่งมันเริ่มแปลกๆแล้วใช่ไหม ถ้าคุณลอง count(*) ตาราง PRESIDENT , ตาราง PRES_HOBBY และ การ JOIN ระหว่างตาราง PRESIDENT กับ PRES_HOBBY มันจะได้ผลลัพธ์ดังนี้

1
2
3
4
5
6
7
8
SELECT COUNT(*) -- 39
FROM PRESIDENT

SELECT COUNT(*) -- 59
FROM PRES_HOBBY

SELECT COUNT(*) -- 2301
FROM PRESIDENT, PRES_HOBBY

หากสังเกตดีๆจะพบว่า 2301 = 39 x 59 นั่นเอง แปลว่าการ JOIN แบบนี้คือการเอาแต่ละ ROW ของ 2 TABLE มาเชื่อมกันทั้งหมดจึงเกิดทุกความเป็นไปได้ขึ้นมา

อ้าวแล้วถ้าอยาก JOIN เฉพาะข้อมูลที่ถูกต้องล่ะทำยังไง คำตอบไม่ยากครับ เราต้องมีเงื่อนไขการ JOIN ครับ ซึ่งมันไม่ยากเลย หากเราดูข้อมูลทั้ง 2 TABLE จะพบว่า COLUMN : PRES_NAME เนี่ยแหละเป็นตัวบอกว่าต้องเอา ROW นี้ของ Table PRESIDENT ควรไปต่อกับ ROW นี้ของ Table PRES_HOBBY ดังนั้นเราจึงสามารถเพิ่มเงื่อนไขเข้าไปตรง WHERE แบบนี้

1
2
3
4
5
6
7
SELECT * 
FROM PRESIDENT, PRES_HOBBY 
WHERE PRESIDENT.PRES_NAME = PRES_HOBBY.PRES_NAME

-- ตรง WHERE นั้นปกติเราจะใส่แค่ชื่อ COLUMN แต่ทีนี้มันหลาย TABLE ดังนั้นเราจึง
-- ระบุ TABLE เข้าไปด้วยว่า COLUMN จาก TABLE ไหน จากตัวอย่างจะเป็น
-- PRESIDENT.PRES_NAME ก็คือ COLUMN : PRES_NAME จาก TABLE PRESIDENT

คราวนี้จะมีคำถามว่าอ้าวแล้วจะรู้ได้ไงว่าจะเอา COLUMN ไหนไป WHERE กับ COLUMN ไหนนั้น จริงๆคนที่เก็บข้อมูลเขาจะทำสิ่งที่เรียกว่า Relation ไว้ใน Database บอกว่า Table นี้กับ Table นี้มี Column ไหนที่ใช้ JOIN กันได้ หรือถ้าเขาไม่เขียนใน Database เขาจะเขียนไว้ใน Manual ของการเก็บข้อมูลนี้ว่า JOIN กันด้วย COLUMN ไหนกับ COLUMN ไหน หรือ แบบแย่สุดคือเขาจะไม่มี MANUAL เราต้องไปดูเองแต่โดยปกติเขาจะตั้งชื่อ COLUMN ที่ JOIN กันได้เป็นชื่อเดียวกัน เพื่อให้เราพอเดาได้ว่า COLUMN นี้กับ COLUMN นี้ JOIN กันได้

ต่อมา ถ้าอยากรู้ว่าเพิ่มว่า ประธานาธิบดีคนไหนชอบตีกอล์ฟและอยู่พรรค Republican บ้างก็สามารถหาได้ด้วยคำสั่ง

1
2
3
SELECT * 
FROM PRESIDENT, PRES_HOBBY 
WHERE PRESIDENT.PRES_NAME = PRES_HOBBY.PRES_NAME AND PRES_HOBBY.HOBBY = 'Golf' AND PRESIDENT.PARTY = 'Republican'

แต่พอเรามาดูจะเห็นว่า WHERE มันเริ่มเยอะและเริ่มงงว่า WHERE ไหนใช้เพื่อการ JOIN WHERE ไหนใช้เพื่อเป็นเงื่อนไขในการค้นหา เพื่อให้ง่ายแก่การเขียนจึงมี KEYWORD เพื่อใช้ง่ายต่อการ JOIN ขึ้นมานั่นคือ JOIN และ ON โดยจาก SQL ตัวที่แล้วเราสามารถเปลี่ยนเป็น SQL ดังนี้

1
2
3
4
5
SELECT *
FROM PRESIDENT
INNER JOIN PRES_HOBBY -- JOIN กันด้วยรูปแบบ INNER เดี๋ยว INNER จะอธิบายต่อจากนี้ แต่ให้เข้าใจว่าการ JOIN มีหลายแบบ
ON PRESIDENT.PRES_NAME = PRES_HOBBY.PRES_NAME -- อันนี้เงื่อนไขการ JOIN ว่า JOIN ด้วยเงื่อนไขอะไร
WHERE PRES_HOBBY.HOBBY = 'Golf' AND PRESIDENT.PARTY = 'Republican'

คำถามต่อมาถ้า JOIN มากกว่า 2 ตารางล่ะ ทำได้ไหม คำตอบคือได้ครับ เดี๋ยวเราจะ JOIN ทั้ง 3 table เลยคือ PRESIDENT, PRES_MARRIAGE, PRES_HOBBY

1
2
3
4
5
6
SELECT *
FROM PRESIDENT
INNER JOIN PRES_HOBBY
ON PRESIDENT.PRES_NAME = PRES_HOBBY.PRES_NAME
INNER JOIN PRES_MARRIAGE
ON PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME

ชนิดการ JOIN

คราวนี้เรามาดูชนิดของการ JOIN กันครับ โดยเราจะมาดูวิธีการทำงานของมันกัน โดยเราจะ JOIN 2 Table เข้าด้วยกันคือ PRESIDENT กับ PRES_MARRIAGE โดยใช้เงื่อนไขการ JOIN คือ PRESNAME

INNER JOIN

INNER JOIN ก็คือเอาผลลัพธ์เฉพาะที่ JOIN กันได้เท่านั้น

1
2
3
4
SELECT *
FROM PRESIDENT
INNER JOIN PRES_MARRIAGE
ON PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME

การทำงานก็จะดังภาพคือไล่ทีละ ROW ของตาราง PRESIDENT ไปหาคู่ JOIN ที่ ROW ในตาราง PRES_MARRIAGE ถ้าเจอก็เอาข้อมูลมาต่อกันเป็นผลลัพธ์ด้านล่างจะเห็นว่าจะไม่มีผลลัพธ์ของ ROW : “Buchanan J” จากตาราง PRESIDENT

LEFT JOIN

LEFT JOIN เอาทางซ้ายหมด กับ ที่ JOIN ติด

เอาตารางทางซ้ายไปไล่ JOIN ถ้า JOIN ติดก็เอาผลลัพธ์ที่ JOIN ติด ถ้า JOIN ไม่ติดเอาข้อมูลจากตารางที่อยู่ทางซ้ายมาส่วนข้อมูลที่ JOIN ไม่ติดให้ใส่ค่า NULL ไปแทน ตัวอย่างจะเป็นดังภาพ

1
2
3
4
SELECT *
FROM PRESIDENT
LEFT JOIN PRES_MARRIAGE
ON PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME

จะเห็นว่า ROW : “Buchanan J” จะโผล่มาในผลลัพธ์ด้วยแต่ข้อมูลเป็นมาจากตาราง PRES_MARIAGE เป็นค่า NULL หมด

RIGHT JOIN (ตัว WEB ไม่ Support นะครับ แต่จริงๆสลับไปใช้ LEFT JOIN ก็ได้)

RIGHT JOIN เอาทางขวาทั้งหมด กับ ที่ JOIN ติด

อันนี้คล้ายๆกับ LEFT JOIN เลย แต่เปลี่ยนเป็นเอาทางขวาหมด อันไหนที่ JOIN ไม่ติดที่อยู่กับตารางทางซ้ายจะมีค่าเป็น NULL

1
2
3
4
SELECT *
FROM PRESIDENT
RIGHT JOIN PRES_MARRIAGE
ON PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME

จะเห็น ROW ที่ JOIN ไม่ติด ข้อมูลตารางที่อยู่ด้านซ้ายจะมีค่าเป็น NULL

FULL JOIN (ตัว WEB ไม่ Support นะครับ)

1
2
3
4
SELECT *
FROM PRESIDENT
FULL JOIN PRES_MARRIAGE
ON PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME

FULL JOIN เอาทั้งซ้าย ทั้งขวา และที่ JOIN ติด

อันนี้คือเอาทั้งหมดเลยสมชื่อ FULL ผลลัพธ์ก็จะได้ดังภาพ

สรุปเกี่ยวกับ Type การ JOIN

สำหรับเรื่อง Type การ JOIN อาจจะมีหลายอันแต่จริงๆเวลาทำงานผมเคยใช้อยู่แค่ 2 TYPE คือ INNER JOIN กับ LEFT JOIN เท่านั้น RIGHT JOIN นี่ไม่เคยคิดจะใช้เพราะว่ามันแค่สลับตารางขึ้นก่อนก็เป็น LEFT JOIN ละ เลยไม่มีเหตุผลที่จะต้องใช้ ส่วน FULL JOIN นี่ยิ่งแล้วใหญ่ไม่เคยมีงานไหนที่ต้องการผลลัพธ์แบบนั้นเลย

แล้วเราจะใช้ LEFT JOIN ตอนไหนล่ะ จริงๆมันจะมีโจทย์บางอย่างที่ต้องการข้อมูลของตารางซ้ายทั้งหมด ส่วนตารางขวาเป็นข้อมูลประกอบ ไม่มีก็ให้แสดง NULL ได้ เช่น แสดงข้อมูลประธานาธิบดี และ ภรรยาทั้งหมดก็จะได้ SQL ประมาณนี้

1
2
3
4
SELECT PRESIDENT.PRESIDENT, PRES_MARRIAGE.SPOUSE_NAME
FROM PRESIDENT
LEFT JOIN PRES_MARRIAGE
ON PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME

จบเรื่อง JOIN

สำหรับตอนนี้เราก็ได้เรียนรู้เกี่ยวกับการ JOIN ระหว่างตารางกันไปแล้วนะครับ จะเห็นว่าไม่ยากเลย ถ้าเข้าใจ Concept ตรงนี้จะ JOIN กี่ตารางก็ทำได้เลยครับ สำหรับตอนนี้ขอจบเพียงเท่านี้ สวัสดีครับ

เพลงประกอบการเขียน Blog

เพลงที่โคตรเก่าแต่เนื้อเพลงนี่อย่างโดน ตอนฟังครั้งแรก นี่แบบอะไรวะ พอมีประสบการณ์ชอบคนมีแฟนแล้วนี่เข้าใจเพลงนี้เลย คือแบบ “หมาคาบไปแดก” จริงๆ คือทั้งเพลงคือความคิดของเราเลยแบบ ฝันให้เขาเลิกกัน ให้ผู้ชายนอกใจ เขาจะได้โสด แต่แม่งแค่ฝันไง เขารักกันจะตายห่า จะแต่งงานกันอยู่ละ