Thứ Tư, 5 tháng 8, 2020

SQL cơ bản - Bài 5: JOIN

Trong bài viết này, chúng tôi sẽ xem xét một số liên kết phổ biến, cả ANSI và không ANSI, có sẵn trong SQL.

  • Thiết lập môi trường
  • Giới thiệu
  • [INNER] JOIN ... ON
  • LEFT [OUTER] JOIN
  • RIGHT [OUTER] JOIN
  • FULL [OUTER] JOIN
  • CROSS JOIN
  • NATURAL JOIN
  • [INNER] JOIN ... USING


Thiết lập môi trường

Bạn có thể thực hiện tất cả các truy vấn trực tuyến miễn phí bằng SQL Fiddle .
Các ví dụ trong bài viết này yêu cầu phải có các bảng sau đây.
--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;

CREATE TABLE departments (
  department_id   NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
  department_name VARCHAR2(14),
  location        VARCHAR2(13)
);

INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;


CREATE TABLE employees (
  employee_id   NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
  employee_name VARCHAR2(10),
  job           VARCHAR2(9),
  manager_id    NUMBER(4),
  hiredate      DATE,
  salary        NUMBER(7,2),
  commission    NUMBER(7,2),
  department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);

INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
Các bảng này là một biến thể của các bảng EMP và DEPT từ lược đồ SCOTT. Bạn sẽ thấy rất nhiều ví dụ của Oracle trên internet bằng cách sử dụng các bảng từ lược đồ SCOTT. Bạn có thể tìm thấy các định nghĩa bảng gốc trong tập lệnh "$ORACLE_HOME/rdbms/admin/utlsampl.sql".

Giới thiệu

Các phép nối (join) được sử dụng để kết hợp dữ liệu từ nhiều bảng để tạo thành một tập kết quả duy nhất. Oracle cung cấp hai cách tiếp cận để tham gia các bảng, cú pháp nối không ANSI và cú pháp nối ANSI, trông khá khác nhau.
Cú pháp tham gia không phải ANSI trong lịch sử là cách bạn thực hiện tham gia trong Oracle và ngày nay nó vẫn rất phổ biến. Các bảng được nối được liệt kê trong  mệnh đềFROM và các điều kiện nối được định nghĩa là các vị từ trong mệnh đề WHERENgay cả khi bạn không thích nó, bạn sẽ phải làm quen với nó vì có rất nhiều mã ngoài đó vẫn sử dụng nó. Nếu bạn không quen với cú pháp, bạn sẽ phải vật lộn để sửa lỗi bất kỳ mã hiện có nào và một số ví dụ trên internet sẽ trông khá bí ẩn đối với bạn.
Cú pháp tham gia ANSI được giới thiệu trong Oracle 9i . Nó có một số lợi thế so với cú pháp ban đầu.
  • Nó đọc giống tiếng Anh hơn, vì vậy nó rõ ràng hơn nhiều.
  • Các bảng và điều kiện nối đều được giữ cùng nhau trong mệnh đề FROM, vì vậy mệnh đề WHEREchỉ chứa các bộ lọc, không chứa các điều kiện nối.
  • Cú pháp gây khó khăn, nếu không nói là không thể bao gồm điều kiện nối.
  • Các bộ lọc trên các cột từ các bảng tham gia bên ngoài được xử lý theo cách rõ ràng hơn nhiều.
  • Nó dễ mang theo hơn, được hỗ trợ bởi một số công cụ cơ sở dữ liệu quan hệ.
  • Nó cung cấp một số chức năng không được hỗ trợ trực tiếp bởi cú pháp tham gia không phải ANSI, mà không cần sử dụng nhiều nỗ lực hơn.
Bất chấp tất cả những lợi thế này, nhiều nhà phát triển của Oracle vẫn sử dụng cú pháp tham gia không phải ANSI. Một phần điều này chỉ vì thói quen. Một phần điều này là do trình tối ưu hóa Oracle chuyển đổi hầu hết cú pháp nối ANSI thành cú pháp nối không ANSI tương đương trước khi nó được thực thi.
Đối với người mới bắt đầu, ý kiến ​​cá nhân của tôi là bạn nên tập trung vào cú pháp tham gia ANSI, nhưng lưu ý về tương đương không phải ANSI. Trong bài viết này tôi sẽ chỉ ra cú pháp ANSI và không phải ANSI cho mỗi ví dụ, khi có liên quan.
Một số phương thức tham gia phổ biến hơn các phương pháp khác, vì vậy ban đầu tập trung sự chú ý của bạn vào những phương pháp bạn có thể thấy nhất. Các phép nối phổ biến nhất mà bạn có thể thấy trong mã là như sau.
  • [INNER] JOIN ... ON
  • LEFT [OUTER] JOIN
  • RIGHT [OUTER] JOIN
Sau đây là ít phổ biến hơn.
  • FULL [OUTER] JOIN
  • CROSS JOIN
  • NATURAL JOIN
  • [INNER] JOIN ... USING
Sau đây là rất hiếm tại thời điểm này.
  • CROSS APPLY
  • OUTER APPLY
Nếu một từ được bao quanh bởi "[]" thì có nghĩa đó là một từ khóa tùy chọn. Không có bất kỳ vòng loại nào khác, tham gia là một tham gia bên trong, vì vậy sử dụng từ khóa INNER là không cần thiết. Nếu liên kết bao gồm các từ LEFTRIGHT hoặc FULL, theo định nghĩa là join ngoài, vì vậy từ khóa OUTER là dư thừa. Lựa chọn bao gồm hoặc loại trừ những từ này thực sự là sở thích cá nhân, vì vậy hãy tuân theo tiêu chuẩn trong công ty của bạn hoặc làm những gì cảm thấy phù hợp với bạn.
Với tất cả những gì trong tâm trí, chúng ta hãy xem xét một số ví dụ.

[INNER] JOIN ... ON

Một dữ liệu INNER JOIN kết hợp từ hai bảng trong đó có một kết quả khớp trên (các) cột tham gia trong cả hai bảng.

Hãy nhớ rằng, từ khóa INNER là tùy chọn. Trong các ví dụ bên dưới, chúng tôi sẽ trả lại DEPARTMENT_NAME và EMPLOYEE_NAME cho mỗi nhân viên. Bộ phận OPERATIONS có DEPARTMENT_ID là 40, do đó, nó không bị xóa bởi điều kiện bộ lọc, nhưng không có nhân viên nào trong bộ phận này, do đó không có kết quả trùng khớp và nó không được trả về trong tập kết quả.
Dưới đây là một ví dụ về ANSI INNER JOIN.
SELECT d.department_name,
       e.employee_name
FROM   departments d
       JOIN employees e ON d.department_id = e.department_id
WHERE  d.department_id >= 30
ORDER BY d.department_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

6 rows selected.

SQL>
Đây là tương đương non-ANSI của câu lệnh trước.
SELECT d.department_name,
       e.employee_name
FROM   departments d, employees e
WHERE  d.department_id = e.department_id
AND    d.department_id >= 30
ORDER BY d.department_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

6 rows selected.

SQL>

LEFT [OUTER] JOIN (JOIN TRÁI)

LEFT [OUTER] JOIN trả về tất cả các hàng hợp lệ từ bảng ở phía bên trái của từ khóa JOIN, cùng với các giá trị từ bảng ở phía bên phải hoặc NULL nếu một hàng phù hợp không tồn tại.

Sử dụng ví dụ trước, nhưng chuyển sang một LEFT OUTER JOIN, chúng ta sẽ thấy bộ phận OPERATIONS , mặc dù nó không có nhân viên.
Dưới đây là một ví dụ về ANSI LEFT OUTER JOIN.
SELECT d.department_name,
       e.employee_name     
FROM   departments d
       LEFT OUTER JOIN employees e ON d.department_id = e.department_id
WHERE  d.department_id >= 30
ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
OPERATIONS
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

7 rows selected.

SQL>
Đây là tương đương non-ANSI của câu lệnh trước. Lưu ý "(+)" được sử dụng để chỉ ra phía của điều kiện join có thể bị thiếu. Đối với điều kiện nối nhiều cột, mỗi cột phải có "(+)". Không giống như cú pháp nối ANSI, cú pháp nối non-ANSI không bị ảnh hưởng bởi thứ tự của các bảng.
SELECT d.department_name,
       e.employee_name      
FROM   departments d, employees e
WHERE  d.department_id = e.department_id (+) 
AND    d.department_id >= 30
ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
OPERATIONS
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

7 rows selected.

SQL>
Thêm bộ lọc (filter) vào các cột được trả về từ bảng đã tham gia bên ngoài (outer) là nguyên nhân phổ biến gây nhầm lẫn. Nếu bạn kiểm tra một giá trị cụ thể, ví dụ "salary> = 2000", nhưng giá trị cho cột SALARY là NULL vì hàng bị thiếu, một điều kiện thông thường trong mệnh đề WHERE sẽ ném hàng đi, do đó đánh bại đối tượng của outer join. Cả hai phương pháp ANSI và non-ANSI đều có cách xử lý vấn đề này.
Sử dụng cú pháp  ANSI join, các bộ lọc trên các cột từ bảng nối ngoài được bao gồm trong chính phép nối, thay vì được đặt trong mệnh đề WHERE.
SELECT d.department_name,
       e.employee_name     
FROM   departments d
       LEFT OUTER JOIN employees e ON d.department_id = e.department_id AND e.salary >= 2000
WHERE  d.department_id >= 30
ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
OPERATIONS
SALES          BLAKE

2 rows selected.

SQL>
Sử dụng cú pháp nối non-ANSI, "(+)" được sử dụng để chỉ ra một cột có thể có giá trị NULL là kết quả của phép outer join.
SELECT d.department_name,
       e.employee_name      
FROM   departments d, employees e
WHERE  d.department_id = e.department_id (+)
AND    e.salary (+) >= 2000
AND    d.department_id >= 30
ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
OPERATIONS
SALES          BLAKE

2 rows selected.

SQL>

RIGHT [OUTER] JOIN (JOIN PHẢI)

Sự RIGHT [OUTER] JOIN đối lập của LEFT [OUTER] JOINNó trả về tất cả các hàng hợp lệ từ bảng ở phía bên phải của từ khóa JOIN, cùng với các giá trị từ bảng ở phía bên trái hoặc NULL nếu một hàng phù hợp không tồn tại. Tất cả các điểm nêu ra trong phần trước cũng áp dụng ở đây.

Ví dụ sau đây đã thay đổi thứ tự của các bảng để RIGHT [OUTER] JOIN bây giờ bắt buộc phải có.
SELECT d.department_name,
       e.employee_name     
FROM   employees e
       RIGHT OUTER JOIN departments d ON e.department_id = d.department_id
WHERE  d.department_id >= 30
ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
OPERATIONS
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

7 rows selected.

SQL>
Hãy nhớ rằng, cú pháp nối ngoài non-ANSI không phụ thuộc vào thứ tự bảng, do đó không có khái niệm thực sự về các phép nối ngoài phải hoặc trái, chỉ là các phép nối ngoài.

FULL [OUTER] JOIN (JOIN ĐẦY ĐỦ)

FULL [OUTER] JOIN kết hợp tất cả các hàng từ các bảng ở bên trái và bên phải của phép join. Nếu một trong hai bên bị thiếu dữ liệu, nó sẽ được thay thế bằng NULL, thay vì bỏ hàng đi.

Để xem một ví dụ hoạt động, chúng ta cần thêm một nhân viên khác không được chỉ định vào một bộ phận.
INSERT INTO employees VALUES (8888,'JONES','DBA',null,to_date('02-1-1982','dd-mm-yyyy'),1300,NULL,NULL);
COMMIT;
Dưới đây là một ví dụ về ANSI FULL OUTER JOIN.
SELECT d.department_name,
       e.employee_name     
FROM   employees e
       FULL OUTER JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
OPERATIONS
RESEARCH       ADAMS
RESEARCH       FORD
RESEARCH       JONES
RESEARCH       SCOTT
RESEARCH       SMITH
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD
               JONES

16 rows selected.

SQL>
Không có tương đương trực tiếp với phép nối ngoài đầy đủ bằng cú pháp nối non-ANSI, nhưng chúng ta có thể tạo lại nó bằng cách kết hợp hai truy vấn nối ngoài bằng cách sử dụng a UNION ALL, như được hiển thị bên dưới.
SELECT d.department_name,
       e.employee_name      
FROM   employees e, departments d
WHERE  e.department_id = d.department_id (+)
UNION ALL
SELECT d.department_name,
       e.employee_name      
FROM   departments d, employees e
WHERE  d.department_id = e.department_id (+)
AND    e.employee_name IS NULL
ORDER BY 1, 2;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
OPERATIONS
RESEARCH       ADAMS
RESEARCH       FORD
RESEARCH       JONES
RESEARCH       SCOTT
RESEARCH       SMITH
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD
               JONES

16 rows selected.

SQL>
Thật thú vị, khi bạn chạy ANSI FULL OUTER JOIN, trình tối ưu hóa của Oracle viết lại nó thành tương đương non-ANSI, do đó không có cải tiến hiệu suất liên quan đến nó. Nó chỉ dễ nhìn hơn.
Hãy loại bỏ nhân viên bổ sung đó để nó không ảnh hưởng đến bất kỳ ví dụ nào khác.
DELETE FROM employees WHERE employee_id = 8888;
COMMIT;

CROSS JOIN

CROSS JOIN là sự tạo ra có chủ ý của một sản phẩm của Cartesian. Không có cột tham gia nào được chỉ định, do đó, mọi sự kết hợp có thể của các hàng giữa hai bảng được tạo ra.

Dưới đây là một ví dụ về ANSI CROSS JOIN.
SELECT e.employee_name,
       d.department_name
FROM   employees e
       CROSS JOIN departments d
ORDER BY e.employee_name, d.department_name;

EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS      ACCOUNTING
ADAMS      OPERATIONS
ADAMS      RESEARCH
ADAMS      SALES

... Output amended for brevity ...

WARD       ACCOUNTING
WARD       OPERATIONS
WARD       RESEARCH
WARD       SALES

56 rows selected.

SQL>
Đây là tương đương non-ANSI của câu lệnh trước. Lưu ý, không có điều kiện tham gia trong mệnh đề WHERE.
SELECT e.employee_name,
       d.department_name
FROM   employees e, departments d
ORDER BY e.employee_name, d.department_name;


EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS      ACCOUNTING
ADAMS      OPERATIONS
ADAMS      RESEARCH
ADAMS      SALES

... Output amended for brevity ...

WARD       ACCOUNTING
WARD       OPERATIONS
WARD       RESEARCH
WARD       SALES

56 rows selected.

SQL>

NATURAL JOIN (JOIN TỰ NHIÊN)

NATURAL JOIN là một biến thể trên một INNER JOINCác cột tham gia được xác định ngầm, dựa trên các tên cột. Bất kỳ cột nào có cùng tên giữa hai bảng được coi là cột join. Dưới đây là một ví dụ sử dụng cú pháp join ANSI.
ELECT e.employee_name,
       d.department_name
FROM   employees e
       NATURAL JOIN departments d
ORDER BY e.employee_name, d.department_name;

EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS      RESEARCH
ALLEN      SALES
BLAKE      SALES
CLARK      ACCOUNTING
FORD       RESEARCH
JAMES      SALES
JONES      RESEARCH
KING       ACCOUNTING
MARTIN     SALES
MILLER     ACCOUNTING
SCOTT      RESEARCH
SMITH      RESEARCH
TURNER     SALES
WARD       SALES

14 rows selected.

SQL>
Không có tương đương non-ANSI về điều này, vì tất cả các điều kiện tham gia phải được chỉ định.
Sử dụng một NATURAL JOIN là một ý tưởng tồi. Nếu ai đó thêm một cột mới vào một trong các bảng có cùng tên với một cột trong bảng khác, họ có thể phá vỡ mọi liên kết tự nhiên hiện có. Nó thực sự là một lỗi đang chờ để xảy ra.
Bạn không thể áp dụng bất kỳ bộ lọc bí danh nào cho các cột được sử dụng trong các phép join tự nhiên, như trong ví dụ sau.
SELECT e.employee_name,
       d.department_name
FROM   employees e
       NATURAL JOIN departments d
WHERE  d.department_id = 20
ORDER BY e.employee_name;

WHERE  d.department_id = 20
       *
ERROR at line 5:
ORA-25155: column used in NATURAL join cannot have qualifier

SQL>
Thay vào đó, bạn phải xóa bí danh, trong các trường hợp khác sẽ dẫn đến lỗi tham chiếu mơ hồ.
SELECT e.employee_name,
       d.department_name
FROM   employees e
       NATURAL JOIN departments d
WHERE  department_id = 20
ORDER BY e.employee_name;

EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS      RESEARCH
FORD       RESEARCH
JONES      RESEARCH
SCOTT      RESEARCH
SMITH      RESEARCH

5 rows selected.

SQL>

[INNER] JOIN ... USING

INNER JOIN ... USING gần như là một ngôi nhà nửa đường giữa thông thường INNER JOIN và NATURAL JOINPhép nối được tạo bằng các cột có tên trùng khớp trong mỗi bảng, nhưng bạn phải chỉ định các cột sẽ được sử dụng, không phải toàn bộ điều kiện. Điều này cho phép bạn tham gia vào một tập hợp con của các cột chung cho cả hai bảng.
SELECT e.employee_name,
       d.department_name
FROM   employees e
       JOIN departments d USING (department_id)
ORDER BY e.employee_name;

EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS      RESEARCH
ALLEN      SALES
BLAKE      SALES
CLARK      ACCOUNTING
FORD       RESEARCH
JAMES      SALES
JONES      RESEARCH
KING       ACCOUNTING
MARTIN     SALES
MILLER     ACCOUNTING
SCOTT      RESEARCH
SMITH      RESEARCH
TURNER     SALES
WARD       SALES

14 rows selected.

SQL>
Đây là một cú pháp join an toàn vì nó không thể bị ảnh hưởng bởi việc thêm các cột vào một trong hai bảng. Tương tự như NATURAL JOIN, bạn không thể áp dụng bất kỳ bộ lọc bí danh nào cho các cột được sử dụng trong liên kết, nhưng nếu bạn xóa bí danh thì nó hoạt động.
ELECT e.employee_name,
       d.department_name
FROM   employees e
       JOIN departments d USING (department_id)
WHERE  d.department_id = 20
ORDER BY e.employee_name;

WHERE  d.department_id = 20
       *
ERROR at line 5:
ORA-25154: column part of USING clause cannot have qualifier

SQL>


SELECT e.employee_name,
       d.department_name
FROM   employees e
       JOIN departments d USING (department_id)
WHERE  department_id = 20
ORDER BY e.employee_name;

EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS      RESEARCH
FORD       RESEARCH
JONES      RESEARCH
SCOTT      RESEARCH
SMITH      RESEARCH

5 rows selected.

SQL>

@ Trần Văn Bình - Founder of "Oracle DBA Việt Nam" #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master