MySQL part 2

UNION vs. JOIN

A JOIN combines result sets horizontally, a UNION appends result set vertically.

Inserting rows using default value

CREATE TABLE IF NOT EXISTS tasks ( task_id INT AUTO_INCREMENT, title VARCHAR(255) NOT NULL, start_date DATE, due_date DATE, priority TINYINT NOT NULL DEFAULT 3, description TEXT, PRIMARY KEY (task_id) ); INSERT INTO tasks(title,priority) VALUES('Understanding DEFAULT keyword in INSERT statement',DEFAULT); Because the default value for the column priority is 3 as declared in the table definition

INSERT IGNORE

CREATE TABLE subscribers ( id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(50) NOT NULL UNIQUE ); INSERT INTO subscribers(email) VALUES('[email protected]'); INSERT INTO subscribers(email) VALUES('[email protected]'), ('[email protected]'); --returns an error INSERT IGNORE INTO subscribers(email) VALUES('[email protected]'), -- returns a warning ('[email protected]'); --still inserts If you query data from subscribers table, you will find that only one row was actually inserted and the row that causes the error was not.

using union

INSERT INTO t1 VALUES (1),(2),(3); INSERT INTO t2 VALUES (2),(3),(4); //assume empty tables SELECT id FROM t1 UNION SELECT id FROM t2; --1, 2, 3, 4 Because the rows with value 2 and 3 are duplicates, the UNION removed them and kept only unique values. if you use the UNION ALL explicitly, the duplicate rows, if available, remain in the result. Because UNION ALL does not need to handle duplicates, it performs faster than UNION DISTINCT

Inserting multiple rows

INSERT INTO tasks(title, priority) VALUES ('My first task', 1), ('It is the second task',2), ('This is the third task of the week',3); Note that when you insert multiple rows and use the LAST_INSERT_ID() function to get the last inserted id of an AUTO_INCREMENT column, you will get the id of the first inserted row only, not the id of the last inserted row.

To differentiate between employees and customers in a union, you can add a column as shown in the following query

SELECT CONCAT(firstName, ' ', lastName) fullname, 'Employee' as contactType FROM employees UNION SELECT CONCAT(contactFirstName, ' ', contactLastName), 'Customer' as contactType FROM customers ORDER BY fullname

the following subquery finds the maximum, minimum and average number of items in sale orders (each item has its own unique orderNumber)

SELECT MAX(items), MIN(items), FLOOR(AVG(items)) FROM (SELECT orderNumber, COUNT(orderNumber) AS items FROM orderdetails GROUP BY orderNumber) AS lineitems; Note that the FLOOR() is used to remove decimal places from the average values of items.

In addition to columns, you can group rows by expressions. The following query gets the total sales for each year.

SELECT YEAR(orderDate) AS year, SUM(quantityOrdered * priceEach) AS total FROM orders INNER JOIN orderdetails USING (orderNumber) WHERE status = 'Shipped' GROUP BY YEAR(orderDate); In this example, we used the YEAR function to extract year data from order date ( orderDate). We included only orders with shipped status in the total sales. Note that the expression which appears in the SELECT clause must be the same as the one in the GROUP BY clause.

Using MySQL GROUP BY with HAVING clause example To filter the groups returned by GROUP BY clause, you use a HAVING clause. The following query uses the HAVING clause to select the total sales of the years after 2003.

SELECT YEAR(orderDate) AS year, SUM(quantityOrdered * priceEach) AS total FROM orders INNER JOIN orderdetails USING (orderNumber) WHERE status = 'Shipped' GROUP BY year HAVING year > 2003;

Suppose that you want to find all orders that are in shipped status and have the total amount greater than 1500, you can join the orderdetails table with the orders table using the INNER JOIN clause and apply a condition on status column and total aggregate as shown in the following query

SELECT a.ordernumber, status, SUM(priceeach*quantityOrdered) total FROM orderdetails a INNER JOIN orders b ON b.ordernumber = a.ordernumber GROUP BY ordernumber, status HAVING status = 'Shipped' AND total > 1500; The HAVING clause is only useful when you use it with the GROUP BY clause to generate the output of the high-level reports. For example, you can use the HAVING clause to answer questions like finding the number orders this month, this quarter, or this year that have total sales greater than 10K.

MySQL UNION and ORDER BY

SELECT concat(firstName,' ',lastName) fullname FROM employees UNION SELECT concat(contactFirstName,' ',contactLastName) FROM customers ORDER BY fullname;

you can use a subquery with NOT IN operator to find the customers who have not placed any orders as follows:

SELECT customerName FROM customers WHERE customerNumber NOT IN (SELECT DISTINCT customerNumber FROM orders);

the following query returns the customer who has the highest payment. (Assume all data are in the same table)

SELECT customerNumber, checkNumber, amount FROM payments WHERE amount = (SELECT MAX(amount) FROM payments);

For example, you can find customers whose payments are greater than the average payment using a subquery(Assume all data are in the same table)

SELECT customerNumber, checkNumber, amount FROM payments WHERE amount > (SELECT AVG(amount) FROM payments);

The following query finds sales orders whose total values are greater than 60K. (assume data is located in two tables) You can use the query as a correlated subquery to find customers who placed at least one sales order with the total value greater than 60K by using the EXISTS operator:

SELECT customerNumber, customerName FROM customers WHERE EXISTS( SELECT orderNumber, SUM(priceEach * quantityOrdered) FROM orderdetails INNER JOIN orders USING (orderNumber) WHERE customerNumber = customers.customerNumber GROUP BY orderNumber HAVING SUM(priceEach * quantityOrdered) > 60000);

How to avoid printing NULLs in rows with total sum values //IF statement

SELECT orderYear, productLine, SUM(orderValue) totalOrderValue, GROUPING(orderYear), -- returns 1 when sum calculation puts NULL in orderYear GROUPING(productLine) -- returns 1 when sum calculation puts NULL in productLine FROM sales GROUP BY orderYear, productline WITH ROLLUP; https://sp.mysqltutorial.org/wp-content/uploads/2018/08/MySQL-ROLLUP-GROUPING-function-example.png SELECT IF(GROUPING(orderYear), 'All Years', orderYear) orderYear, IF(GROUPING(productLine), 'All Product Lines', productLine) productLine, SUM(orderValue) totalOrderValue FROM sales... https://sp.mysqltutorial.org/wp-content/uploads/2018/08/MySQL-ROLLUP-GROUPING-function-substitution.png

How to calculate sales of each separate product and total sales

SELECT productLine, SUM(orderValue) totalOrderValue FROM sales GROUP BY productline WITH ROLLUP;

To get the total amount of all orders by status, you join the orders table with the orderdetails table and use the SUM function to calculate the total amount.

SELECT status, SUM(quantityOrdered * priceEach) AS amount FROM orders INNER JOIN orderdetails USING (orderNumber) GROUP BY status;

To set a new value for the max_allowed_packet variable, you use the following statement:

SET GLOBAL max_allowed_packet=size; where size is an integer that represents the number the maximum allowed packet size in bytes. Note that the max_allowed_packet has no influence on the INSERT INTO .. SELECT statement. The INSERT INTO .. SELECT statement can insert as many rows as you want.

INSERT multiple rows limit

SHOW VARIABLES LIKE 'max_allowed_packet'; In theory, you can insert any number of rows using a single INSERT statement. However, when MySQL server receives the INSERT statement whose size is bigger than max_allowed_packet, it will issue a packet too large error and terminates the connection. This statement shows the current value of the max_allowed_packet variable

To find the detail of the warning, you can use the .... command

SHOW WARNINGS; https://sp.mysqltutorial.org/wp-content/uploads/2017/07/MySQL-INSERT-IGNORE-warning.png

Simple MySQL recursive CTE example

WITH RECURSIVE cte_count (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte_count WHERE n < 3 ) SELECT n FROM cte_count; -- 1, 2, 3

The basic use of CTE

WITH customers_in_usa AS ( SELECT customerName, state FROM customers WHERE country = 'USA' ) SELECT customerName FROM customers_in_usa WHERE state = 'CA' ORDER BY customerName; A common table expression is a named temporary result set that exists only within the execution scope of a single SQL statement e.g.,SELECT, INSERT, UPDATE, or DELETE. Similar to a derived table, a CTE is not stored as an object and last only during the execution of a query. Unlike a derived table, a CTE can be self-referencing (a recursive CTE) or can be referenced multiple times in the same query. In addition, a CTE provides better readability and performance in comparison with a derived table. In this example, the name of the CTE is customers_in_usa, the query that defines the CTE returns two columns customerName and state. Hence, the customers_in_usa CTE returns all customers located in the USA.

A more advanced MySQL CTE example

WITH salesrep AS ( SELECT employeeNumber, CONCAT(firstName, ' ', lastName) AS salesrepName FROM employees WHERE jobTitle = 'Sales Rep' ), customer_salesrep AS ( SELECT customerName, salesrepName FROM customers INNER JOIN salesrep ON employeeNumber = salesrepEmployeeNumber ) SELECT * FROM customer_salesrep ORDER BY customerName;

f you have more than one column specified in the GROUP BY clause, the ROLLUP clause...

assumes a hierarchy among the input columns. SELECT productLine, orderYear, SUM(orderValue) totalOrderValue FROM sales GROUP BY productline, orderYear WITH ROLLUP; --rollup returns total sales(sum of sales of single product of each year) of each distinct product line, then total sales of everything (The ROLLUP generates the subtotal row every time the product line changes and the grand total at the end of the result.) If you reverse the hierarchy, for example: ... GROUP BY orderYear, productline The ROLLUP generates the subtotal every time the year changes and the grand total at the end of the result set.

MySQL basic INSERT

CREATE TABLE IF NOT EXISTS tasks ( task_id INT AUTO_INCREMENT, title VARCHAR(255) NOT NULL, start_date DATE, due_date DATE, priority TINYINT NOT NULL DEFAULT 3, description TEXT, PRIMARY KEY (task_id) ); INSERT INTO tasks(title,priority) VALUES('Learn MySQL INSERT Statement',1);

How to create table which is an empty copy of another table

CREATE TABLE customers_archive LIKE customers;

INSERT IGNORE and STRICT mode

CREATE TABLE tokens ( s VARCHAR(6) ); INSERT IGNORE INTO tokens VALUES('abcdefg'); MySQL truncated data before inserting it into the tokens table. In addition, it issues a warning. https://sp.mysqltutorial.org/wp-content/uploads/2017/07/MySQL-INSERT-INGORE-warning-strict-mode.png

How to insert data from one table into another

INSERT INTO customers_archive SELECT * FROM customers WHERE NOT EXISTS( SELECT 1 FROM orders WHERE orders.customernumber = customers.customernumber );

Using SELECT statement in the VALUES list

INSERT INTO stats(totalProduct, totalCustomer, totalOrder) VALUES( (SELECT COUNT(*) FROM products), (SELECT COUNT(*) FROM customers), (SELECT COUNT(*) FROM orders) );

INSERT INTO SELECT

INSERT INTO suppliers ( supplierName, phone, addressLine1, addressLine2, city, state, postalCode, country, customerNumber ) SELECT customerName, phone, addressLine1, addressLine2, city, state , postalCode, country, customerNumber FROM customers WHERE country = 'USA' AND state = 'CA';

Inserting dates into the table

INSERT INTO tasks(title, start_date, due_date) VALUES('Insert date into table','2018-01-09','2018-09-15'); INSERT INTO tasks(title,start_date,due_date) VALUES('Use current date for the task',CURRENT_DATE(),CURRENT_DATE());

The following example uses the HAVING clause to find orders that have total amounts greater than 1000 and contain more than 600 items:

SELECT ordernumber, SUM(quantityOrdered) AS itemsCount, SUM(priceeach*quantityOrdered) AS total FROM orderdetails GROUP BY ordernumber HAVING total > 1000 AND itemsCount > 600;

The GROUP BY clause: MySQL vs. standard SQL

Standard SQL does not allow you to use an alias in the GROUP BY clause, however, MySQL supports this. For example, the following query extracts the year from the order date. It first uses year as an alias of the expression YEAR(orderDate) and then uses the year alias in the GROUP BY clause. This query is not valid in standard SQL. SELECT YEAR(orderDate) AS year, COUNT(orderNumber) FROM orders GROUP BY year; MySQL also allows you to sort the groups in ascending or descending orders while the standard SQL does not. The default order is ascending. For example, if you want to get the number of orders by status and sort the status in descending order, you can use the GROUP BY clause with DESC as the following query: SELECT status, COUNT(*) FROM orders GROUP BY status DESC; Notice that we used DESC in the GROUP BY clause to sort the status in descending order. We could also specify explicitly ASC in the GROUP BY clause to sort the groups by status in ascending order.

the INTERSECT operator

The INTERSECT operator compares the result sets of two queries and returns the distinct rows that are output by both queries. The left query produces a result set of (1,2,3). The right query returns a result set of (2,3,4). The INTERSECT operator returns the distinct rows of both result sets which include (2,3). Unfortunately, MySQL does not support the INTERSECT operator. However, you can emulate the INTERSECT operator: SELECT DISTINCT id FROM t1 INNER JOIN t2 USING(id); SELECT DISTINCT id FROM t1 WHERE id IN (SELECT id FROM t2);

MINUS operator

The MINUS compares the results of two queries and returns distinct rows from the result set of the first query that does not appear in the result set of the second query. MySQL MINUS operator emulation SELECT id FROM t1 LEFT JOIN t2 USING (id) WHERE t2.id IS NULL;

This example adds the number 1 to the phone extension of employees who work at the office in San Francisco:

UPDATE employees SET extension = CONCAT(extension, '1') WHERE EXISTS( SELECT 1 FROM offices WHERE city = 'San Francisco' AND offices.officeCode = employees.officeCode);

IN vs EXISTS

WHERE customerNumber IN (SELECT customerNumber FROM orders); WHERE EXISTS( SELECT 1 FROM orders WHERE orders.customernumber = customers.customernumber); The query that uses the EXISTS operator is much faster than the one that uses the IN operator. The reason is that the EXISTS operator works based on the "at least found" principle. The EXISTS stops scanning the table when a matching row found. On the other hands, when the IN operator is combined with a subquery, MySQL must process the subquery first and then uses the result of the subquery to process the whole query. The general rule of thumb is that if the subquery contains a large volume of data, the EXISTS operator provides better performance. However, the query that uses the IN operator will perform faster if the result set returned from the subquery is very small.

Using MySQL recursive CTE to traverse the hierarchical data

https://www.mysqltutorial.org/mysql-recursive-cte/ WITH RECURSIVE myrec AS ( SELECT employeeNumber, reportsTo, firstName, 1 lvl FROM employees WHERE reportsTo IS NULL UNION ALL ------ SELECT e1.employeeNumber, e1.reportsTo, e1.firstName, lvl + 1 FROM employees e1 INNER JOIN myrec e2 ON e2.employeeNumber = e1.reportsTo ------ ) SELECT employeeNumber, reportsTo, firstName, lvl FROM myrec;