Planned lessonMID-011-L05

Set operations and FILTER

Lesson 5 of 7
Mid-level

On This Lesson 4 topics

In this lesson
Planned topics
Draft outline details
Practice

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

combine result sets and compute several conditional aggregates in one pass.

Planned topics

UNION removes duplicates; UNION ALL keeps them and is faster. Default to UNION ALL.
INTERSECT and EXCEPT find common and missing rows; both sides need matching columns.
count(*) FILTER (WHERE status = 'PAID') replaces repeated CASE expressions.
One scan can produce many metrics.

Draft outline details

Demonstration

sql

SELECT count(*) AS orders,
       count(*) FILTER (WHERE status = 'PAID')      AS paid,
       count(*) FILTER (WHERE status = 'CANCELLED') AS cancelled
FROM orders;

Check your understanding

Which is faster, UNION or UNION ALL, and why?

Show answer

UNION ALL; it skips duplicate removal.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson