A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. In simple layman's terms, CTE basically provides data at the runtime which may come from multiple tables. For e.g. if we have different tables like emp, dept, salgrade etc. then CTE can help in getting the resultset/dataset from multiple tables and form a temporary table. Then we can refer this table within other query any number of times. For e.g. if we want to display the following: Deptno Dname Location TotalCount 10 Hotel Management Amritsar 6 20 Information Technology Chicago 1 30 Finance Ludhiana