Lệnh JOIN trong SQLite - SQLite căn bản và nâng cao

Trong bài này chúng ta sẽ tìm hiểu lệnh JOIN trong SQLite.

1. Lệnh JOIN trong SQLite là gì?

Lệnh JOIN trong SQLite được sử dụng để kết hợp các bản ghi từ hai hoặc nhiều bảng trong cơ sở dữ liệu. Lệnh JOIN sẽ kết hợp các trường từ hai bảng bằng cách sử dụng các giá trị chung cho mỗi bảng.

SQLite định nghĩa ba loại join chính như sau:

• CROSS JOIN
• INNER JOIN
• OUTER JOIN

Giả sử chúng ta có 2 bảng COMPANY và DEPARTMENT. Chúng ta đã sử dụng các câu lệnh INSERT để điền dữ liệu vào bảng COMPANY. Vì vậy, giả sử chúng ta đã có các dòng dữ liệu sau trong bảng COMPANY

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Bảng DEPARTMENT được định nghĩa như sau:

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Dưới đây là danh sách các câu lệnh INSERT để điền dữ liệu vào bảng DEPARTMENT

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

Cuối cùng bảng DEPARTMENT có các dòng dữ liệu như sau:

ID          DEPT        EMP_ID
----------  ----------  ----------
1           IT Billing  1
2           Engineering 2
3           Finance     7

2. CROSS JOIN trong SQLite

CROSS JOIN kết nối mọi hàng của bảng đầu tiên với mỗi hàng của bảng thứ hai. Nếu các bảng đầu vào có hàng x và y, tương ứng, bảng kết quả sẽ có hàng x * y. Vì CROSS JOIN có khả năng tạo các bảng cực lớn, nên phải cẩn thận chỉ sử dụng chúng khi thích hợp.

Cú pháp của CROSS JOIN trong SQLite như sau:

SELECT ... FROM table1 CROSS JOIN table2 ...

Dựa vào các bảng trên, bạn có thể viết CROSS JOIN như sau:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

Truy vấn trên sẽ tạo ra kết quả như sau:

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Paul        Engineering
7           Paul        Finance
1           Allen       IT Billing
2           Allen       Engineering
7           Allen       Finance
1           Teddy       IT Billing
2           Teddy       Engineering
7           Teddy       Finance
1           Mark        IT Billing
2           Mark        Engineering
7           Mark        Finance
1           David       IT Billing
2           David       Engineering
7           David       Finance
1           Kim         IT Billing
2           Kim         Engineering
7           Kim         Finance
1           James       IT Billing
2           James       Engineering
7           James       Finance

3. INNER JOIN trong SQLite

INNER JOIN tạo một bảng kết quả mới bằng cách kết hợp các giá trị cột của hai bảng (bảng1 và bảng2) dựa trên join-predicate. Truy vấn so sánh từng hàng của bảng1 với từng hàng của bảng2 để tìm tất cả các cặp hàng thỏa mãn join-predicate. Khi join-predicate được thỏa mãn, các giá trị cột cho mỗi cặp hàng A và B phù hợp được kết hợp thành một hàng kết quả.

INNER JOIN là loại join mặc định và phổ biến nhất. Bạn có thể sử dụng từ khóa INNER tùy ý.

Cú pháp của INNER JOIN trong SQLite như sau:

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

Để tránh dư thừa và giữ cho cụm từ ngắn hơn, các điều kiện INNER JOIN có thể được khai báo bằng biểu thức USING. Biểu thức này chỉ định danh sách một hoặc nhiều cột.

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

NATURAL JOIN tương tự như JOIN ... USING, chỉ khi nó tự động kiểm tra sự bằng nhau giữa các giá trị của mỗi cột tồn tại trong cả hai bảng

SELECT ... FROM table1 NATURAL JOIN table2...

Dựa vào các bảng trên, bạn có thể viết INNER JOIN như sau:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

Truy vấn trên sẽ tạo ra kết quả như sau:

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineering
7           James       Finance

4. OUTER JOIN trong SQLite

OUTER JOIN là một phần mở rộng của INNER JOIN. Mặc dù tiêu chuẩn SQL xác định ba loại OUTER JOIN: LEFT, RIGHT, và FULL, SQLite chỉ hỗ trợ LEFT OUTER JOIN.

OUTER JOIN có một điều kiện giống hệt với INNER JOIN, được thể hiện bằng cách sử dụng từ khóa ON, USING, hoặc NATURAL. Bảng kết quả ban đầu được tính theo cùng một cách. Sau khi tính toán primary JOIN, OUTER JOIN  sẽ lấy bất kỳ hàng không liên kết nào từ một hoặc cả hai bảng, đưa chúng ra bằng NULL và nối chúng vào bảng kết quả.

Sau đây là cú pháp của LEFT OUTER JOIN

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

Để tránh dư thừa và giữ cho cụm từ ngắn hơn, các điều kiện OUTER JOIN có thể được khai báo bằng biểu thức USING. Biểu thức này chỉ định danh sách một hoặc nhiều cột.

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

Dựa vào các bảng trên, bạn có thể viết một inner join như sau:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

Truy vấn trên sẽ tạo ra kết quả như sau:

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineering
            Teddy
            Mark
            David
            Kim
7           James       Finance