What is a common table expression (CTE) in SQL?

Short answer: A Common Table Expression (CTE) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are often used for organizing complex queries.

Example code

WITH EmployeeCTE AS ( SELECT name, salary FROM employees WHERE salary > 50000 SELECT * FROM EmployeeCTE;

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.