SQL query return data from multiple tables

Tôi muốn biết những điều dưới đây:

• Làm thế nào có thể lấy dữ liệu từ nhiều bảng trong Database.
• Có những cách nào để làm được điều này.
• Sử dụng join và union khác nhau như thế nào?

Để lấy dữ liêu từ nhiều bảng mà không phải connect nhiều lần đến DB, vậy tôi phải làm thế nào? Câu trả lời sau khi đọc xong các phần dưới đây.

Part 1 - Joins and Unions

Tại sao lại sử dụng nó? Nó có mang lại hiệu quả về performance không? Câu trả lời là không, nhưng cách viết này lại là câu Sql dễ đọc. Để bài toán trở nên dễ hình dung, tôi sẽ tạo ra CSDL để người dùng dễ hiểu điều này hơn. Bảng đầu tiên là thông tin danh sách màu sắc:

mysql> create table colors(id int(3) not null auto_increment primary key, color varchar(15), paint varchar(10));
Query OK, 0 rows affected (0,28 sec)

mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| color | varchar(15) | YES  |     | NULL    |                |
| paint | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0,01 sec)

mysql> insert into colors (color, paint) values ('Red', 'Metallic'), 
    -> ('Green', 'Gloss'), ('Blue', 'Metallic'), 
    -> ('White' 'Gloss'), ('Black' 'Gloss');
Query OK, 5 rows affected (0.00 sec) 
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from colors;
+----+-------+----------+
| id | color | paint    |
+----+-------+----------+
|  1 | Red   | Metallic |
|  2 | Green | Gloss    |
|  3 | Blue  | Metallic |
|  4 | White | Gloss    |
|  5 | Black | Gloss    |
+----+-------+----------+
5 rows in set (0,00 sec)

mysql> select * from colors;
+----+-------+----------+
| id | color | paint    |
+----+-------+----------+
|  1 | Red   | Metallic |
|  2 | Green | Gloss    |
|  3 | Blue  | Metallic |
|  4 | White | Gloss    |
|  5 | Black | Gloss    |
+----+-------+----------+
5 rows in set (0,00 sec)

Tiếp đó tạo bảng chứa thông tin các hãng xe

mysql> create table brands (id int(3) not null auto_increment primary key,
    -> brand varchar(15));
Query OK, 0 rows affected (0,31 sec)

mysql> show columns from brands;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| brand | varchar(15) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0,00 sec)

mysql> insert into brands (brand) values ('Ford'), ('Toyota'),
    -> ('Nissan'),('Smart'), ('BMW');
Query OK, 5 rows affected (0,03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from brands;
+----+--------+
| id | brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  3 | Nissan |
|  4 | Smart  |
|  5 | BMW    |
+----+--------+
5 rows in set (0,00 sec)

Tiếp đến tạo bảng các loại (mẫu) xe

mysql> create table models (id int(3) not null auto_increment primary key,
    -> model varchar(15));
Query OK, 0 rows affected (0,27 sec)

mysql> show columns from models;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| model | varchar(15) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0,00 sec)

mysql> insert into models (model) values ('Sports'), ('Sedan'), ('4WD'), ('Luxury');
Query OK, 4 rows affected (0,04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from models;
+----+--------+
| id | model  |
+----+--------+
|  1 | Sports |
|  2 | Sedan  |
|  3 | 4WD    |
|  4 | Luxury |
+----+--------+
4 rows in set (0,00 sec)

Và cuối cùng chúng ta sẽ tạo ra những chiếc xe với đầy đủ thông tin về màu sắc (color), hãng xe (brand), loại xe (model)

mysql> create table cars (id int(3) not null auto_increment primary key, 
    -> color int(3), brand int(3), model int(3));
Query OK, 0 rows affected (0,28 sec)

mysql> show columns from cars;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| id    | int(3) | NO   | PRI | NULL    | auto_increment |
| color | int(3) | YES  |     | NULL    |                |
| brand | int(3) | YES  |     | NULL    |                |
| model | int(3) | YES  |     | NULL    |                |
+-------+--------+------+-----+---------+----------------+
4 rows in set (0,00 sec)

mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1), 
    -> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1);
Query OK, 10 rows affected (0,04 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from cars;
+----+-------+-------+-------+
| id | color | brand | model |
+----+-------+-------+-------+
|  1 |     1 |     2 |     1 |
|  2 |     3 |     1 |     2 |
|  3 |     5 |     3 |     1 |
|  4 |     4 |     4 |     2 |
|  5 |     2 |     2 |     3 |
|  6 |     3 |     5 |     4 |
|  7 |     4 |     1 |     3 |
|  8 |     2 |     2 |     1 |
|  9 |     5 |     2 |     3 |
| 10 |     4 |     5 |     1 |
+----+-------+-------+-------+
10 rows in set (0,00 sec)

Bây giờ chúng ta đã có đủ dữ liệu để thực hiện các điều kiện join khác nhau và cùng đưa ra đánh giá. Giả sửa bảng cars chứa thông tin những chiếc xe mà tôi có. Bây giờ tôi muốn biết id của những chiếc xe và loại xe. Dữ liệu được lấy ra từ hai bảng là car và models. Trong bảng models thì loại Sports có id là 1. Vì vậy câu querey sẽ như sau:

select
    ID,
    model
from
    cars
        join models
            on model=ID

Câu truy vấn trông có vể tốt phải không? Tôi đã chọn id, model từ hai bảng cần thiết. Nhưng kết quả :

ERROR 1052 (23000): Column 'ID' in field list is ambiguous

Oh noes! Lỗi xảy ra vì tên cột (column_name) id, model đều tồn tại ở cả hai bảng car và models. Vì vậy câu truy vấn không thể hiểu được. Để khắc phục lỗi này có hai cách, một là sử dụng table_name.coulmn_name để chỉ định lấy cột nào ở bảng nào.

select
    cars.ID,
    models.model
from
    cars
        join models
            on cars.model=models.ID
            
+----+--------+
| id | model  |
+----+--------+
|  1 | Sports |
|  2 | Sedan  |
|  3 | Sports |
|  4 | Sedan  |
|  5 | 4WD    |
|  6 | Luxury |
|  7 | 4WD    |
|  8 | Sports |
|  9 | 4WD    |
| 10 | Sports |
+----+--------+
10 rows in set (0,00 sec)

một cách khác là dùng alias để ký hiệu thay tên bảng (sẽ rất hữu ích với những trường hợp tên bảng quá dài) cũng cho kết quả tương tự bên trên

select
    a.ID,
    b.model
from
    cars a
        join models b
            on a.model=b.ID

Quay lại với yêu cầu ban đầu, nhưng tôi muốn biết chính xác id của những chiếc xe loại Sports, khi đó tôi cần thêm vào câu truy vấn mệnh đề where. Nếu đã biết loại Sports có id = 1, thì mệnh đề where có thể là "id=1" hoặc "model='Sports'". Vì id được đánh chỉ mục và là khóa chính nên sẽ được sử dụng.

select
    a.ID,
    b.model
from
    cars a
        join models b
            on a.model=b.ID
where
    b.ID=1

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)

OK, bây giờ tối muốn biết thêm thông tin về màu sắc những chiếc xe thể thao (loại Sports) của tôi. (nếu đúng sẽ phải viết là "where b.model='Sports'", nhưng giả sử như tôi đã biết loại xe Sports có id = 1)

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
where
    b.ID=1

+----+--------+-------+
| id | model  | color |
+----+--------+-------+
|  1 | Sports | Red   |
|  3 | Sports | Black |
|  8 | Sports | Green |
| 10 | Sports | White |
+----+--------+-------+

4 rows in set (0.00 sec)

OK, lại quay lại với yêu cầu ban đầu, nhưng giờ tôi muốn biết thêm thông tin trong lựa chọn của mình. Cụ thể là tôi muốn xem thông tin id những chiếc xe, loại xe là Sports hoặc 4MD, màu sắc của chúng. Chúng ta có hai cách khác nhau để thực hiện điều này. Thứ nhất là thêm điều kiện "where models.id = 3" (giả sử như tôi đã biết loại xe 4WD có id=3)

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1
    or b.ID=3
    
    +----+--------+-------+
| id | model  | color |
+----+--------+-------+
|  1 | Sports | Red   |
|  3 | Sports | Black |
|  5 | 4WD    | Green |
|  7 | 4WD    | White |
|  8 | Sports | Green |
|  9 | 4WD    | Black |
| 10 | Sports | White |
+----+--------+-------+
7 rows in set (0,00 sec)

Cách thứ 2 là dung toán tử "union all" Truy vấn dưới đây sẽ trả về các xe loại Sports (id=1)

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1

Truy vấn dưới đây sẽ trả về các xe loại 4WD (id=3)

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=3

Toán tử "union all" để kết hợp kết quả tìm kiếm những chiếc xe có id, màu sắc, loại Sports (id=1) và những chiếc xe có id, màu sắc, loại 4WD (id=3)

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1
union all
select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=3
    
+----+--------+-------+
| id | model  | color |
+----+--------+-------+
|  1 | Sports | Red   |
|  3 | Sports | Black |
|  8 | Sports | Green |
| 10 | Sports | White |
|  5 | 4WD    | Green |
|  7 | 4WD    | White |
|  9 | 4WD    | Black |
+----+--------+-------+
7 rows in set (0,00 sec)

Như bạn thấy, kết quả của truy vấn đầu tiên được trả về trước, tiếp theo là kết quả của truy vấn thứ hai. Trong ví dụ này, nó sẽ dễ dàng hơn nhiều so với sử dụng truy vấn đầu tiên (sử dụng whre models.id=1 or models.id=3). Trong một vài trường hợp các truy vấn liên kết có thể là tuyệt vời cho các trường hợp cụ thể. Đây là một cách tuyệt vời để trả lại các kết quả cụ thể từ các bảng không dễ dàng kết hợp với nhau - hoặc cho các bảng hoàn toàn không liên quan. Tuy nhiên có một số quy tắc như sau:

• Các kiểu dữ liệu cột (type colum) từ truy vấn đầu tiên phải khớp với kiểu dữ liệu cột mọi truy vấn khác bên dưới.
• Tên của các cột (name column) từ truy vấn đầu tiên sẽ được sử dụng để xác định toàn bộ các kết quả.
• Số lượng cột trong mỗi truy vấn phải giống nhau.

Bây giờ, bạn có thể đang tự hỏi sự khác biệt giữa sử dụng union và union all là gì. Truy vấn với union sẽ loại bỏ các bản sao, trong khi union all thì không.

Đối với các ví dụ tiếp theo, tôi thêm một vài bản ghi vào các bảng. Tôi thêm bản ghi có thương hiệu là "Holden" vào bảng brands. Tôi cũng thêm một bản ghi vào bảng cars có giá trị color=12 - để nó không có tham chiếu trong bảng colors.

select
    a.brand
from
    brands a

+--------+
| brand  |
+--------+
| Ford   |
| Toyota |
| Nissan |
| Smart  |
| BMW    |
| Holden |
+--------+
6 rows in set (0.00 sec)

Bây giờ, khi tôi join với bảng cars, tôi nhận được kết quả sau:

select
    a.brand
from
    brands a
        join cars b
            on a.ID=b.brand
group by
    a.brand

+--------+
| brand  |
+--------+
| BMW    |
| Ford   |
| Nissan |
| Smart  |
| Toyota |
+--------+
5 rows in set (0.00 sec)

Tôi không thấy kết quả có hãng "Holden" mà tôi mới thêm khi nãy. Điều này là do điều kiện join tìm kiếm các bản ghi phù hợp trong cả hai bảng. Vì trong bảng cars không có thông tin (brand là hãng "Holden") nên kết quả trả về từ câu truy vấn sẽ không có. Để lấy tất kết quả trong một bảng cho dù chúng có được kết hợp trong các bảng khác hay không. Câu truy vấn với outer join sau đây sẽ giúp tôi làm được điều đó.

select
    a.brand
from
    brands a
        left outer join cars b
            on a.ID=b.brand
group by
    a.brand
    
+--------+
| brand  |
+--------+
| BMW    |
| Ford   |
| Holden |
| Nissan |
| Smart  |
| Toyota |
+--------+
6 rows in set (0,00 sec)    

Bây giờ giả sử tôi muốn biết đối với từng hãng xe tôi có bao nhiêu chiếc xe:

select
    a.brand,
    count(b.id) as countOfBrand
from
    brands a
        left outer join cars b
            on a.ID=b.brand
group by
    a.brand
    
+--------+-------------+
| brand  | count(b.id) |
+--------+-------------+
| BMW    |           2 |
| Ford   |           2 |
| Holden |           0 |
| Nissan |           1 |
| Smart  |           1 |
| Toyota |           4 |
+--------+-------------+
6 rows in set (0,00 sec)

Outer join có thể là : left join hoặc right join. Câu hỏi đặt ra tiếp theo là có hay không việc có thể hợp nhất các loại join. Câu trả lời là có.

select
    b.brand,
    c.color,
    count(a.id) as countOfBrand
from
    cars a
        right outer join brands b
            on b.ID=a.brand
        join colors c
            on a.color=c.ID
group by
    a.brand,
    c.color

+--------+-------+--------------+
| brand  | color | countOfBrand |
+--------+-------+--------------+
| Ford   | Blue  |            1 |
| Ford   | White |            1 |
| Toyota | Black |            1 |
| Toyota | Green |            2 |
| Toyota | Red   |            1 |
| Nissan | Black |            1 |
| Smart  | White |            1 |
| BMW    | Blue  |            1 |
| BMW    | White |            1 |
+--------+-------+--------------+
9 rows in set (0,01 sec)

Đây không phải là kết quả mong đợi? Đó là bởi vì mặc dù tôi đã chọn outer join from cars sang brands, nhưng nó không được chỉ định trong việc kết hợp với colors - do đó truy vấn chỉ mang lại kết quả phù hợp trong cả hai bảng. Đây là truy vấn có thể hoạt động để có được kết quả mà tôi mong đợi:

select
    a.brand,
    c.color,
    count(b.id) as countOfBrand
from
    brands a
        left outer join cars b
            on a.ID=b.brand
        left outer join colors c
            on b.color=c.ID
group by
    a.brand,
    c.color

+--------+-------+--------------+
| brand  | color | countOfBrand |
+--------+-------+--------------+
| BMW    | Blue  |            1 |
| BMW    | White |            1 |
| Ford   | Blue  |            1 |
| Ford   | White |            1 |
| Holden | NULL  |            0 |
| Nissan | Black |            1 |
| Smart  | White |            1 |
| Toyota | NULL  |            1 |
| Toyota | Black |            1 |
| Toyota | Green |            2 |
| Toyota | Red   |            1 |
+--------+-------+--------------+
11 rows in set (0.00 sec)

Như vừa thấy, tôi sử dụng 2 phép outer join và được kết quả như mong đợi. Vậy ngoài cách join thì còn cách nào khác không? Chúng ta có thể sử dụng intersection (giao nhau). Ví dụ:

select
    *
from
    colors
where
    ID>2
intersect
select
    *
from
    colors
where
    id<4

không phải tất cả nhưng hấu hết các loại csdl đều hỗ trợ "intersection". Intersection gần như là giống join, chỉ khác là nó chỉ trả về các bản ghi giống hệt nhau giữa các truy vấn khác nhau từ phép union. Một ví dụ đơn giản như sau:

select
    *
from
    colors
where
    ID>2
intersect
select
    *
from
    colors
where
    id<4

Trong khi một truy vấn union bình thường sẽ trả về tất cả các hàng của bảng (truy vấn đầu tiên trả về các bản ghi có ID> 2 và truy vấn thứ hai trả về bản ghi có ID <4) sẽ dẫn đến một tập đầy đủ, thì một truy vấn giao nhau sẽ chỉ trả lại bản ghi có ID= 3 vì nó là giao của 2 truy vấn. Bây giờ, nếu cơ sở dữ liệu của bạn không hỗ trợ một truy vấn intersection (truy vấn giao nhau), thì ta có thể thiết lập với truy vấn sau đây:

select
    a.ID,
    a.color,
    a.paint
from
    colors a
        join colors b
            on a.ID=b.ID
where
    a.ID>2
    and b.ID<4

+----+-------+----------+
| ID | color | paint    |
+----+-------+----------+
|  3 | Blue  | Metallic |
+----+-------+----------+
1 row in set (0.00 sec)

Xin nhắc lại là nếu bạn đang làm việc với CSDL không hỗ trợ truy vấn intersection (truy vấn giao nhau) thì hãy thiết lập phép join bảng.