Introduction
The purpose of this article is to show the power of MySQL JOIN operations, Nested MySQL Queries(intermediate or temporary result set table) and Aggregate functions(like GROUP BY). One can refer to link1 or link2 for basic understanding of MySQL join operations.
In order to explain various MySQL JOIN operations, I have chosen a problem domain of an idea management system. Let me first briefly describe this problem domain.
- System users: In this idea management system, there are set of of registered users who belongs to different departments. Basic requirement of this system is to keep track of all the ideas submitted by registered users.
- Idea: A single idea can originates from single or multiple users, so an idea can have single or multiple innovators accordingly. However, only one innovator can submit the idea and other can be listed as idea innovators.
- User role: User can have multiple role like normal user or admin user. Admin user can change the status of submitted idea from pending status(0) to accepted(1) or rejected(2).
- Idea status update: On status change, all the associated innovators receive some points as reward.
- Idea events generation: Based on idea progress, some events can be generated which are then fed into system as idea events by admin user.
- Generation of consolidated reports: Main objective of this idea management system is to process the user & idea information to show consolidated information like list of recent ideas, list of top users or top groups based on points to motivate users to submit more ideas.
In this article, I will explain, how to get this consolidated information in a single complex MySQL query using MySQL JOIN and Aggregate operators. I would suggest, first attempt the problems mentioned in this article yourself before looking into the actual MySQL queries in order to get better understanding.
This system contains following tables which we will be referring through out this article to solve the problems. For sample data, download the database dump and import it into your MySQL environment(see using the code section).
Let me first start with brief description of these tables.
- user_info:
| id | full_name | dep_id | points |
| jack | JACK D. | 2 | 2 |
| jackson | M.S. Jackson | 3 | 3 |
| alice | Alice W | 2 | 1 |
| bob | Bob S. | 2 | 2 |
This table list information of all registered users.
Attributes:
- id :- Auto generated id for user
- full_name :- Full name of user
- dep_id :- Department id of user which is a foreign key referring to 'id' field in dept_info table
- points :- Total points received by user through idea submission
- user_idea:
| idea_id | user_id | title | innovators | idea_categories | status | description |
| 1 | jack | Video Annotations | jack | 1;2 | 1 | Video Annotations Description |
| 2 | jack | Optimize waterfall model | jack;jackson | 3 | 0 | Optimize waterfall model Description |
| 3 | jackson | Automation | jackson | 1 | 1 | Automation Description |
| 4 | jackson | Design Patterns | jackson | 1 | 0 | Design Patterns Description |
| 5 | alice | Identify Video Objects | alice;jack | 2 | 1 | Identify Video Objects Description |
| 6 | bob | Tin Can LMS | bob | 1 | 1 | Tin Can LMS Description |
| 7 | bob | Text Summarization | bob | 2;3 | 0 | Text Summarization Description |
This table contains details of all the ideas submitted by registered users present in user_info table.
Attributes:
- idea_id :- Auto generated id for submitted idea
- user_id :- id of user who submitted the idea. It is a foreign key referring to 'id' field of user_info table
- title :- title of idea
- innovators :- Semicolon concatenated list string of all the user 'id's who invented this idea. For example, innovators ="1;2" means, user with id=1(i.e. jack) and user with id =2(i.e. jackson) are innovators of this idea.
- idea categories :- Semicolon concatenated list string of category 'id's (referring to category_id in idea_categories table) of all idea categories in which idea can belong. For example, idea_categories ="2;3" means idea belong to category with category_id =2 (i.e. Video) and category with category_id =3(i.e. Language Analysis).
- status :- acceptance status of idea (e.g. 0 implies accepted, 1 implies accepted & 2 implies rejected)
- description :- description of idea
- idea_events:
| idea_id | event_id | events |
| 1 | 1 | Ideation Phase |
| 1 | 2 | Implementaion Phase |
| 3 | 3 | Discussion Phase |
It contains event information associated with idea present in user_idea table.An idea can have zero or multiple idea events.
Attributes:
- idea_id :- Foreign key referring to 'idea_id' field of user_idea table
- event_id :- Auto generated id for event
- events :- Event description string
- idea_categories:
| category_id | category_name |
| 1 | Project Lifecycle |
| 2 | Video |
| 3 | Language Analysis |
This table contain information of all registered idea categories in which any submitted idea can belong.
Attributes:
- category_id :- Auto generated id for category
- category_name :- Category Name
- dept_info:
| id | name |
| 1 | Other |
| 2 | Development |
| 3 | Manager |
This table list all the user departments.
Attributes:
- id :- Auto generated id for department
- name :- Department Name
Using the code
Download & extract the source code. Import the sql_join.zip database into your MySQL environment.
mysql -u root < {path-of-extracted-folder}\sql_join.txt
You can also use online SQL practice tool like
http://sqlfiddle.com/ to build your database online and execute query on top of it. Just copy the contents of 'sql_join_sqlfiddle.txt' into left panel of sqlfiddle page and click "Build Schema" button. After schema is built, you can type in your MySQL queries in the right panel and click "Run SQL" button to see the result displayed in bottom panel.
Ok, lets try to get some consolidated information from our system starting with simpler one to explore power of inner join and inner queries.
Problem 1: Getting list of ideas with user information
Task: In this problem, we want to retrieve all the ideas with attributes (idea_id, title, status and innovators) which are present in user_idea table along with idea user information with attributes (user id and user full name) present in user_info table.
In order to get desired result, we have to join user_idea and user_info tables.
Concepts: Inner Join, Table aliases
Relevant Tables:user_info and user_idea
Expected Result:
| idea_id | user_id | full_name | title | innovators | status |
| 1 | jack | JACK D. | Video Annotations | jack | 1 |
| 2 | jack | JACK D. | Optimize waterfall model | jack;jackson | 0 |
| 3 | jackson | M.S. Jackson | Automation | jackson | 1 |
| 4 | jackson | M.S. Jackson | Design Patterns | jackson | 0 |
| 5 | alice | Alice W | Identify Video Objects | alice;jack | 1 |
| 6 | bob | Bob S. | Tin Can LMS | bob | 1 |
| 7 | bob | Bob S. | Another Idea1 | bob | 0 |
Solution:
Here is the appropriate query :
SELECT UI.idea_id, UI.user_id, UInfo.full_name, UI.title, UI.innovators, UI.status
FROM user_idea AS UI
INNER JOIN user_info AS UInfo ON UI.user_id = UInfo.id
Note: Here, we have used table aliases using "AS" to refer table fields more easily in a convenient way.
This was a simple example of Inner Join usage. Here, records from both user_idea and user_info table are merged based on common user_id.
Before moving to complex queries, let's try to understand SQL 'LIKE' operator which is primarily used to test if one string is a part of another string.
Problem 2: Fetch all accepted ideas for a specific user
Task: Here, we want to get list of all accepted ideas(i.e. idea with status = 1) for a particular user who is one of the innovators of those ideas.
Concepts: Use of Like operator to check if a given string is part of list represented as string where list elements are joined using some separator(e.g ';').
Relevant Tables: user_idea
Expected Result:
| idea_id | user_id | title | innovators |
| 1 | jack | Video Annotations | jack |
| 5 | alice | Identify Video Objects | alice;jack |
Solution:
Approach 1:
SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI
WHERE UI.status=1 AND UI.innovators like '%jack%';
Result:
| idea_id | user_id | title | innovators |
| 1 | jack | Video Annotations | jack |
| 3 | jackson | Automation | jackson |
| 5 | alice | Identify Video Objects | alice;jack |
Issues: If you examine this query carefully, you can realize that it might fetch wrong results if one user_id is substring of another. For e.g. above query will return idea 3 having innovators "jackson"(as jackson contain jack) which is not desired. This approach might be suited in situations where each user id is distinct and doesn't contain other user id as substring.
Approach 2: Append list separator(';') to the start and end of innovators string before using LIKE operator. It's kind of tricky way of matching user_id without using multiple'OR' operator in 'LIKE" statements to handle cases where innovator present at the start, middle or end of the innovators string.
Case 1: Appending list separator:
SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI
WHERE UI.status=1 AND CONCAT(';',UI.innovators,';') like '%;jack;%';
Alternatively, we could use following approaches to get the same result but, I prefer the preceding approach as it is more concise and faster.
Case 2: Using Multiple 'OR'
SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI
WHERE UI.status=1 AND
(
UI.innovators = 'jack' OR
UI.innovators like 'jack;%' OR
UI.innovators like '%;jack;%' OR
UI.innovators like '%;jack'
)
Case 3: Using Regular expression (REGEXP or RLIKE)
SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI
WHERE UI.status=1 AND UI.innovators REGEXP '^jack$|^jack;|;jack;|;jack$';
Case 4: Create function in the database incase this operation is used frequently
//Create Function isMember
DELIMITER $$
CREATE FUNCTION IsMember(inList Text, inMember varchar(10))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
DECLARE exp varchar(50) DEFAULT '';
SET exp = CONCAT('^',inMember,'$','|^',inMember,';|;','inMember',';|;',inMember,'$');
RETURN inList REGEXP exp;
END
$$
DELIMITER ;
//Using Function
SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI
WHERE UI.status=1 AND IsMember(UI.innovators,'jack');
Performance Analysis:
In General 'LIKE' is more optimized than 'REGEXP'(function in this case) for large dataset.
Below is the approximate execution time for queries against set of approx 70K user_ideas.
System Details:
Processor: (Intel Core i5 CPU M520 @2.4GHz 2.4GHz), OS: (Windows 7, 4GB RAM), MySQL Version: (5.5.24 for Win32 (x86)).
Query Execution Time:
- Case1 (CONCAT with LIKE): ~0.28sec
- Case2 (Multiple LIKE with OR): ~0.29sec
- Case3 (REGEXP): ~0.9sec
- Case4 (FUNCTION): ~8.6sec
Result:
| idea_id | user_id | title | innovators |
| 1 | jack | Video Annotations | jack |
| 5 | alice | Identify Video Objects | alice;jack |
Problem 3: Fetch idea details with idea categories names and idea events information
Task: This problem is little bit complex as it involves fetching concatenated data(e.g. idea events) from one table(e.g idea events table) and merging it with row fetched from other table(e.g. idea table)
Concepts: LEFT JOIN, INNER JOIN, Nested SQL query, Table aliases and Aggregate Functions like GROUP BY, GROUP_CONCAT
Relevant Tables: user_idea, idea_events, idea_categories
Expected Result:
| idea_id | title | events | categories |
| 1 | Video Annotations | Ideation Phase;Implementation Phase | Project Lifecycle;Video |
| 2 | Optimize waterfall model | NULL | Language Analysis |
| 3 | Automation | Discussion Phase | Project Lifecycle |
| 4 | Design Patterns | NULL | Project Lifecycle |
| 5 | Identify Video Objects | NULL | Video |
| 6 | Tin Can LMS | NULL | Project Lifecycle |
| 7 | Text Summarization | NULL | Video;Language Analysis |
Solution:
We can divide our problem in two parts. First, we will fetch all the events associated with each idea and merge into single concatenated field(named as 'events') using some separator(say ';'). Second, we will join the result of this query with idea_categories table to add idea category information.
Note: Idea events can be null. i.e. some idea may not have any associated event.
Step 1: Query to fetching Idea Events (concatenated using separator ';' )
Approach 1:
SELECT UI.idea_id, UI.title, GROUP_CONCAT(IE.events SEPARATOR ';') as events
FROM user_idea AS UI
INNER JOIN idea_events AS IE ON UI.idea_id = IE.idea_id
GROUP BY UI.idea_id
Note: GROUP BY and GROUP_CONCAT aggregate operators are used to club all events specific to single idea.
Result:
| idea_id | title | events |
| 1 | Video Annotations | Ideation Phase;Implementation Phase |
| 3 | Automation | Discussion Phase |
Issues: Ideas with no associated event are not present in results as INNER JOIN is used. We could resolve this problem in approach2 using LEFT JOIN.
Approach 2:
SELECT UI.idea_id, UI.title, GROUP_CONCAT(IE.events SEPARATOR ';') as events
FROM user_idea UI
LEFT JOIN idea_events IE ON UI.idea_id = IE.idea_id
GROUP BY UI.idea_id
Result:
| idea_id | title | events |
| 1 | Video Annoatations | Ideation Phase;Implementation Phase |
| 2 | Optimize waterfall model | NULL |
| 3 | Automation | Discussion Phase |
| 4 | Design Patterns | NULL |
| 5 | Identify Video Objects | NULL |
| 6 | Tin Can LMS | NULL |
| 7 | Text Summarization | NULL |
Note:
- We have used LEFT JOIN instead of INNER JOIN as we want all records of ideas even if there is no corresponding event.
- We are using GROUP_CONCAT and GROUP_BY to club events per idea in a single entry. Without GROUP_BY & GROUP_CONCAT operators, we would get multiple entries per idea as shown below (see first two row of result):
SELECT UI.idea_id, UI.title, IE.events
FROM user_idea UI
LEFT JOIN idea_events IE ON UI.idea_id = IE.idea_id;
Result(Without GROUP_BY):
| idea_id | title | events |
| 1 | Video Annoatations | Ideation Phase |
| 1 | Video Annoatations | Implementation Phase |
| 2 | Optimize waterfall model | NULL |
| ... | ... | ... |
However, if we use GROUP_BY without using GROUP_CONCAT, we won't get expected result. We will get one row per idea as we are grouping by idea_id with single event information that corresponds to that idea(missing other events) as we are not clubbing events using GROUP_CONCAT (see event column of row 1 in result. Only 'Ideation Phase' is coming and not the 'Implementation Phase'). Key rule is, one should use aggregate operators like GROUP_CONCAT, AVG, SUM, COUNT, MAX, MIN etc. whenever using GROUP_BY.
SELECT UI.idea_id, UI.title, IE.events
FROM user_idea UI
LEFT JOIN idea_events IE ON UI.idea_id = IE.idea_id
GROUP BY UI.idea_id;
Result(Without GROUP_CONCAT):
| idea_id | title | events |
| 1 | Video Annoatations | Ideation Phase |
| 2 | Optimize waterfall model | NULL |
| 3 | Automation | Discussion Phase |
| ... | ... | ... |
Step 2: Fetch Pillars Information by joining idea_categories tables with the results of query in Step1.
We will use SQL query in step 1 as inner or nested query for fetching category information.
SELECT AUI.idea_id, AUI.title, AUI.events,
GROUP_CONCAT(IC.category_name SEPARATOR ';') as categories
FROM (
SELECT UI.idea_id, UI.title, GROUP_CONCAT(IE.events SEPARATOR ';') as events ,
CONCAT(';',UI.idea_categories,';') as temp_categories
FROM user_idea UI
LEFT JOIN idea_events IE ON UI.idea_id = IE.idea_id
GROUP BY UI.idea_id
) AS AUI
INNER JOIN idea_categories IC
ON AUI.temp_categories LIKE CONCAT('%;',IC.category_id,';%')
GROUP BY AUI.idea_id;
Result:
| idea_id | title | events | categories |
| 1 | Video Annotations | Ideation Phase;Implementation Phase | Project Lifecycle;Video |
| 2 | Optimize waterfall model | NULL | Language Analysis |
| 3 | Automation | Discussion Phase | Project Lifecycle |
| 4 | Design Patterns | NULL | Project Lifecycle |
| 5 | Identify Video Objects | NULL | Video |
| 6 | Tin Can LMS | NULL | Project Lifecycle |
| 7 | Text Summarization | NULL | Video;Language Analysis |
Note:
- We have used nested query result set aliased as AUI to append category information. In general, nested query of following pattern are very useful when you want to peform join operation on the result set of some temporary or intermediate sql query.
SELECT T1.field1,..., NT.field1,...
FROM (
SELECT T2.field1, ..
FROM T2
WHERE ...
) AS NT
INNER JOIN T1 ON T1.someField = NT.someField && ....
WHERE ...
- We have applied techniques mentioned in Problem 2(Approach2-Case1) to get category names (this time we have used same technique in SELECT statement). However, we could use the same in conditional clause also). In nested query, we are appending list separator(';') to the user_idea.categories field and aliasing it as 'temp_categories' which will be used in conditional clause of outer query. The result set of nested query will have 4 columns i.e. 'idea_id', 'title', 'events' (concatenated event string) and 'temp_categories'. Now, this result set is used as temporary table aliased as 'AUI' which is then joined with idea_categories table using LIKE condition on temp_categories. GROUP_BY & GROUP_CONCAT are then used in outer SELECT query to get concatenated string of idea category names.
- Alternate way to get the same result could be, get idea events & idea categories separately and use inner join or intersection.
Problem 4: Get Top Innovator Groups based on some heuiristic like group point average.
Task: In this problem, we want to fetch leading or top groups information with attributes like total number of innovators in a leading group, total number of ideas submitted by innnovators in that group and total group points. Also, sort the result based on group point average.
Concepts: COUNT, SUM, GROUP BY, ORDER BY,DESC, Nested SQL query, JOIN
Relevant Tables: user_info, user_idea, dept_info
Expected Result:
| dept_id | dept_name | totalInnovators | totalIdeas | totalPoints |
| 3 | Manager | 1 | 2 | 3 |
| 2 | Development | 3 | 5 | 5 |
Solution:
We will divide this task also into two parts as done for Problem 3. First, we will join the user_info with dep_info to get user department or group information and join it with idea table to get total idea count submitted by each user. Second, we will join the result of previous step with dept_info to get total number of innovators, total ideas, total points per group using aggregate operators sorted by group point average
Step 1: Get user Department infomation joining user_pre_info and dept_info and count user ideas
SELECT UPI.id as user_id, UPI.dept_id, DI.name as dept_name, UPI.points, COUNT(UI.idea_id) AS totalIdeas
FROM user_info UPI
INNER JOIN dept_info AS DI ON UPI.dept_id=DI.id
LEFT JOIN user_idea AS UI ON UPI.id = UI.user_id
GROUP BY UPI.id
Result:
| user_id | dept_id | dept_name | points | totalIdeas |
| alice | 2 | Development | 1 | 1 |
| bob | 2 | Development | 2 | 2 |
| jack | 2 | Development | 2 | 2 |
| jackson | 3 | Manager | 3 | 2 |
Note:
- We have used COUNT operator to get idea count per user
- We have used GROUP BY operator to group based on user_id
Step 2: Count number of users, total ideas in a group and sort result by point average w.r.t number of users in a group
We will join the result of SQL query in step 1 with dept_info table to get the desired result.
SELECT UGI.dept_id, UGI.dept_name, COUNT(UGI.id) AS totalInnovators, SUM(UGI.totalIdeas) AS totalIdeas, SUM(UGI.points) AS totalPoints
FROM (
SELECT UPI.id, UPI.dept_id, DI.name as dept_name, UPI.points, COUNT(UI.idea_id) AS totalIdeas
FROM user_info AS UPI
INNER JOIN dept_info AS DI ON UPI.dept_id=DI.id
LEFT JOIN user_idea AS UI ON UPI.id = UI.user_id
GROUP BY UPI.id
) AS UGI
GROUP BY UGI.dept_id HAVING (totalPoints>0 AND totalInnovators>0) ORDER BY SUM(UGI.points)/COUNT(UGI.id) DESC LIMIT 5
Result:
| dept_id | dept_name | totalInnovators | totalIdeas | totalPoints |
| 3 | Manager | 1 | 2 | 3 |
| 2 | Development | 3 | 5 | 5 |
Note:
- we have grouped the result by department id to get total points per group.
- We have sorted the result by group point average using ORDER BY.
