What is the use of WITH clause in SQL?

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
(SELECT *
FROM Employee
WHERE dep_name = ‘Finance’)
SELECT *
FROM fin_employee
WHERE AGE < 30
UNION ALL
SELECT *
FROM fin_employee
WHERE Gender = ‘Female’;

Read the full book at www.amazon.com
Posted in SQL, SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

*