Common MySQL SELECT Commands: Multi‑Table Join Queries

This guide shows how to work with two MySQL tables ( bumen and yg ) and perform multi‑table SELECT queries.

First, the structures of the tables are displayed:

mysql> desc bumen;  # table 1
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| bumenName | varchar(20) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

mysql> desc yg; # table 2
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | bigint(10)  | NO   | PRI | NULL    | auto_increment |
| ygName | varchar(20) | YES  |     | NULL    |                |
| salary | float       | YES  |     | NULL    |                |
| bmID   | int(10)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

Sample data is inserted into both tables:

mysql> insert into bumen values (1,'研发部'),(2,'测试部'),(3,'UI'),(4,'市场');
mysql> insert into yg values (1,'田老板',20000,1), (2,'汪总',221000,1), (3,'李四',15000,2), (4,'大神',30000,3), (5,'销售',41321.9,4);

Basic SELECT statements display the contents of each table:

mysql> select * from yg;
+----+-----------+---------+------+
| id | ygName    | salary  | bmID |
+----+-----------+---------+------+
| 1  | 田老板    | 20000   | 1    |
| 2  | 汪总      | 221000  | 1    |
| 3  | 李四      | 15000   | 2    |
| 4  | 大神      | 30000   | 3    |
| 5  | 销售      | 41321.9 | 4    |
+----+-----------+---------+------+

mysql> select * from bumen;
+----+-----------+
| id | bumenName |
+----+-----------+
| 1  | 研发部    |
| 2  | 测试部    |
| 3  | UI        |
| 4  | 市场      |
+----+-----------+

Several multi‑table SELECT queries illustrate different join techniques:

-- Simple inner join showing all columns from both tables
mysql> select * from yg y, bumen b where y.bmID=b.id;

-- Selecting specific columns
mysql> select b.bumenName, y.ygName from yg y, bumen b where y.bmID=b.id;

-- Filtering by department name
mysql> select b.bumenName, y.ygName from yg y, bumen b where y.bmID=b.id and b.bumenName='研发部';

-- Using UNION to combine results from two departments
mysql> select y.ygName, y.salary, y.bmID, b.bumenName from bumen b, yg y where b.id=y.bmID and b.bumenName='研发部' union all select y.ygName, y.salary, y.bmID, b.bumenName from bumen b, yg y where b.id=y.bmID and b.bumenName='市场';

The article also shows a common mistake (referencing a non‑existent database) and how to correct the query.

Overall, the examples provide a practical reference for performing multi‑table SELECT operations, applying filters, and combining result sets in MySQL.