SQL JOIN
เป็นคำสั่งที่ใช้สำหรับการระบุเงื่อนไขการเลือกข้อมูลในตาราง (Table) โดยเงื่อนไขการ JOIN จะกระทำเมื่อมีข้อมูลตั้งแต่ 2 Table ขึ้นไป โดยข้อมูลเหล่านั้นเป็นข้อมูลที่มีความสัมพันธ์และเชื่อมโยงกับข้อมูลหลัก
Database : MySQL,Microsoft Access,SQL Server,Oracle
Syntax
SELECT [Table-Name1].Column1, [Table-Name2].Column1,... FROM [Table-Name1],[Table-Name2]
WHERE [Table-Name1].Column = [Table-Name2].Column
WHERE [Table-Name1].Column = [Table-Name2].Column
Table : customer
|
CustomerID
|
Name
|
Email
|
CountryCode
|
Budget
|
Used
|
|---|---|---|---|---|---|
|
C001
|
Win Weerachai | win.weerachai@thaicreate.com |
TH
|
1000000 | 600000 |
|
C002
|
John Smith | john.smith@thaicreate.com |
EN
|
2000000 | 800000 |
|
C003
|
Jame Born | jame.born@thaicreate.com |
US
|
3000000 | 600000 |
|
C004
|
Chalee Angel | chalee.angel@thaicreate.com |
US
|
4000000 | 100000 |
Table : audit
|
AuditID
|
CustomerID
|
Date
|
Used
|
|---|---|---|---|
|
1
|
C001
|
2008-07-01
|
100000 |
|
2
|
C001
|
2008-07-05
|
200000 |
|
3
|
C001
|
2008-07-10
|
300000 |
|
4
|
C002
|
2008-07-02
|
400000 |
|
5
|
C002
|
2008-07-07
|
100000 |
|
6
|
C002
|
2008-07-15
|
300000 |
|
7
|
C003
|
2008-07-20
|
400000 |
|
8
|
C003
|
2008-07-25
|
200000 |
|
9
|
C004
|
2008-07-04
|
100000 |
Sample1 การเลือกข้อมูลแบบเชื่อมตาราง customer และ audit
SELECT customer.*,audit.* FROM customer,audit
WHERE customer.CustomerID = audit.CustomerID
WHERE customer.CustomerID = audit.CustomerID
Output
|
CustomerID
|
Name
|
Email
|
CountryCode
|
Budget
|
Used
|
AuditID
|
CustomerID
|
Date
|
Used
|
|---|---|---|---|---|---|---|---|---|---|
|
C001
|
Win Weerachai | win.weerachai@thaicreate.com |
TH
|
1000000 | 600000 |
1
|
C001
|
2008-08-01
|
100000 |
|
C001
|
Win Weerachai | win.weerachai@thaicreate.com |
TH
|
1000000 | 600000 |
2
|
C001
|
2008-08-05
|
200000 |
|
C001
|
Win Weerachai | win.weerachai@thaicreate.com |
TH
|
1000000 | 600000 |
3
|
C001
|
2008-08-10
|
300000 |
|
C002
|
John Smith | john.smith@thaicreate.com |
EN
|
2000000 | 800000 |
4
|
C002
|
2008-08-02
|
400000 |
|
C002
|
John Smith | john.smith@thaicreate.com |
EN
|
2000000 | 800000 |
5
|
C002
|
2008-08-07
|
100000 |
|
C002
|
John Smith | john.smith@thaicreate.com |
EN
|
2000000 | 800000 |
6
|
C002
|
2008-08-15
|
300000 |
|
C003
|
Jame Born | jame.smith@thaicreate.com |
US
|
3000000 | 600000 |
7
|
C003
|
2008-08-20
|
400000 |
|
C003
|
Jame Born | jame.smith@thaicreate.com |
US
|
3000000 | 600000 |
8
|
C003
|
2008-08-25
|
200000 |
|
C004
|
Chalee Angel | chalee.angel@thaicreate.com |
US
|
4000000 | 100000 |
9
|
C004
|
2008-07-04
|
100000 |
Sample2 การเลือกข้อมูลแบบเชื่อมตาราง customer และ audit และ CustomerID = C001
SELECT customer.*,audit.* FROM customer,audit
WHERE customer.CustomerID = audit.CustomerID
AND customer.CustomerID = 'C001'
WHERE customer.CustomerID = audit.CustomerID
AND customer.CustomerID = 'C001'
Output
|
CustomerID
|
Name
|
Email
|
CountryCode
|
Budget
|
Used
|
AuditID
|
CustomerID
|
Date
|
Used
|
|---|---|---|---|---|---|---|---|---|---|
|
C001
|
Win Weerachai | win.weerachai@thaicreate.com |
TH
|
1000000 | 600000 |
1
|
C001
|
2008-08-01
|
100000 |
|
C001
|
Win Weerachai | win.weerachai@thaicreate.com |
TH
|
1000000 | 600000 |
2
|
C001
|
2008-08-05
|
200000 |
|
C001
|
Win Weerachai | win.weerachai@thaicreate.com |
TH
|
1000000 | 600000 |
3
|
C001
|
2008-08-10
|
300000 |
Sample3 การเลือกข้อมูลแบบเชื่อมตาราง customer และ audit และ CustomerID = C001 และแสดงผลเฉพาะตาราง audit
SELECT audit.* FROM customer,audit
WHERE customer.CustomerID = audit.CustomerID
AND customer.CustomerID = 'C001'
WHERE customer.CustomerID = audit.CustomerID
AND customer.CustomerID = 'C001'
Output
|
AuditID
|
CustomerID
|
Date
|
Used
|
|---|---|---|---|
|
1
|
C001
|
2008-08-01
|
100000 |
|
2
|
C001
|
2008-08-05
|
200000 |
|
3
|
C001
|
2008-08-10
|
300000 |
แจ่มๆ