The MySQL intermediate tutorial covers some of intermediate techniques such as Aggregate Functions,GROUP BY, HAVING,SubQuery,Concatenation,UNION and joins.
Note: To start good practice please download our sample database and import it your mysql server. Here is download link: sampledb.zip
Aggregate Functions in MySQL
We use the Aggregate functions in order to get the summary of the data in a table without actually retrieve the value. Aggregate functions ignore null value when performing calculation except COUNT function. Aggregate functions are often used with GROUP BY clause of SELECT statement. Here are the most common aggregate functions which MySQL supports:
SUM function returns the sum of all values in an expression.
Let’s practice with OrderDetails table by following examples:
To get the total money for each selling product we just use the SUM function and group by product. Here is the query:
SELECT productCode,sum(priceEach * quantityOrdered) total FROM orderdetails GROUP by productCode
AVG is used to calculate average value of an expression. It ignores NULL values. We can use AVG function to calculate the average price of all products buy executing the following query.
SELECT AVG(buyPrice) average_buy_price FROM Products;
MAX and MIN Function
MAX function returns the maximum and MIN function returns the minimum value of the set of values in expression. As an example, we can use MIN and MAX function to retrieve the highest and lowest price product as follows:
SELECT MAX(buyPrice) highest_price, MIN(buyPrice) lowest_price FROM Products
COUNT function returns the count of the items in expression. We can use COUNT function to count how many products we have as follows:
SELECT COUNT(*) AS Total FROM products
MySQL GROUP BY
The MySQL GROUP BYclause is used with SQL SELECT statement to to group selected records into a set of summary records by the one or more column’s value or expression. Now, suppose you want to get groups for each order, you can use the MySQL GROUP BY clause as follows:
SELECT status FROM orders GROUP BY status
Display: It seems that the GROUP BY clause only scans for unique occurrences in the status column and return the result set. However if you look at the data of the orders table you will see that each row in result set above is summary of records that represent a group orders that have the same status on the status column.
MySQL GROUP BY with aggregate function
Aggregate functions are used with MySQL GROUP BY clause to perform calculation on each group of records on return a single value for each row. Let’s say if you want to know how many orders in each status group you can use the COUNT function as follows:
SELECT status, count(*) FROM orders GROUP BY status
The query counts number of orders for each order’s status.
MySQL GROUP BY vs. ANSI SQL GROUP BY
MySQL follows ANSI SQL. However, there are two differences the way GROUP BY works in MySQL and ANSI SQL.
- In ANSI SQL you must group by all columns you specifies in the SELECT clause. MySQL does not have this restriction. You can have additional columns in the SELECT clause that are not in the GROUP BY clause.
- MySQL also allows you to sort the group order in which the results are returned. The default order is ascending.
If you want to see the result of the query above in the descending order, you can do it as follows:
SELECT status, count(*) FROM orders GROUP BY status DESC;
As you see the status of orders now are in reverse alphabetical order. By default it is ascending order determined by ASC.
The MySQL HAVING clause is an optional part of and used only with the SQL SELECT statement. The MySQL HAVING clause specifies a filter condition for a group of record or an aggregate. The MySQL HAVING is often used with MySQL GROUP BY clause. When using with MYSQL GROUP BY clause, you can apply filter condition of the HAVING clause only to the columns appear in the GROUP BY clause. If the MySQL GROUP BY clause is omitted, the MySQL HAVING clause will behave like a WHERE clause. Notes that the MySQL HAVING clause applies to groups as a whole while the WHERE clause applies to individual rows.
Examples of MySQL HAVING clause
Let’s take a look at an example of using MySQL HAVING clause to have a better understanding.
We have orderDetails table in our sample database. We can use the MySQL GROUP BY clause to get all orders, number of items sold and total values in each order as follows:
SELECT ordernumber, sum(quantityOrdered) AS itemsCount, sum(priceeach) AS total FROM orderdetails GROUP BY ordernumber
Now you can ask what order has total value greater than $1600. In this case, you need to use the MySQL HAVING clause on aggregate to answer that question.
SELECT ordernumber, sum(quantityOrdered) AS itemsCount, sum(priceeach) AS total FROM orderdetails GROUP BY ordernumber HAVING total > 1600
SELECT ordernumber, sum(quantityOrdered) AS itemsCount, sum(priceeach) AS total FROM orderdetails GROUP BY ordernumber HAVING total > 1500 AND itemsCount > 600
The MySQL HAVING clause is useful only with the MySQL GROUP BY clause for building output of high-level reports. For example, you can use the MySQL HAVING clause to answer questions like how many order has total values more than 1600 this month, this quarter and this year?…
We use column alias for the aggregate sum(priceeach) as total so in the HAVING clause we just have to specify that column alias total instead of typing the aggregate sum(priceeach) again.
You can use a complex condition in the MySQL HAVING clause such as OR, AND operators. For example if you want to know what order has total value greater than $1000 and has more than 10 items in it. You can use the following query to find out:
Subquery is a query that embeds into other queries. We need subquery because we want to capture the return query from other tables as the input in the WHERE clause in the main SELECT statement. Usually, subqueries have relationship between other tables that we need to query. For example, we have the orders table, and the orderDetails table. It’s a relationship between orders and orderDetails table. Most subqueries are used in the WHERE clause and with the IN operators.
SELECT orderNumber,customerNumber,status,shippedDate FROM orders WHERE orderNumber IN ( SELECT orderNumber FROM orderDetails) LIMIT 10
MySQL SubQuery with Group By and Having
For example, if you want to find out all orders in the orders table which have total cost greater than $60000, we can use SQL IN with sub-query.
First to select all the orders which has total cost greater than $60000, you can retrieve it from orderDetails table as follows:
SELECT orderNumber,customerNumber,status,shippedDate FROM orders WHERE orderNumber IN ( SELECT orderNumber FROM orderDetails GROUP BY orderNumber HAVING SUM(quantityOrdered * priceEach) > 60000);
You get all the orders which have total cost greater than $60000
We use the concatenation operation to combine field from different columns in the same table or from a different table.
Here are several examples to demonstrate the concat function. We will use the sample database to practice.
In order to display the first 5 full name of contacts of the customers we use concat function to concatenate the first name and last name and a separator between them. Here is the query:
SELECT CONCAT(contactLastname,', ',contactFirstname) fullname FROM customers LIMIT 5;
Concat with Separator Function
MySQL also supports concat_ws function which allows us to concatenate two or more than two strings with a predefined separator. The syntax of the concat_ws function is:
The first parameter is the predefined separator you specified and the others are the string you want to concatenate. the result is the concatenating string with separator between each. For example, you can achieve the same result in the above example by using concat_ws function instead of concat function.
Here is example of using concat_ws to get address format of customers.
SELECT CONCAT_WS(char(13), CONCAT_WS(' ',contactLastname,contactFirstname), addressLine1, addressLine2, CONCAT_WS(' ',postalCode,city), country, CONCAT_WS(char(13),'') ) AS Customer_Address FROM customers LIMIT 2
We use UNION operator in order to combine multiple SELECT statements. However, we also can use the WHERE clause instead of UNION to combine the return SELECT statements. We need to make our own decision when to use WHERE clause or when to use the UNION operator to combine the return SELECT statement. It is totally depending on the situation.
- In order to use the UNION Operator, we need least two tables
- All the query that use the UNION Operator must have the same columns, aggregate functions, and expressions
- UNION operator is automatically removed the duplicate return rows
- UNION ALL will return all the rows included the duplicate rows
Suppose you want to combine customers and employees infomation into one result set, you use the following query:
SELECT customerNumber id, contactLastname name FROM customers UNION SELECT employeeNumber id,firstname name FROM employees;
When using ORDER BY to sort the result with UNION, you have to use it in the last SQL SELECT statement. It would be the best to parenthesize all the SELECT statements and place ORDER BY at the end.
Suppose you want to sort the combination of employees and customers in the query above by name andID in ascending order.
(SELECT customerNumber id,contactLastname name FROM customers) UNION (SELECT employeeNumber id,firstname name FROM employees) ORDER BY name,id;
What will be displayed in the output if we don’t use alias for each column in the SELECT statements? MySQL will use the column names of the first SELECT statement as the label of the output.
(SELECT customerNumber, contactLastname FROM customers) UNION (SELECT employeeNumber, firstname FROM employees) ORDER BY contactLastname, customerNumber;
MySQL also provides you another option to sort the result set based on column position in the ORDER BY clause as the following query:
(SELECT customerNumber, contactLastname FROM customers) UNION (SELECT employeeNumber,firstname FROM employees) ORDER BY 2, 1;
MySQL INNER JOIN
Inner join is a simple join which is based on two or multiple tables. We use the keyword INNER JOIN ON to make the inner join table. WE also can use the WHERE clause to make the join between tables which we can get the same return data as we use the INNER JOIN ON.
Before using MySQL INNER JOIN clause, you have to specify the following criteria:
- First, you need to specify the tables you want to join with the main table. The main table appear in the FROM clause. The table you want to join with appear after keyword INNER JOIN. Theoretically, you can join a table with unlimited number of tables. However, for better performance you should limit the number of tables to join based on join conditions and volume of data in those tables.
- Second, you need to specify the join condition or join predicate. The join condition appears after the keyword ON of MySQL INNER JOIN clause. The join condition is the rule for matching rows between the main table and other tables being joined with.
The syntax of the MySQL INNER JOIN is as follows:
SELECT column_list FROM t1 INNER JOIN t2 ON join_condition1 INNER JOIN t3 ON join_condition2 ... WHERE where_conditions;
For example, if you join two tables A and B, the MySQL INNER JOIN clause compares each record of the table A with each record of table B to find all pair of records that satisfy the join-condition. When the join-condition are satisfied, column values for each matched pair of record of table A and table B are combined into a returned record. Note that the records on both tables have to match based on the join-condition. If no record on both table A and B matches, the query will return an empty result.
Avoid column ambiguous error in MySQL INNER JOIN
If you join multiple tables that has column with similar name, you have to use table qualifier to refer to column to avoid column ambiguous error. Suppose if table tbl_A and tbl_B has the same column M. In the SELECT statement with MySQL INNER JOIN clause, you have to refer to column M by using the table qualifier as tbl_A.M or tbl_B.M (table_name.column_name).
Another effective way to avoid column ambiguous is by using table alias. For example, you can give A as the table alias of the table tbl_A and refer to the column M as A.M so you don’t have to type again and again the long table name in your SQL statement.
Example of MySQL INNER JOIN clause
Let’s take a look at two tables: products and orderDetails in our sample database.
The products table is the master data table that stores all products. Whenever a product is sold, it is stored in the orderDetails table with other information. The link between products table and orderDetails table is productCode.
Now, if you want to know what product was sold in which order, you can use the MySQL INNER JOIN clause as follows:
SELECT A.productCode, A.productName, B.orderNumber FROM products A INNER JOIN orderDetails B on A.productCode = B.productCode LIMIT 10;
The MySQL INNER JOIN clause compares each row of table products and orderDetails table to find a pair of rows that has the same productCode. If a pair of rows that have the same, the product code, product name and order number are combined into a returned row.
MySQL LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
The MySQL LEFT JOIN clause consists of LEFT JOIN keyword followed by the second table you want to join. Next element is the ON keyword followed by the join condition. In the join condition, you specify the columns to be used for matching row in the two tables. The syntax of MySQL is as follows:
SELECT t1.c1, t1.c2,…t2.c1,t2.c2 FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 …(join_condition) WHERE where_condition
The MySQL LEFT JOIN clause works like this: when a row from the left table matches a row from the right table based on join_condition, the row’s content are selected as an output row. When row in the left table has no match it is still selected for output, but combined with a “fake” row from the right table that contains NULL in all columns. In short, the MySQL LEFT JOIN clause allows you to select all rows from the left table even there is no match for them in the right table.
Let’s take a look at two table customers and orders: If you want to know which customer has which order and each order’s status. You can use the MySQL LEFT JOIN as follows:
SELECT c.customerNumber, customerName,orderNUmber, o.status FROM customers c LEFT JOIN orders o ON c.customerNumber = o.customerNumber LIMIT 10;
The left table is the customers table so you see all customers are listed as the way MySQL LEFT JOIN clause works. However, there are rows that we have customer information but all order information are NULL. This means those customers do not have any order in our database. The MySQL LEFT JOIN clause is very useful when you want to find the records in the left table that are unmatched by the right table. You can accomplish this by add a WHERE clause to select only that have NULL values in a right table column. So to find all customers who does not have any order in our database we can use the MySQL LEFT JOIN clause as follows:
MySQL RIGHT JOIN
The RIGHT JOIN keyword returns all rows from the RIGHT table (table_name2), even if there are no matches in the LEFT table (table_name1).
The MySQL RIGHT JOIN clause consists of RIGHT JOIN keyword followed by the second table you want to join. Next element is the ON keyword followed by the join condition. In the join condition, you specify the columns to be used for matching row in the two tables. The syntax of MySQL is as follows:
SELECT t1.c1, t1.c2,…t2.c1,t2.c2 FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1 …(join_condition) WHERE where_condition
Let’s take a look at two table customers and orders: If you want to know which customer has which order and each order’s status. You can use the MySQL RIGHT JOIN as follows:
SELECT c.customerNumber, customerName,orderNUmber, o.status FROM customers c RIGHT JOIN orders o ON c.customerNumber = o.customerNumber LIMIT 10;
RIGHT JOIN is opposite the LEFT JOIN