Multi-Table Query Techniques in MySQL

Multi-Table Relationships

When designing relational schemas for applications, tables often relate due to business logic. Common relationship types include:

  • One-to-Many (or Many-to-One)
  • Many-to-Many
  • One-to-One

One-to-Many

Example: Department–Employee association.

  • A department can have multiple employees; each employee belongs to one department.
  • Implementation: Add a foreign key in the employee table referencing the department's primary key.

Many-to-Many

Example: Student–Course selection.

  • A student may enroll in many courses; a course can be taken by many students.
  • Implementation: Introduce a junction table containing foreign keys referencing both student and course primary keys.
CREATE TABLE learner (
  stu_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Student ID',
  full_name VARCHAR(10) COMMENT 'Name',
  student_no VARCHAR(10) COMMENT 'Student Number'
) COMMENT 'Learner Table';

INSERT INTO learner VALUES 
(NULL, 'Dai Qisi', '2000100101'),
(NULL, 'Xie Xun', '2000100102'),
(NULL, 'Yin Tianzheng', '2000100103'),
(NULL, 'Wei Yixiao', '2000100104');

CREATE TABLE subject (
  subj_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Subject ID',
  title VARCHAR(10) COMMENT 'Subject Name'
) COMMENT 'Subject Table';

INSERT INTO subject VALUES 
(NULL, 'Java'), (NULL, 'PHP'), (NULL, 'MySQL'), (NULL, 'Hadoop');

CREATE TABLE learner_subject (
  link_id INT AUTO_INCREMENT PRIMARY KEY,
  sid INT NOT NULL COMMENT 'Student ID',
  cid INT NOT NULL COMMENT 'Subject ID',
  CONSTRAINT fk_sid FOREIGN KEY (sid) REFERENCES learner(stu_id),
  CONSTRAINT fk_cid FOREIGN KEY (cid) REFERENCES subject(subj_id)
) COMMENT 'Enrollment Link Table';

INSERT INTO learner_subject VALUES 
(NULL, 1, 1), (NULL, 1, 2), (NULL, 1, 3), (NULL, 2, 2), (NULL, 2, 3), (NULL, 3, 4);

One-to-One

Example: User–UserDetail split.

  • Used to separate core fields from extended ones for performance.
  • Implementation: Place a unique foreign key in either table referencing the other's primary key.
CREATE TABLE base_user (
  uid INT AUTO_INCREMENT PRIMARY KEY COMMENT 'User ID',
  full_name VARCHAR(10) COMMENT 'Name',
  age_val INT COMMENT 'Age',
  gender CHAR(1) COMMENT 'M: Male, F: Female',
  mobile CHAR(11) COMMENT 'Phone'
) COMMENT 'Base User Info';

CREATE TABLE edu_detail (
  edu_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Education ID',
  degree VARCHAR(20) COMMENT 'Degree',
  specialty VARCHAR(50) COMMENT 'Major',
  pri_school VARCHAR(50) COMMENT 'Primary School',
  mid_school VARCHAR(50) COMMENT 'Middle School',
  uni_name VARCHAR(50) COMMENT 'University',
  ref_uid INT UNIQUE COMMENT 'User ID',
  CONSTRAINT fk_refuid FOREIGN KEY (ref_uid) REFERENCES base_user(uid)
) COMMENT 'Education Details';

INSERT INTO base_user(uid, full_name, age_val, gender, mobile) VALUES
(NULL,'Huang Bo',45,'M','18800001111'),
(NULL,'Bing Bing',35,'F','18800002222'),
(NULL,'Ma Yun',55,'M','18800008888'),
(NULL,'Li Yanhong',50,'M','18800009999');

INSERT INTO edu_detail(edu_id, degree, specialty, pri_school, mid_school, uni_name, ref_uid) VALUES
(NULL,'Bachelor','Dance','Jingan First Primary','Jingan First Middle','Beijing Dance Academy',1),
(NULL,'Master','Acting','Chaoyang First Primary','Chaoyang First Middle','Beijing Film Academy',2),
(NULL,'Bachelor','English','Hangzhou First Primary','Hangzhou First Middle','Hangzhou Normal University',3),
(NULL,'Bachelor','Applied Math','Yangquan First Primary','Yangquan First Middle','Tsinghua University',4);

Multi-Table Query Fundamentals

Sample Data Setup

CREATE TABLE division (
  div_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Division ID',
  div_name VARCHAR(50) NOT NULL COMMENT 'Division Name'
) COMMENT 'Division Table';

INSERT INTO division(div_id, div_name) VALUES 
(1,'R&D'), (2,'Marketing'), (3,'Finance'), (4,'Sales'), (5,'Exec Office'), (6,'HR');

CREATE TABLE worker (
  w_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Worker ID',
  w_name VARCHAR(50) NOT NULL COMMENT 'Name',
  w_age INT COMMENT 'Age',
  role VARCHAR(20) COMMENT 'Position',
  pay INT COMMENT 'Salary',
  start_date DATE COMMENT 'Start Date',
  lead_id INT COMMENT 'Manager ID',
  div_ref INT COMMENT 'Division ID',
  CONSTRAINT fk_div FOREIGN KEY (div_ref) REFERENCES division(div_id)
) COMMENT 'Worker Table';

INSERT INTO worker(w_id, w_name, w_age, role, pay, start_date, lead_id, div_ref) VALUES
(1,'Jin Yong',66,'CEO',20000,'2000-01-01',NULL,5),
(2,'Zhang Wuji',20,'PM',12500,'2005-12-05',1,1),
(3,'Yang Xiao',33,'Dev',8400,'2000-11-03',2,1),
(4,'Wei Yixiao',48,'Dev',11000,'2002-02-05',2,1),
(5,'Chang Yuchun',43,'Dev',10500,'2004-09-07',3,1),
(6,'Xiao Zhao',19,'Encourager',6600,'2004-10-12',2,1),
(7,'Miejue',60,'Finance Head',8500,'2002-09-12',1,3),
(8,'Zhou Zhiruo',19,'Accountant',48000,'2006-06-02',7,3),
(9,'Ding Minjun',23,'Cashier',5250,'2009-05-13',7,3),
(10,'Zhao Min',20,'Marketing Head',12500,'2004-10-12',1,2),
(11,'Lu Zhangke',56,'Clerk',3750,'2006-10-03',10,2),
(12,'He Bipeng',19,'Clerk',3750,'2007-05-09',10,2),
(13,'Fang Dongbai',19,'Clerk',5500,'2009-02-12',10,2),
(14,'Zhang Sanfeng',88,'Sales Head',14000,'2004-10-12',1,4),
(15,'Yu Lianzhou',38,'Sales',4600,'2004-10-12',14,4),
(16,'Song Yuanqiao',40,'Sales',4600,'2004-10-12',14,4),
(17,'Chen Youliang',42,NULL,2000,'2011-10-12',1,NULL);

Querying across multiple tables without conditions yields a Cartesian product — every combination of rows. To avoid irrelevant pairings, specify join conditions:

SELECT * FROM worker, division WHERE worker.div_ref = division.div_id;

Join Types

Inner Join

Returns rows with matching keys in both tables.

Implicit form:

SELECT cols FROM tbl_a, tbl_b WHERE tbl_a.key = tbl_b.key;

Explicit form:

SELECT cols FROM tbl_a JOIN tbl_b ON tbl_a.key = tbl_b.key;

Example – worker name with division name:

SELECT w.w_name, d.div_name FROM worker w, division d WHERE w.div_ref = d.div_id;
SELECT w.w_name, d.div_name FROM worker w JOIN division d ON w.div_ref = d.div_id;

Alias usage simplifies syntax; once aliased, original table names cannot be used for columns.

Outer Join

Left outer join returns all rows from the left table plus matches from the right:

SELECT cols FROM tbl_left LEFT JOIN tbl_right ON condition;

Right outer join returns all rows from the right table plus matches from the left:

SELECT cols FROM tbl_left RIGHT JOIN tbl_right ON condition;

Example – all workers with division info:

SELECT w.*, d.div_name FROM worker w LEFT JOIN division d ON w.div_ref = d.div_id;

All divisions with worker info:

SELECT d.*, w.* FROM worker w RIGHT JOIN division d ON w.div_ref = d.div_id;

Self Join

Join a table with itself using aliases:

SELECT cols FROM tbl AS a JOIN tbl AS b ON condition;

Find worker and their manager:

SELECT a.w_name AS worker, b.w_name AS manager FROM worker a JOIN worker b ON a.lead_id = b.w_id;

Include workers with out managers:

SELECT a.w_name AS worker, b.w_name AS manager FROM worker a LEFT JOIN worker b ON a.lead_id = b.w_id;

Union Queries

Combine results of multiple SELECTs:

SELECT cols FROM tbl_a ...
UNION [ALL]
SELECT cols FROM tbl_b ...;

UNION ALL keeps duplicates; UNION removes them. Column count and types must match.

Example – salary below 5000 or age above 50:

SELECT * FROM worker WHERE pay < 5000
UNION ALL
SELECT * FROM worker WHERE w_age > 50;

Subqueries

Nested SELECT statements used within another SQL command.

Scalar Subquery

Returns a single value.

Find workers in Sales division:

SELECT * FROM worker WHERE div_ref = (SELECT div_id FROM division WHERE div_name = 'Sales');

Column Subquery

Returns a list of values.

Workers in Sales or Marketing:

SELECT * FROM worker WHERE div_ref IN (SELECT div_id FROM division WHERE div_name IN ('Sales','Marketing'));

Employees earning more than every Finance staff:

SELECT * FROM worker WHERE pay > ALL (SELECT pay FROM worker WHERE div_ref = (SELECT div_id FROM division WHERE div_name='Finance'));

Row Subquery

Returns a single row of multiple columns.

Match worker with same salary and manager as Zhang Wuji:

SELECT * FROM worker WHERE (pay, lead_id) = (SELECT pay, lead_id FROM worker WHERE w_name='Zhang Wuji');

Table Subquery

Returns a result set usable as a derived table.

Find workers hired after 2006-01-01 with division data:

SELECT e.*, d.* FROM (SELECT * FROM worker WHERE start_date > '2006-01-01') e LEFT JOIN division d ON e.div_ref = d.div_id;

Practical Multi-Table Scenarios

CREATE TABLE pay_scale (
  level_no INT,
  min_pay INT,
  max_pay INT
) COMMENT 'Pay Grade Table';

INSERT INTO pay_scale VALUES 
(1,0,3000),(2,3001,5000),(3,5001,8000),(4,8001,10000),(5,10001,15000),(6,15001,20000),(7,20001,25000),(8,25001,30000);
  1. Worker name, age, role, division (implicit inner join):
SELECT w.w_name, w.w_age, w.role, d.div_name FROM worker w, division d WHERE w.div_ref = d.div_id;
  1. Workers under 30 with division (explicit inner join):
SELECT w.w_name, w.w_age, w.role, d.div_name FROM worker w JOIN division d ON w.div_ref = d.div_id WHERE w.w_age < 30;
  1. Divisions with at least one worker:
SELECT DISTINCT d.div_id, d.div_name FROM worker w, division d WHERE w.div_ref = d.div_id;
  1. Workers over 40 with division (include unassigned):
SELECT w.*, d.div_name FROM worker w LEFT JOIN division d ON w.div_ref = d.div_id WHERE w.w_age > 40;
  1. Worker salary grade:
SELECT w.*, p.level_no, p.min_pay, p.max_pay FROM worker w, pay_scale p WHERE w.pay BETWEEN p.min_pay AND p.max_pay;
  1. R&D workers with salary grade:
SELECT w.*, p.level_no FROM worker w JOIN division d ON w.div_ref = d.div_id JOIN pay_scale p ON w.pay BETWEEN p.min_pay AND p.max_pay WHERE d.div_name='R&D';
  1. Average salary of R&D:
SELECT AVG(w.pay) FROM worker w JOIN division d ON w.div_ref = d.div_id WHERE d.div_name='R&D';
  1. Workers earning more than Miejue:
SELECT * FROM worker WHERE pay > (SELECT pay FROM worker WHERE w_name='Miejue');
  1. Workers earning above average:
SELECT * FROM worker WHERE pay > (SELECT AVG(pay) FROM worker);
  1. Workers earning less then their division's average:
SELECT * FROM worker w1 WHERE w1.pay < (SELECT AVG(w2.pay) FROM worker w2 WHERE w2.div_ref = w1.div_ref);
  1. Division info with employee count:
SELECT d.div_id, d.div_name, (SELECT COUNT(*) FROM worker w WHERE w.div_ref = d.div_id) AS staff_count FROM division d;
  1. Learner course enrollment:
SELECT l.full_name, l.student_no, s.title FROM learner l JOIN learner_subject ls ON l.stu_id = ls.sid JOIN subject s ON ls.cid = s.subj_id;

Tags: MySQL Multi-Table Query JOIN subquery Database Design

Posted on Sun, 30 Aug 2026 16:52:43 +0000 by skorp

Hot Tags

©2026 Freaks City