In SQL, WITH clause is used to create a Subquery or View for a set of data.
The main uses of WITH clause are:
- Simplify: It can simplify a SQL query by creating a subset of data.
- Reduce Repetition: WITH clause can create a subset of data that can
be reused multiple times in the main query.
E.g. In following query we use WITH clause to get the set of employee in Finance department. Then we use this subset fin_employee to filter based on AGE less than 30 and Female Gender.
We have used the same set fin_employee multiple times in main query.
WITH fin_employee AS
WHERE dep_name = ‘Finance’)
WHERE AGE < 30
WHERE Gender = ‘Female’;