แบบสอบถามย่อยเพื่อแก้แบบสอบถาม

แบบสอบถามย่อยถูกกำหนดให้เป็นแบบสอบถามภายในแบบสอบถามได้ดีที่สุด แบบสอบถามย่อยช่วยให้คุณสามารถเขียนคิวรีที่เลือกแถวข้อมูลสำหรับเกณฑ์ที่พัฒนาขึ้นจริงในขณะที่คิวรีกำลังดำเนินการในขณะรัน อย่างเป็นทางการมากขึ้นคือการใช้คำสั่ง 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);