แบบสอบถามย่อยเพื่อแก้แบบสอบถาม
แบบสอบถามย่อยถูกกำหนดให้เป็นแบบสอบถามภายในแบบสอบถามได้ดีที่สุด แบบสอบถามย่อยช่วยให้คุณสามารถเขียนคิวรีที่เลือกแถวข้อมูลสำหรับเกณฑ์ที่พัฒนาขึ้นจริงในขณะที่คิวรีกำลังดำเนินการในขณะรัน อย่างเป็นทางการมากขึ้นคือการใช้คำสั่ง SELECT ภายในหนึ่งในอนุประโยคของคำสั่ง SELECT อื่น ในความเป็นจริงแบบสอบถามย่อยสามารถอยู่ในแบบสอบถามย่อยอื่นซึ่งอยู่ในแบบสอบถามย่อยอื่นและอื่น ๆ นอกจากนี้ยังสามารถซ้อนคิวรีย่อยภายในคำสั่ง INSERT, UPDATE และ DELETE แบบสอบถามย่อยต้องอยู่ในวงเล็บ
แบบสอบถามย่อยสามารถใช้ที่ใดก็ได้ที่อนุญาตให้นิพจน์ได้โดยให้ค่านี้ส่งคืนค่าเดียว ซึ่งหมายความว่าคิวรีย่อยที่ส่งคืนค่าเดียวสามารถแสดงรายการเป็นอ็อบเจ็กต์ในรายการคำสั่ง FROM สิ่งนี้เรียกว่ามุมมองแบบอินไลน์เนื่องจากเมื่อใช้เคียวรีย่อยเป็นส่วนหนึ่งของคำสั่ง FROM จะถือว่าเป็นตารางเสมือนหรือมุมมอง สามารถวางข้อความค้นหาย่อยใน FROM clause, WHERE clause หรือ HAVING clause ของเคียวรีหลัก
Oracle อนุญาตให้มีการซ้อนสูงสุด 255 ระดับแบบสอบถามย่อยในส่วนคำสั่ง WHERE ไม่มีขีด จำกัด สำหรับการซ้อนเคียวรีย่อยที่แสดงในส่วนคำสั่ง FROM ในทางปฏิบัติขีด จำกัด 255 ระดับไม่ได้เป็นขีด จำกัด เลยเพราะเป็นเรื่องยากที่จะพบกับเคียวรีย่อยที่ซ้อนกันเกินสามหรือสี่ระดับ
คำสั่งย่อย SELECT คล้ายกับคำสั่ง SELECT ที่ใช้ในการเริ่มต้นแบบสอบถามปกติหรือภายนอกไวยากรณ์ที่สมบูรณ์ของแบบสอบถามย่อยคือ:
( SELECT [DISTINCT] subquery_select_parameter
FROM {table_name | view_name}
{table_name | view_name} ...
[WHERE search_conditions]
[GROUP BY column_name [,column_name ] ...]
[HAVING search_conditions] )
ประเภทของแบบสอบถามย่อย
Single Row Sub Query: แบบสอบถามย่อยซึ่งส่งคืนเอาต์พุตแถวเดียว เครื่องหมายการใช้ตัวดำเนินการเปรียบเทียบแถวเดียวเมื่อใช้ในเงื่อนไข WHERE
Multiple row sub query: แบบสอบถามย่อยส่งคืนเอาต์พุตหลายแถว พวกเขาใช้ประโยชน์จากตัวดำเนินการเปรียบเทียบหลายแถวเช่น IN, ANY, ALL อาจมีการสืบค้นย่อยที่ส่งคืนหลายคอลัมน์ด้วย
Correlated Sub Query: เคียวรีย่อยที่สัมพันธ์กันขึ้นอยู่กับข้อมูลที่จัดเตรียมโดยคิวรีภายนอกเคียวรีย่อยประเภทนี้ยังรวมเคียวรีย่อยที่ใช้ตัวดำเนินการ EXISTS เพื่อทดสอบการมีอยู่ของแถวข้อมูลที่ตรงตามเกณฑ์ที่ระบุ
แบบสอบถามย่อยแถวเดียว
แบบสอบถามย่อยแถวเดียวใช้เมื่อผลลัพธ์ของแบบสอบถามภายนอกยึดตามค่าเดียวที่ไม่รู้จัก แม้ว่าประเภทการสืบค้นนี้จะเรียกอย่างเป็นทางการว่า "single-row" แต่ชื่อก็หมายความว่าแบบสอบถามส่งกลับหลายคอลัมน์ แต่มีผลลัพธ์เพียงแถวเดียว อย่างไรก็ตามแบบสอบถามย่อยแถวเดียวสามารถส่งคืนผลลัพธ์ได้เพียงแถวเดียวซึ่งประกอบด้วยคอลัมน์เดียวไปยังแบบสอบถามภายนอก
ในแบบสอบถาม SELECT ด้านล่าง SQL ภายในจะส่งกลับเพียงแถวเดียวคือเงินเดือนขั้นต่ำสำหรับ บริษัท ในทางกลับกันจะใช้ค่านี้เพื่อเปรียบเทียบเงินเดือนของพนักงานทุกคนและแสดงเฉพาะที่ซึ่งเงินเดือนเท่ากับเงินเดือนขั้นต่ำ
SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary)
FROM employees);
HAVING clause จะใช้เมื่อต้องการ จำกัด ผลลัพธ์กลุ่มของคิวรีตามเงื่อนไขบางประการ หากต้องเปรียบเทียบผลลัพธ์ของเคียวรีย่อยกับฟังก์ชันกลุ่มคุณต้องซ้อนคิวรีภายในในส่วนคำสั่ง HAVING ของคิวรีภายนอก
SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary) < (SELECT AVG (salary) FROM employees)
แบบสอบถามย่อยหลายแถว
เคียวรีย่อยหลายแถวคือคิวรีแบบซ้อนกันที่สามารถส่งคืนผลลัพธ์มากกว่าหนึ่งแถวไปยังคิวรีพาเรนต์ แบบสอบถามย่อยหลายแถวถูกใช้มากที่สุดใน WHERE และ HAVING clauses เนื่องจากส่งคืนหลายแถวจึงต้องจัดการโดยชุดตัวดำเนินการเปรียบเทียบ (IN, ALL, ANY) ในขณะที่ตัวดำเนินการ IN มีความหมายเดียวกันตามที่กล่าวไว้ในบทก่อนหน้าตัวดำเนินการใด ๆ จะเปรียบเทียบค่าที่ระบุกับแต่ละค่าที่ส่งคืนโดยแบบสอบถามย่อยในขณะที่ ALL เปรียบเทียบค่ากับทุกค่าที่คิวรีย่อยส่งคืน
แบบสอบถามด้านล่างแสดงข้อผิดพลาดเมื่อแบบสอบถามย่อยแถวเดียวส่งกลับหลายแถว
SELECT first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE LOCATION_ID = 100)
department_id = (select
*
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row
การใช้ตัวดำเนินการหลายแถว
[> ALL] มากกว่าค่าสูงสุดที่ส่งกลับโดยการสืบค้นย่อย
[<ALL] น้อยกว่าค่าต่ำสุดที่เคียวรีย่อยส่งคืน
[<ANY] น้อยกว่าค่าสูงสุดที่ส่งกลับโดยการสืบค้นย่อย
[> ANY] มากกว่าค่าต่ำสุดที่ส่งกลับโดยการสืบค้นย่อย
[= ANY] เท่ากับค่าใด ๆ ที่ส่งกลับโดยการสืบค้นย่อย (เช่นเดียวกับ IN)
ด้านบน SQL สามารถเขียนใหม่ได้โดยใช้ตัวดำเนินการ IN ดังต่อไปนี้
SELECT first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE LOCATION_ID = 100)
หมายเหตุในแบบสอบถามข้างต้น IN ตรงกับรหัสแผนกที่ส่งคืนจากแบบสอบถามย่อยเปรียบเทียบกับสิ่งนั้นในแบบสอบถามหลักและส่งกลับชื่อพนักงานที่ตรงตามเงื่อนไข
การเข้าร่วมจะเป็นทางออกที่ดีกว่าสำหรับแบบสอบถามข้างต้น แต่เพื่อวัตถุประสงค์ในการแสดงภาพประกอบจะมีการใช้แบบสอบถามย่อยในนั้น
แบบสอบถามย่อยที่สัมพันธ์กัน
เมื่อเทียบกับแบบสอบถามย่อยทั่วไปโดยที่แบบสอบถามภายนอกขึ้นอยู่กับค่าที่ให้ไว้โดยแบบสอบถามภายในแบบสอบถามย่อยที่สัมพันธ์กันคือคำค้นหาภายในที่ขึ้นอยู่กับค่าที่ให้ไว้โดยแบบสอบถามภายนอก ซึ่งหมายความว่าในเคียวรีย่อยที่สัมพันธ์กันคิวรีภายในจะถูกเรียกใช้งานซ้ำ ๆ ครั้งเดียวสำหรับแต่ละแถวที่คิวรีภายนอกอาจเลือก
การสืบค้นย่อยที่สัมพันธ์กันสามารถสร้างตารางผลลัพธ์ที่ตอบคำถามการจัดการที่ซับซ้อนได้
พิจารณาคำค้นหา SELECT ด้านล่าง ไม่เหมือนกับเคียวรีย่อยที่พิจารณาก่อนหน้านี้คิวรีย่อยในคำสั่ง SELECT นี้ไม่สามารถแก้ไขได้โดยไม่ขึ้นกับแบบสอบถามหลัก สังเกตว่าแบบสอบถามภายนอกระบุว่ามีการเลือกแถวจากตารางพนักงานที่มีชื่อนามแฝงเป็น e1 แบบสอบถามภายในเปรียบเทียบคอลัมน์หมายเลขแผนกพนักงาน (DepartmentNumber) ของตารางพนักงานที่มีนามแฝง e2 กับคอลัมน์เดียวกันสำหรับชื่อตารางนามแฝง e1
SELECT EMPLOYEE_ID, salary, department_id
FROM employees E
WHERE salary > (SELECT AVG(salary)
FROM EMP T
WHERE E.department_id = T.department_id)
แบบสอบถามย่อยหลายคอลัมน์
แบบสอบถามย่อยหลายคอลัมน์ส่งคืนคอลัมน์มากกว่าหนึ่งคอลัมน์ไปยังแบบสอบถามภายนอกและสามารถแสดงรายการในส่วนคำสั่ง FROM, WHERE หรือ HAVING ของแบบสอบถามภายนอกได้ ตัวอย่างเช่นข้อความค้นหาด้านล่างแสดงรายละเอียดประวัติของพนักงานสำหรับผู้ที่มีเงินเดือนปัจจุบันอยู่ในช่วง 1,000 และ 2000 และทำงานในแผนก 10 หรือ 20
SELECT first_name, job_id, salary
FROM emp_history
WHERE (salary, department_id) in (SELECT salary, department_id
FROM employees
WHERE salary BETWEEN 1000 and 2000
AND department_id BETWEEN 10 and 20)
ORDER BY first_name;
เมื่อมีการใช้เคียวรีย่อยหลายคอลัมน์ในส่วนคำสั่ง FROM ของคิวรีภายนอกจะสร้างตารางชั่วคราวที่สามารถอ้างอิงได้โดยส่วนคำสั่งอื่นของคิวรีภายนอก ตารางชั่วคราวนี้เรียกอย่างเป็นทางการว่ามุมมองแบบอินไลน์ ผลลัพธ์ของแบบสอบถามย่อยจะได้รับการปฏิบัติเหมือนตารางอื่น ๆ ในส่วนคำสั่ง FROM หากตารางชั่วคราวมีข้อมูลที่จัดกลุ่มชุดย่อยที่จัดกลุ่มจะถือว่าเป็นแถวข้อมูลที่แยกจากกันในตาราง พิจารณาคำสั่ง FROM ในแบบสอบถามด้านล่าง มุมมองแบบอินไลน์ที่สร้างขึ้นโดยการสืบค้นย่อยเป็นแหล่งข้อมูลสำหรับแบบสอบถามหลัก
SELECT *
FROM (SELECT salary, department_id
FROM employees
WHERE salary BETWEEN 1000 and 2000);