MySQL coalesce, exists, union and having example

John D K -

Working with SQL requires formatting and filtering of data which can be done by:

  • HAVING Clause
  • EXISTS Operator
  • coalesce and ifnull functions
  • Union

MySQL HAVING Clause

Having clause is useful when you want to filter groups in SQL. For example if you want to get only groups of two and more elements:

SELECT COUNT(id), city
FROM customers
GROUP BY city
HAVING COUNT(id) > 2;

SELECT COUNT(id), job_title
FROM customers
GROUP BY job_title
HAVING COUNT(id) > 2
ORDER BY COUNT(id) DESC;

Note: WHERE clause is used as a constraint on single rows while HAVING clause is applied as a condition on groups,

MySQL EXISTS Operator

You can check if a row exists for a given constraints by exists. In this example we have table products and another one order_details which are linked. The product price is in table order_details and we are using exists in order to check if a product exists with price higher than 50. First two examples check if an order exists or not for a product:

-- check if order exists for a product
SELECT *
FROM products p 
WHERE  EXISTS (SELECT * FROM order_details od WHERE od.product_id  = p.id)

-- check all products without order
SELECT *
FROM products p 
WHERE  EXISTS (SELECT * FROM order_details od WHERE od.product_id  = p.id)

SELECT *
FROM products p 
WHERE EXISTS (SELECT * FROM order_details od WHERE od.product_id  = p.id  AND unit_price > 50)

MySQL coalesce and ifnull functions

In case of incomplete or wrong date you can get unexpected results. For example if you have product of two columns and one of them return null. This can be avoided by using(doing the same in this case):

  • IFNULL
  • COALESCE
SELECT p.product_name, unit_price, quantity, unit_price * quantity, IFNULL(unit_price, 0) * od.quantity
FROM order_details od, products p
WHERE od.product_id = p.id 
	
SELECT p.product_name, unit_price, quantity, unit_price * quantity, COALESCE(unit_price, 0) * od.quantity
FROM order_details od, products p
WHERE od.product_id = p.id

MySQL union and union all

You can combine results of several queries in MySQL simply by using:

  • UNION ALL - with repetitions
  • UNION - no repetitions
-- union all - with repetitions
SELECT City FROM employees
UNION all
select ' -----  ' from dual
UNION all
SELECT city FROM customers

-- union - no repetitions
SELECT City FROM employees
UNION
select ' -----  ' from dual
UNION
SELECT city FROM customers

-- union and order
SELECT City FROM employees
UNION
SELECT city FROM customers
order by city