Ninja IT

Self-join

Self-join

Self-join là một kiểu JOIN trong đó bảng tự JOIN với chính nó.
Bởi vì có hai bảng cùng tên nên chúng ta sẽ tạo ALIAS cho từng bảng.
Self-join dùng để mô tả các dữ liệu dạng cây có quan hệ cha con.

Ví dụ sau chúng ta lưu trữ một số thư mục và tập tin của linux.

/ (root) → bin → cat, ls
         → etc → hosts, mysql
         → usr → bin, share
                  ↳ vim, make        
         → var → www, log
         → sys → kernel

Mô tả bảng như sau:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

DROP TABLE IF EXISTS folders;

CREATE TABLE folders (
  id INTEGER PRIMARY KEY AUTOINCREMENT, -- SQLite 
  -- id INT AUTO_INCREMENT PRIMARY KEY, -- MySQL 
  -- id SERIAL PRIMARY KEY,  -- PostgreSQL
  parent_id INTEGER,
  name VARCHAR(255),
  type CHAR(1)
);

-- kiểu d là folder
-- kiểu - là file
INSERT INTO folders (id, parent_id, name, type) VALUES 
  (1 , NULL, '/'     , 'd'),
  (2 , 1   , 'bin'   , 'd'),
  (3 , 1   , 'etc'   , 'd'),
  (4 , 1   , 'usr'   , 'd'),
  (5 , 1   , 'var'   , 'd'),
  (6 , 1   , 'sys'   , 'd'),
  (7 , 2   , 'cat'   , '-'),
  (8 , 2   , 'ls'    , '-'),
  (9 , 3   , 'hosts' , '-'),
  (10, 3   , 'mysql' , 'd'),
  (11, 4   , 'bin'   , 'd'),
  (12, 4   , 'share' , 'd'),
  (13, 5   , 'www'   , 'd'),
  (14, 5   , 'log'   , 'd'),
  (15, 6   , 'kernel', 'd'),
  (16, 11  , 'vim'   , '-'),
  (17, 11  , 'make'  , '-');

Lấy ra tên thư mục và tên thư mục cha

Câu lệnh SQL sau sẽ lấy tên thư mục / file cùng với tên thư mục cha (không tính thư mục root).

1
2
3
4

SELECT parent.name AS parrent_name, child.name, child.type
  FROM folders AS child
  INNER JOIN folders AS parent
  ON child.parent_id = parent.id;

Dữ liệu sẽ in ra:

+--------------+--------+------+
| parrent_name | name   | type |
+--------------+--------+------+
| /            | bin    | d    |
| /            | etc    | d    |
| /            | usr    | d    |
| /            | var    | d    |
| /            | sys    | d    |
| bin          | cat    | -    |
| bin          | ls     | -    |
| etc          | hosts  | -    |
| etc          | mysql  | d    |
| usr          | bin    | d    |
| usr          | share  | d    |
| var          | www    | d    |
| var          | log    | d    |
| sys          | kernel | d    |
| bin          | vim    | -    |
| bin          | make   | -    |
+--------------+--------+------+

Lấy thư mục với điều kiện nhất định

Chúng ta lấy các thư mục và file có thư mục cha có đường dẫn tuyệt đối là /bin.

1
2
3
4
5

SELECT parent.name AS parrent_name, child.name, child.type
  FROM folders AS child
  INNER JOIN folders AS parent
  ON child.parent_id = parent.id
  WHERE parent.name = 'bin' AND parent.parent_id = 1;

Kết quả là:

+--------------+------+------+
| parrent_name | name | type |
+--------------+------+------+
| bin          | cat  | -    |
| bin          | ls   | -    |
+--------------+------+------+