Common Table Expression (CTE)

Common Table Expression or CTE it is one of query form SQL that can be utilized to simplify JOIN in SQL into subqueries and being able to give the query that has hierarchy characteristic.

Example

CTE

WITH lecture_supervisor_cte AS
(SELECT School
FROM lecture_supervisor
WHERE lecture_supervisor_name = 'Dr Suraya Hanim'

SELECT student_name
FROM univ_student
INNER JOIN lecture_supervisor_cte
ON univ_student.school = lecture_supervisor_cte.school
AND exam_score > 70

The benefit of using CET

• Readability - The use of CTE ease developer in reading complex query that has been made. With the hierarchy pattern, the query will be easy to be read compared when we made a lot of subquery or create some separate views
• Recursion - CTE support the recursive queries making, where a subquery can call itself and support the usage of union.

Introductory to Subquery

Subquery or Inner query or Nested query are queries in other SQL queries and planted in clause WHERE. A subquery can be utilized to recall data that has been used in the main query as requirement to more limit the data that will be taken.

Example

What are the benefit using Subquery?

Some of benefit using CTE in query are:

• Custom Column - Subquery can be used as custom column that can stand alone.

  Example:

  SELECT student_name, exam_score,
  (SELECT AVG(exam_score) FROM student)
  AS average_exam_score FROM student