(ORA-00022) - Subquery & Correlated Subquery?

Subquery và các loại của Subquery

* Normal Subquery: Truy vấn bên trong thực hiện 1 lần duy nhất. Truy vấn bên trong sẽ thực thi đầu tiên và kết quả này được dùng cho lệnh truy vấn bên ngoài. Lệnh truy vấn bên trong không phụ thuộc vào lệnh truy vấn bên ngoài.

Các loại Subquery:
- Single row Subquery (Kết quả chỉ 1 hàng): Truy vấn bên trong chỉ trả về 1 hàng. Dùng các toán tử so sánh một hàng (=, >, ...) để so sánh với truy vấn ngoài.

SQL> SELECT LAST_NAME, SALARY
     FROM EMPLOYEES
     WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES);

SQL> SELECT EMPLOYEE_ID, SALARY
     FROM EMPLOYEES
     WHERE SALARY > (SELECT SALARY FROM EMPLOYEES
                     WHERE EMPLOYEE_ID = 100);

- Multi row Subquery (Kết quả nhiều hàng): Truy vấn bên trong trả về nhiều hơn 1 hàng. Dùng các toán tử so sánh nhiều hàng (IN,  ANY, ALL, ...) để so sánh với truy vấn ngoài.

SQL> SELECT EMPLOYEE_ID, LAST_NAME, SALARY
     FROM EMPLOYEES
     WHERE SALARY IN (SELECT MIN(SALARY) FROM EMPLOYEES
                      GROUP BY DEPARTMENT_ID);

SQL> SELECT EMPLOYEE_ID, SALARY
     FROM EMPLOYEES
     WHERE SALARY IN (SELECT MIN(SALARY) FROM EMPLOYEES
                      WHERE DEPARTMENT_ID = 30);

- Single column Subquery: Truy vấn lấy tên của nhân viên được trả lương cao nhất thuộc bộ phận 30. ALL được dùng với toán tử so sánh vì subquey không trả về giá trị duy nhất mà đang trả về nhiều giá trị.

SQL> SELECT FIRST_NAME, LAST_NAME
     FROM EMPLOYEES
     WHERE DEPT_ID = 30
       AND SALARY >= ALL (SELECT SALARY FROM EMPLOYEES
                          WHERE DEPT_ID = 30);

- Multi column Subquery: Truy vấn so sánh với điều kiện từ 2 column trở lên giữa parent subquery và subquery được gọi là Multiple-column Subquery.
Với điều kiện trả ra các nhân viên làm cùng một công việc và cùng mức lương với nhân viên có employee_id = 420.

SQL> SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARY
     FROM EMPLOYEES
     WHERE (JOB_ID, SALARY) IN (SELECT JOB_ID, SALARY 
                                FROM EMPLOYEES
                                WHERE EMPLOYEE_ID = 420);

* Correlated Subquery: Truy vấn bên ngoài sẽ thực thi đầu tiên và đối với mỗi hàng của kết quả truy vấn bên ngoài, truy vấn bên trong sẽ dùng tiếp để thực thi. Vì vậy truy vấn bên trong sẽ thực hiện nhiều lần dựa vào số hàng kết quả của truy vấn bên ngoài. Đầu truy vấn bên ngoài dùng kết quả của truy vấn bên trong để so sánh. Điều này có nghĩa là truy vấn bên trong và bên ngoài phụ thuộc nhau.

SQL> SELECT DEPARTMENT_ID, EMPLOYEE_ID, SALARY
     FROM EMPLOYEES E1
     WHERE 1 = (SELECT COUNT(DISTINCT SALARY) 
                FROM EMPLOYEES E2
                WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
                  AND E1.SALARY <= E2.SALARY);

SQL> SELECT DEPARTMENT_ID, DEPARTMENT_NAME
     FROM DEPARTMENTS D
     WHERE EXISTS (SELECT 1
                   FROM EMPLOYEES E
                   WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID);

SQL> SELECT DEPARTMENT_ID, DEPARTMENT_NAME
     FROM DEPARTMENTS D
     WHERE NOT EXISTS (SELECT 1
                       FROM EMPLOYEES E
                       WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID);

* Nested Subquery: xảy ra khi subquery nằm trong mệnh đề where hoặc having của một subquery khác. Subquery trong cùng sẽ thực hiện đầu tiên và sau đó dựa trên kết quả của nó, subquery tiếp theo sẽ được thực hiện. Cuối cùng, truy vấn ngoài cùng sẽ thực hiện dựa trên kết quả này.

SQL> SELECT *
     FROM RESULT
     WHERE ROLLNO IN (SELECT ROLLNO
                      FROM STUDENT
                      WHERE COURSEID = (SELECT COURSEID
                                        FROM STUDENT
                                        WHERE ROLLNO = 12));

* Scalar Subquery (truy vấn con vô hướng): là truy vấn con trả về chính xác giá trị cột từ một hàng. Giá trị trả về của Scalar Subquery là một danh sách của subquery. Nếu subquery trả về 0 hàng, thì giá trị của scalar subquery là NULL. Ngược lại, nếu trả về nhiều hàng thì Oracle trả về lỗi.

SQL> SELECT LAST_NAME, JOB_ID, SALARY
     FROM EMPLOYEES
     WHERE SALARY > (SELECT AVG(SALARY)
                     FROM EMPLOYEES);

SQL> SELECT D.DEPT_NO, D.DEPT_NAME, 
            (SELECT COUNT(*) FROM EMPLOYEES E
             WHERE E.DEPT_NO = D.DEPT_NO) AS "Num Dept"
     FROM DEPARTMENTS D;

Kết: Sự khác biệt giữa kỹ thuật Normal Subquery và Co-related Subquery:

1. Looping - lặp: Co-related Subquery thực hiện dựa trên truy vấn chính (main-query), trong khi đó Normal Subquery thì không. Do đó Co-related Subquery thực hiện dựa trên mỗi lần lặp của main-query. Trong khi đó, trường hợp của Normal Subquery, subquery thực hiện đầu tiên, sau đó truy vấn bên ngoài mới được thực hiện tiếp theo. Vì vậy tối đa số lần thực thi của Co-related Subquery là MxN, và M+N đối với Normal Subquery.
2. Dependency - sự phụ thuộc (từ trong ra ngoài vs từ ngoài vào trong): Trong trường hợp Co-related Subquery, truy vấn bên trong phụ thuộc truy vấn bên ngoài. Đối với Normal Subquery thì truy vấn bên ngoài lại phụ thuộc truy vấn bên trong.
3. Performance - hiệu suất: Sử dụng Co-related Subquery làm giảm hiệu suất vì nó thực hiện MxN vòng lặp thay vì M+N vòng lặp của Normal Subquery.

Hãy tham gia và để lại nhận xét của bạn vào bên dưới. Càng nhiều người tham gia, chúng ta càng được hưởng lợi nhiều =)). Vì vậy, để lại những suy nghĩ của bạn trước khi rời khỏi trang nhé.