MySQL SubQuery

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 orderDetailstable 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


MYSQL CONCATENATION

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;