You will learn how to write complex SQL queries by dividing the problem (result you want to acheive) and in which order you must think and write SQL clauses to simply your task.
This tutorial will not explain working of SQL clauses. I assume you understand working of all SQL clauses.
Thinking and writing SQL query clauses in particular order (in which database engine executes the query) matters a lot because problem (getting required data) gets much simplified by creating query in such an order. Actually the problem is automatically divided and simplified when writing query in execution order. Let’s take a look how the database engine executes the query.
The Source of Data
The first clause which gets executed is FROM clause. It means the first step when you write a query is to know FROM which table(s) you want to get the data. If the required data is in multiple tables, then think about the JOINs in first step. When FROM clause gets executed, all rows are fetched from the tables specified. Let’s call them Individual Rows.
Filter Individual Rows
Second step is to think about WHERE clause which is used to filter Individual Rows on particular condition(s).
Grouping of Individual Rows
GROUP BY is the third clause which gets executed. So think about it in the third step when writing a query. This clause group Individual Rows (fetched using FROM and filtered using WHERE) on the basis and order of columns specified.
Filter Group Rows
HAVING clause is used to filter Group Rows based on the condition(s) specified in it. It’s fourth one which gets executed.
The fifth clause which gets executed is SELECT. This clause is used to filter columns which are not required.
Ordering of Rows
The final step in execution of query is to order the rows based on specified column(s).
Let’s apply the above steps to solve following problem.
Count the total articles (whose status is published) posted by each author in WordPress database.
First step is to identify the tables from where we can find users and posts. We can solve above problem from two wordpress tables (wp_posts and wp_users).
We need to INNER JOIN both tables ON the basis of columns wp_users.ID and wp_posts.post_author. Both of them contains the user id so we can join them as shown below:
FROM wp_users u INNER JOIN wp_posts p ON u.ID = p.post_author
You can place SELECT * before FROM clause to test the results returned from this query. Also remember to test each clause throughly before moving to next one.
Remeber we need to COUNT only those posts whose status is ‘publish’. Actually in WordPress posts can have different states like published, draft, revision, and pending review. We need to filter Individual Rows using WHERE to get only those whose post_status is ‘publish’
SELECT * FROM wp_users u INNER JOIN wp_posts p ON u.ID = p.post_author WHERE p.post_status = 'publish'
Now we need to GROUP individual rows on the basis of wp_users.user_login (used to login WordPress admin panel and it’s unique) column because we want to make a single (Group) row for each user. Also by making a Group Row by user_login, we can COUNT the total number of posts (in SELECT clause later) added by each user.
SELECT * FROM wp_users u INNER JOIN wp_posts p ON u.ID = p.post_author WHERE p.post_status = 'publish' GROUP BY u.user_login
We can skip HAVING clause because we don’t need any filtration of Group Rows.
According to our requirement, we need to display only two columns using SELECT clause. First is the user_login and second is the COUNT of posts added by the user.
SELECT u.user_login, COUNT(p.ID) AS total_posts FROM wp_users u INNER JOIN wp_posts p ON u.ID = p.post_author WHERE p.post_status = 'publish' GROUP BY u.user_login
COUNT() is an aggregate function which is used to count the rows and in our case it counts the rows in each group, not the total rows fetched from the table. Remember that each group is created on the basis of user_login.
Finally we need to sort our results in ascending order using ORDER BY
SELECT u.user_login, COUNT(p.ID) AS total_posts FROM wp_users u INNER JOIN wp_posts p ON u.ID = p.post_author WHERE p.post_status = 'publish' GROUP BY u.user_login ORDER BY u.user_login
If your query invole subqueries then try writing them when writing a clause in which it will go. For example, if the subquery will go in FROM clause then try writing it in Step 1. Similarly if your subquery will go in SELECT clause, then try writing it in Step 5.
I want to remind you again that this article is not about learning SQL basics. It’s about the steps to write complex queries. Your problem will get much simplified if you follow these steps to write complex queries. Try writing more queries using these steps.