Common Table Expression or CTE specifies a temporary named result set which is derived from a query and can be used in the same execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. This is quite useful if you need temporary results from a sub query within your main query. An example below uses Northwind 2011 database:

Let’s break down the SQL code above:

1. CTE Duplicates is defined and run. It returns a list of OrderID, grouped by OrderID and Quantity, where count of orders in a group is bigger then 1:
   
   MySQL

   WITH Duplicates AS ( select OrderID from OrderDetails where Quantity >= 20 group by OrderID, Quantity Having Count(*) > 1 )

   1 2 3 4 5 6 7 8

   WITH Duplicates AS (   select OrderID from OrderDetails where Quantity >= 20 group by OrderID, Quantity Having Count(*) > 1 )

2. The main query uses the results from Duplicates query in the Where clause:
   
   MySQL

   SELECT OrderID,ProductID, UnitPrice,Quantity,Discount FROM OrderDetails Where OrderID in (select OrderID from Duplicates) order by OrderID, Quantity

   1 2 3 4

   SELECT OrderID,ProductID, UnitPrice,Quantity,Discount   FROM OrderDetails Where OrderID in (select OrderID from Duplicates) order by OrderID, Quantity

3. The result of the whole script is:
   

You can also use multiple CTE expressions in a single SQL statment. Check the example below:

You can also read more about CTE on Microsoft website.