SELECT — Marketing Cloud SQL reference | Cleon

SELECT is where every SQL Query Activity in Marketing Cloud starts. It tells the engine which columns of a Data Extension or System Data View should make it into the destination DE. What it doesn't tell — column types, lengths, the order rows actually land in — is the origin of most production bugs.

Official syntax

The SELECT syntax in MC SQL is a strict subset of T-SQL. Projection is supported with column aliases, expressions, and column-level functions. There is no SELECT INTO (use INSERT INTO ... SELECT to write back), no SELECT TOP n PERCENT, and ORDER BY is unreliable when wrapped inside INSERT INTO ... SELECT (see gotchas — #10).

-- Plain projection
SELECT
  SubscriberKey,
  EmailAddress,
  LoyaltyTier
FROM master_subscribers;

-- With aliases + expressions
SELECT
  s.SubscriberKey,
  LOWER(s.EmailAddress) AS EmailAddressNormalized,
  CASE
    WHEN s.LoyaltyTier IN ('gold','silver') THEN 'high'
    ELSE 'standard'
  END AS Segment
FROM master_subscribers s;

-- With aggregate
SELECT
  EmailDomain,
  COUNT(*) AS SubscriberCount
FROM master_subscribers
GROUP BY EmailDomain;

Reference:

What survives in production

Avoid `SELECT *`

-- AT RISK — column order and presence depend on the source DE,
-- which can change between deploys without you noticing
SELECT * FROM master_subscribers;

-- DURABLE — explicit projection, breaks loudly if a column goes
-- away instead of silently re-shaping the destination DE
SELECT
  SubscriberKey,
  EmailAddress,
  LoyaltyTier,
  LastPurchase
FROM master_subscribers;

The wildcard is faster to type, slower to debug at 11pm. A DE owner adds a column, your destination DE schema drifts, the next Send pulls the wrong shape — and none of that surfaces until a journey misroutes someone.

Comment intent in column aliases

SELECT
  SubscriberKey,
  EmailAddress,
  -- Aliasing the LOWER() result so the destination DE column name
  -- documents that the raw value was normalized at query time.
  LOWER(EmailAddress) AS EmailAddressLower
FROM master_subscribers;

The alias is the only place the next dev sees what you decided. A column called EmailAddressLower carries the decision; a column called Email1 carries nothing.

Don't pull what the destination doesn't need

Each column in the destination DE is storage cost and query time on the next read. If the downstream Send Definition only needs SubscriberKey + EmailAddress, project those two — not the eight you happened to need to compute the filter.

System Data Views: column set is documented, but watch for rotation

When you SELECT from _Subscribers, _Sent, _Open and friends, the column set is documented in Salesforce Help. The risk is not which columns exist — it's whether the row set still maps to the SubscriberKeys you expect after a Send Definition rotation. See gotchas — #6.

Quick decision

Use SELECT directly when:

You're projecting from a single Data Extension or one System Data View into a destination DE.
The columns you need are already typed and sized correctly in the source.
The query stays under ~3 minutes against a representative production volume.

Wrap SELECT in a staging step when:

You're transforming with CASE, string ops, or date arithmetic — stage first, dedup or validate, then promote.
You're joining across more than two Data Extensions — flatten in stages.
The source is a System Data View — snapshot first into a de_log_* you control.

Don't use SELECT when you really wanted to update:

MC SQL Activities can't UPDATE. Re-emit the row through INSERT INTO ... SELECT with the destination DE's "Update" mode and the right primary key. See gotchas — #2.

MC SQL gotchas — what fires at scale across every clause

More reference pages incoming: Basics · FROM · JOIN · WHERE · LIKE · CASE · INSERT INTO · String / Date / Numeric / Conversion / Aggregate / Null Functions · Style Guide.

Plus how-to snippets for common production debugging — email sends, value length, contact reach, etc.