SQL Transform

The examples below build on a small three-table schema (customer, customer_order, order_item) created by Demo 19 and queried by Demo 20.

JOIN

A JOIN combines rows from two tables into one result, matched on a condition — almost always a foreign key equal to the primary key it references.

Example:

select c.name, o.id as order_id, o.status
  from customer c
  join customer_order o on o.customer_id = c.id
 order by c.name, o.id;

GROUP BY and Aggregates

GROUP BY collapses many rows into one summary row per group. Every column in the SELECT list must either be in the GROUP BY or wrapped in an aggregate function (COUNT, SUM, AVG, MIN, MAX). HAVING filters groups after aggregation — WHERE filters rows before it.

Example:

select o.customer_id,
       count(*) as order_count,
       sum(i.quantity * i.unit_price) as total_spent
  from customer_order o
  join order_item i on i.order_id = o.id
 group by o.customer_id
having sum(i.quantity * i.unit_price) > 20
 order by total_spent desc;

Subqueries

A subquery is a SELECT nested inside another statement — in the FROM clause (an inline view), in the WHERE clause (a filter built from another query), or as a scalar value in the SELECT list.

Example:

-- a subquery in WHERE: customers who have placed at least one order
select name from customer
 where id in (select customer_id from customer_order);

CTEs: the WITH Clause

A Common Table Expression names a subquery up front with WITH, so the main query reads top to bottom instead of nesting parentheses inside parentheses. It is the same subquery, just given a name and pulled out where it is easier to read — and it can be referenced more than once.

Example:

with order_totals as (
  select o.id as order_id, o.customer_id,
         sum(i.quantity * i.unit_price) as order_total
    from customer_order o
    join order_item i on i.order_id = o.id
   group by o.id, o.customer_id
)
select c.name, ot.order_id, ot.order_total
  from order_totals ot
  join customer c on c.id = ot.customer_id
 order by ot.order_total desc;

Views

A VIEW saves a query under a name, so it can be queried as if it were a table — the query itself runs fresh every time. Use a view to hide a complex join behind a simple name, or to give a team read access to a shape of the data without exposing the underlying tables directly.

Example:

create or replace view customer_order_totals as
  select c.id as customer_id, c.name,
         count(o.id) as order_count,
         nvl(sum(i.quantity * i.unit_price), 0) as total_spent
    from customer c
    left join customer_order o on o.customer_id = c.id
    left join order_item i     on i.order_id = o.id
   group by c.id, c.name;

select * from customer_order_totals order by total_spent desc;

UNION and UNION ALL

UNION stacks the results of two compatible SELECTs into one result set and removes duplicate rows. UNION ALL does the same but keeps duplicates — and is faster, because it skips the duplicate-elimination pass. Prefer UNION ALL whenever you already know the two sides cannot overlap.

Example:

select name, 'customer' as kind from customer
union all
select product, 'product' as kind from order_item;

MERGE

MERGE (also called "upsert") compares a source row set against a target table and, in one statement, updates matching rows and inserts new ones. It replaces the older, more error-prone pattern of a manual "does it exist? then UPDATE else INSERT". Note the target must be a real table — not an aggregate view like the one above, which Oracle will not let you write to directly.

Example:

-- a plain summary table, refreshed on demand by the MERGE below
create table customer_order_summary (
  customer_id  number primary key,
  order_count  number,
  total_spent  number(12, 2)
);

merge into customer_order_summary t
using (select o.customer_id, count(*) as order_count,
              sum(i.quantity * i.unit_price) as total_spent
         from customer_order o join order_item i on i.order_id = o.id
        group by o.customer_id) s
   on (t.customer_id = s.customer_id)
 when matched then
   update set t.order_count = s.order_count, t.total_spent = s.total_spent
 when not matched then
   insert (customer_id, order_count, total_spent)
   values (s.customer_id, s.order_count, s.total_spent);

See it in a full demo: Demo 20 — Transform / ETL.