◀ Course contents Part 1 · Module 1-04

DISTINCT, Aliases & Expressions

Shaping the output rather than choosing the rows

The last two modules were about which rows come back. This one is about what those rows look like: removing duplicates, computing columns the table does not store, and naming everything so the result explains itself to whoever opens it next.

Ready?

1

DISTINCT Removes Duplicate Rows

DISTINCT sits immediately after SELECT and drops repeated rows from the result. Its everyday use is answering "what values does this column actually contain" before you write a filter against it. 

SELECT DISTINCT plan
FROM users;

Three rows come back instead of twelve. That is how you learn the column stores free, pro and team — in lower case — rather than guessing 'Pro' and getting an empty result with no error to explain it.

The one thing to remember: DISTINCT applies to the whole row, not to the column nearest to it. SELECT DISTINCT plan, country returns every unique combination, which can easily be more rows than DISTINCT plan alone.

3 rows

SELECT DISTINCT plan

The three plan values.

12 rows

SELECT DISTINCT plan, name

Every name is unique, so every combination is too. Nothing is removed.

Quick check

SELECT DISTINCT country FROM users returns 12 rows out of 12 users. What does that tell you?

2

The SELECT List Can Compute

A SELECT list is not restricted to columns the table has. Any expression works, evaluated once per row. 

SELECT name, price, price * 12
FROM products;

Arithmetic, text functions, comparisons, CASE — all of it can sit in the SELECT list. The row it produces is computed on the way out and stored nowhere, which is exactly the point: a derived value cannot drift out of sync with the values it derives from, because it is recomputed every time.

Strings are joined with || in standard SQL, which Postgres follows. (MySQL uses a CONCAT function instead.) Each literal piece is its own quoted string: 

SELECT name || ' (' || country || ')'
FROM users;
-- Ada Lovelace (UK)

Dialects disagree here

SQL Server writes concatenation as +, MySQL as CONCAT(a, b). It is one of the first things to break when a query is moved between databases, and one of the easiest to fix once you know to look for it.

Quick check

Does SELECT price * 12 FROM products change anything in the products table?

3

Every Computed Column Needs a Name

A computed column comes back headed by its own expression — price * 12 — which looks like debugging output in a report and is awkward for anything reading the result by column name. AS fixes it: 

SELECT name,
       price * 12 AS annual_price
FROM products;

The alias exists only for the duration of the query. Nothing about the table changes, and a different query can call the same expression something else.

The word AS is technically optional in most databases — price * 12 annual_price works — but leaving it out makes a missing comma look like an alias, which turns a clear syntax error into a silently wrong column list. Write the AS.

Two columns

SELECT name category FROM products

A missing comma. SQL reads it as "the name column, aliased to category", and returns one column with the wrong heading.

Clear

SELECT name, category FROM products

What was actually meant. The AS habit makes the difference visible.

Quick check

Where does a column alias exist?

4

ORDER BY Can Use an Alias, WHERE Cannot

This looks arbitrary and is not. SQL clauses are evaluated in a fixed order, and it is not the order you write them in: 

FROM      →  WHERE  →  GROUP BY  →  HAVING  →  SELECT  →  ORDER BY  →  LIMIT

WHERE runs before SELECT, so at that moment the alias has not been created yet — hence "no such column". ORDER BY runs after SELECT, so by then the alias exists and can be sorted on. 

SELECT price * 12 AS annual
FROM products
WHERE annual > 100      -- fails: annual does not exist yet
ORDER BY annual;         -- works: it does by now

The fix in WHERE is to repeat the expression: WHERE price * 12 > 100. It is not elegant, and knowing why beats memorising a list of places aliases are allowed. This same evaluation order explains HAVING in Part 2, and it is worth learning once, properly, here.

Written order versus run order

You write SELECT first and the database runs it fifth. That single mismatch explains most of the "but why can't I use that here" questions in SQL — aliases in WHERE, aggregates in WHERE, and why HAVING exists at all.

Learn the run order once and three separate rules stop needing to be memorised.

Quick check

Why does ORDER BY annual work when WHERE annual > 100 does not?

0 of 4 completed

Loading the tables…

01

To do

DISTINCT removes duplicate rows from the result. It is how you answer "what are the possible values of this column" without reading twelve rows and squinting. 

SELECT DISTINCT plan
FROM users;

It goes immediately after SELECT and applies to the whole row, not to one column — SELECT DISTINCT a, b gives every unique combination of a and b, which is usually what you want and occasionally a surprise.

Your task: return each plan that appears in the users table, once each.

query.sql
PostgreSQL
Hint

SELECT DISTINCT plan FROM users; — three plans come back, not twelve rows.

Output 

      


      
    
    
    
  
    
  
    

    

      
    
    02
    
    
      
    
    
    To do
  
    

  
    
  
    
      
    
  The first thing anybody does with an unfamiliar event table is ask what
  the events are called. Nobody can query a funnel without knowing whether
  the step is stored as signup, sign_up or
  Signup.

    

    
  DISTINCT combines happily with ORDER BY, and
  an alphabetical list is much easier to scan.

    

    
  Your task: return each distinct
  event_name in the events table, sorted
  alphabetically.

    
    
    

      
    
        
    
           query.sql
        
    

        
    
          
        
    
          PostgreSQL
          
          
        
    
      
    

      
    
        
        
        
        
      
    
      
    
        Hint
        
    SELECT DISTINCT event_name FROM events ORDER BY event_name; — five names come back.
    
      
    

      
    
        
    
           Output
          
        
    
        
    
      
    

      
      
    
      
  
      
  
      

      

      
      
    03
    
      
      
    
      
    To do
  
      

  
      
  
      
      
      
  A SELECT list is not limited to columns that exist. Any
  expression works, and it is evaluated once per row.

      

      SELECT name, price, price * 12
      FROM products;
      

      
  That third column comes back named something unhelpful like
  price * 12. AS gives it a real name:

      

      SELECT price * 12 AS annual_price
      FROM products;
      

      
  Your task: return name,
  price, and the yearly cost as a third column named exactly
  annual_price.

      
    
      

      
      
        
      
           query.sql
        
      

        
      
          
        
      
          PostgreSQL
          
          
        
      
      
      

      
      
        
        
        
        
      
      
      
      
        Hint
        
      price * 12 AS annual_price — the alias goes after the expression, and the name has to match exactly, underscore included.
      
      
      

      
      
        
      
           Output
          
        
      
        
      
      
      

      
        
    
        
  
        
  
        

        

      
        
    04
    
        
      
    
        
    To do
  
        

  
        
  
        
      
        
  || glues strings together in Postgres. It is how you build a
  readable label out of several columns.

        

        SELECT name || ' (' || country || ')' AS label
        FROM users;
        

        
  Each piece of literal text — the space, the brackets — is its own quoted
  string. Dialects differ here: SQL Server uses +, MySQL uses
  CONCAT(), and || is the standard that Postgres
  and Postgres follow.

        

        
  Your task: return one column named label
  holding each user's name, a space, and their country in brackets — for
  example Ada Lovelace (UK).

        
    
        

      
        
        
        
           query.sql
        
        

        
        
          
        
        
          PostgreSQL
          
          
        
        
      
        

      
        
        
        
        
        
      
        
      
        
        Hint
        
        name || ' (' || country || ')' AS label — mind the space inside the first quoted piece, and the closing bracket in the last one.
        
      
        

      
        
        
        
           Output
          
        
        
        
        
      
        

      
          
    
          
  
          
  
          

          

    
          
  
          


  
  
          
    
          
    
    
          The price card
          
    To do
  
          

  
          
  
          
      
          
  Sales want a one-page price card. For every product they need the product
  name, what it costs a month, what it costs a year, and the category —
  under column headings they can paste straight into a slide.

          

          
  Your task: from products, return exactly
  four columns, named exactly:

          

          product   the product name
          type      the category
          monthly   the price
          yearly    the price multiplied by 12
          

          
  Sorted most expensive first. The column names are the specification —
  returning the right numbers under the wrong headings does not clear this.

          
    
          

      
          
        
          
           query.sql
        
          

        
          
          
        
          
          PostgreSQL
          
          
        
          
      
          

      
          
        
        
        
        
      
          
      
          
        Hint
        
          Every column needs an AS: name AS product, category AS type, price AS monthly, price * 12 AS yearly. Then ORDER BY price DESC.
          
      
          

      
          
        
          
           Output
          
        
          
        
          
      
          

      
            
    
            
  
            
  
            

            

  
            




            



            
  
            
    
            
      
      
            Notification