บันทึกการใช้งาน SQL 103 สำหรับนักวิทยาศาสตร์ข้อมูล Data Science - sprint 02
SQL - 103
join with select multiple table advance
select * from artists, albums
WHERE artists.artistid = albums.artistid;
สามารถตั้งชื่อได้
select * from artists A, albums B
WHERE A.artistid = B.artistid;
select
A.ArtistId,
A.name artistsName ,
B.title albumsName
from artists A, albums B
WHERE A.artistid = B.artistid AND A.name Like 'C%';
convert where to Inner join
วิธีการ join clause จะเทียบเท่ากับ where clause ที่ join ในหัวข้อที่แล้ว
select
A.ArtistId,
A.name artistsName ,
B.title albumsName
from artists A inner JOIN albums B
ON A.artistid = B.artistid
WHERE A.name Like 'C%';
คราวนี้เราต้องการ join เพิ่มอีก 1 table
select
A.ArtistId,
A.name artistsName ,
B.title albumsName ,
C.name trackName
from artists A
inner JOIN albums B ON A.artistid = B.artistid
inner join tracks C ON B.AlbumId = C.albumid
WHERE A.name Like 'Aerosmith';
Review Join Types
image by datarockie bootcamp
Inner join
เมื่อค่าที่ต้องการหามีตรงกันทั้ง 2 table ก็จะมาแสดงที่ result เช่น
1,2,5 ที่ตรงกันทั้ง 2 table
image by datarockie bootcamp
SELECT
A.Title ,
B.Name
from albums A
INNER join artists B ON A.ArtistId = B.ArtistId;
Left join
จะมี table ซ้ายเป็นตัวตั้ง แล้วดูว่า table ขวาตรงกันหรือไม่
กรณีที่ table ทางด้านขวา ไม่มีหรือไม่ตรงกับ table ขวา มันจะมีค่าเป็น Null โดยค่าใน table ซ้ายก็ยังดึงมาเหมือนเดิม
image by datarockie bootcamp
SELECT
A.Name ,
B.Title
from artists A
LEFT join albums B ON A.ArtistId = B.ArtistId;
Full join
จะ merge กันทั้ง 2 table เลยโดยที่ไม่สนว่าจะ match หรือไม่ถ้าตรงกันก็จะมี ค่าทั้ง 2 coumn แต่ถ้าข้างไหนไม่มีก็จะเป็นค่า Null
image by datarockie bootcamp
Cross Join (Cartesian)
image by datarockie bootcamp
มันคือการ คูณกันของ 2 table โดยที่ table ซ้ายเป็นตัวตั้งแล้ว คูณเข้าไปที่ table ทางด้านขวาทุก record
ยกตัวอย่างการสร้าง table ด้วย cross join ชุดไพ่ขึ้นมา
CREATE TABLE ranks (rank TEXT);
CREATE TABLE suits (suit TEXT);
INSERT into ranks
VALUES ('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10'),('J'),('Q'),('K'),('A');
INSERT INTO suits
VALUES ('Clubs'),('Diamonds'),('Hearts'),('Spades');
SELECT * from ranks;
SELECT * from suits;
แล้ว เรียก table
SELECT * from ranks , suits;
จะเห็นว่ามันไม่เรียง ก็ใส่ order by เข้าไป
SELECT * from ranks , suits order by 2;
หรือ ได้ค่าเหมือนกัน
SELECT * from ranks CROSS JOIN suits order by 2;
self join
image by datarockie bootcamp
usecase การ join employee
สมมติว่ามีพนักงานอยู่ 4 คนแล้วเรา จะส่ง report หาเจ้านายคนไหนบ้าง ถ้าดูจากตารางก็จะเห็นว่า 2,3,4 ส่งหา 1
ถ้าอยากจะดึงชื่อของพนักงานและชื่อของเจ้านาย เทคนิคนี้จะใช้ self join
create table employee (
id INT,
name TEXT,
level TEXT,
manager_id
);
insert into employee VALUES
(1,'David', 'CEO', NULL),
(1,'John', 'SVP', 1),
(1,'Mary', 'VP', 2),
(1,'Adam', 'VP', 2),
(1,'Scott', 'MANAGER', 3),
(1,'Louise', 'MANAGER', 3),
(1,'Kevin', 'MANAGER', 4),
(1,'Takesin', 'MANAGER', 4),
(1,'Joe', 'AM', 6),
(1,'Anna', 'AM', 7);
SELECT * from employee;
คราวนี้จะมาทำ join
SELECT * from employee e1, employee e2
WHERE e1.manager_id = e2.id
แต่จะเห็นว่าจะมีทุก column ที่ join เราจะเลือกมาเฉพาะที่ต้องการ
SELECT
e1.name AS staff,
e1.level as staff_level,
e2.name as manager,
e2.level as manager_level
from employee e1, employee e2
WHERE e1.manager_id = e2.id
คราวนี้เราก็จะเห็นว่าแล้ว ใครส่ง report หาใคร แต่เพื่อให้ table เข้าใจง่ายมากขึ้น เราจะเพิ่มไปอีก 1 คอลัมน์
SELECT
e1.name AS staff,
e1.level as staff_level,
e2.name as manager,
e2.level as manager_level,
e1.name || ' report to ' || e2.name as comment
from employee e1, employee e2
WHERE e1.manager_id = e2.id
Intersect & Except
- intersect คือ จะส่งค่าที่ตรงกันกลับไป คล้ายกับ inner join แต่ต่างกันที่จะไม่ดึงข้อมูลจาก table ทางด้านขวา จะเอาข้อมูลเฉพาะ table ซ้ายมือออกไปเท่านั้น
- except คือ จะส่งค่าที่อยู่ใน table ซ้ายมือแต่ไม่ได้อยู่ใน table ขวามือ
image by datarockie bootcamp
สมมติว่าเราอยากรู้ว่า หนังสือเล่มไหนมันตรงกันระหว่าง table book_shop กับ table favorite_book บ้าง
select id from book_shop
INTERSECT
SELECT id from favorite_book;
จะไม่สามารถใช้ * ได้
ถ้าอยากรู้ว่ามีหนังสือเล่มไหนที่ไม่ได้อยู่ favorite_book ให้ใช้ except
select id from book_shop
EXCEPT
SELECT id from favorite_book;
Union & Union All
เป็นการรวม table 2 table เข้าด้วยกัน
- ในกรณีที่ table 2 table มีค่าที่ตรงกัน ถ้าใช้คำว่า UNION มันจะ remove duplicates row ทิ้งไป
- ในกรณีที่ ต้องการเก็บ row duplicates เอาไว้ให้ใช้คำสั่ง UNION ALL
image by datarockie bootcamp
drop table if exists book_shop;
create table book_shop (
id INT,
name TEXT,
release_year INT
);
insert into book_shop VALUES
(6, 'Business Data Science', 2020),
(7, 'Subliminal', 2019);
drop table if exists book_shop_new;
create table book_shop_new (
id INT,
name TEXT,
release_year INT
);
insert into book_shop_new VALUES
(6, 'Business Data Science', 2020),
(7, 'Subliminal', 2019),
(8, 'Good Strategy', 2015);
จะเห็นว่ามีค่าซ้ำแต่ไม่เอามาด้วย
SELECT * from book_shop
union
select * from book_shop_new
แต่ถ้าลองเก็บ ทุก row
SELECT * from book_shop
union ALL
select * from book_shop_new
Subqueries
เป็นเทคนิคการเขียน select ซ้อน select
image by datarockie bootcamp
เวลามันจะ run select มันจะเริ่มที่ inner query ก่อนแล้วค่อยไล่ run select outer ออกมาเรื่อยๆ
เราสามารถเรียกด้วย order by แบบนี้ได้
select * from tracks order by milliseconds DESC LIMIT 1;
แต่ถ้าเราไม่ต้องการเขียนแบบนี้ ก็เขียนแบบนี้ได้ แค่ค่อนข้างเสียเวลา
SELECT max(milliseconds) from tracks;
SELECT * from tracks WHERE milliseconds = 5286953;
จริงๆเราสามารถ ใส่ select ซ้อนกันได้เลย อยู่ในเงื่อนไข where
SELECT * from tracks WHERE milliseconds = (SELECT max(milliseconds) from tracks);
อีกตัวอย่าง ถ้าเราต้องการ ดูลูกค้าที่อยู่ USA และต้องการจะดึงเฉพาะ ชื่อ นามสกุล และ ประเทศ
SELECT firstname, lastname, country FROM
(SELECT * from customers
WHERE country = 'USA');
คอร์สนี้ดีมากกกก (ไก่ ล้านตัว) ใครอ่านจบ แนะนำว่าให้ไปสมัครเรียน ติดตามได้ที่ link ด้านล่างนี้เลย