Sử dụng JOIN trong SQLite

Mệnh đề Joins trong SQLite được sử dụng để kết hợp các bản ghi từ hai hoặc nhiều bảng trong một Database. Một JOIN là một phương tiện để kết hợp các trường từ hai bảng bằng việc sử dụng các giá trị chung của mỗi bảng.

SQLite định nghĩa ba loại Join chính, đó là:

• CROSS JOIN
• INNER JOIN
• OUTER JOIN

Trước khi bắt đầu, bạn theo dõi hai bảng COMPANY và DEPARTMENT. Chúng ta đã thấy cách lệnh INSERT nhập dữ liệu vào trong bảng COMPANY. Do đó, giả sử dưới đây là danh sách các bản ghi có 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ó các định nghĩa sau:

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

Lệnh INSERT để chèn dữ liệu vào bảng này:

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 );

Và chúng ta có các bản ghi sau trong bảng DEPARTMENT:

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

CROSS JOIN trong SQLite

Một 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ó x và y cột, thì tương ứng với đó, bảng kết quả sẽ có x+y cột. Bởi vì các CROSS JOIN này có khả năng để tạo ra các bảng lớn, do đó bạn nên cẩn thận để chỉ sử dụng chúng khi thấy cần thiết.

Cú pháp cơ bản của CROSS JOIN trong SQLite là:

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

Dựa vào hai bảng trên, chúng ta có thể viết một Cross Join như sau:

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

Truy vấn trên sẽ cho kết quả:

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

INNER JOIN trong SQLite

Một INNER JOIN tạo một bảng kết quả mới bằng việc kết hợp các giá trị cột của hai bảng (table1 và table2) dựa trên join-predicate. Truy vấn này so sánh mỗi hàng trong table1 với mỗi hàng trong table2 để tìm ra các cặp hàng mà thỏa mãn join-predicate. Khi join-predicate được thỏa mãn, các giá trị cột của mỗi cặp hàng được kết nối A và B được kết hợp vào trong một hàng kết quả.

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

Cú pháp cơ bản của INNER JOIN trong SQLite là:

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

Để tránh sự rườm rà và giúp cho truy vấn của bạn ngắn hơn, các điều kiện INNER JOIN có thể được khai báo với biểu thức USING. Biểu thức này xác định một danh sách của một hoặc nhiều cột.

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

Một NATURAL JOIN là tương đương với một JOIN…USING, chỉ khi nó tự động kiểm tra cho sự cân bằng 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 hai bảng trên, chúng ta có thể viết mộ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ẽ cho kết quả:

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

OUTER JOIN trong SQLite

OUTER JOIN là một sự mở rộng của INNER JOIN. Mặc dù SQL chuẩn định nghĩa 3 loại OUTER JOIN là: LEFT, RIGHT, và FULL nhưng SQLite chỉ hỗ trợ LEFT OUTER JOIN.

Các OUTER JOIN có một điều kiện giống như các INNER JOIN, được biểu diễn bởi sử dụng từ khóa ON, USING hoặc NATURAL. Bảng kết quả ban đầu được tính toán theo cùng cách thức. Khi JOIN đầu tiên được tính toán, một Outer Join sẽ nhận bất kỳ các hàng chưa được kết hợp nào từ một hoặc các bảng, đệm chúng với các NULL và phụ chúng thêm vào bảng kết quả.

Cú pháp cơ bản của LEFT OUTER JOIN trong SQLite là:

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

Để tránh sự rườm rà và giúp cho truy vấn của bạn ngắn hơn, các điều kiện INNER JOIN có thể được khai báo với biểu thức USING. Biểu thức này xác định một danh sách của 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, chúng ta có thể viết một LEFT OUTER 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ẽ cho kết quả:

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