Why Your Alias Doesn't Work in WHERE

Imagine you are a company that sells headphones in Spain and we want to expand to France.

You have two tables:

• orders: a user bought a headphone and we save it in an order.

• addresses: responsible for saving the different addresses of the user.

And we will fetch country_code for our endpoint.

The column country_code is not ready yet and it's filled with NULLs. So we add 'temporary_code' AS country_code to have the filter logic ready and we do: WHERE country_code LIKE '%temporary_code%' .

SELECT  order_id,  
        address_id,  
        size,  
        street,  
        country,  
        'temporary_code' AS country_code  
FROM orders  
    LEFT JOIN addresses ON orders.address_id = addresses.id
WHERE country_code LIKE '%temporary_code%'

But something is not working correctly... Why?

The order in a SQL execution is:

1. FROM

2. WHERE

3. SELECT

So in this case we will be filtering by the country_code column that already exists inside the addresses table and not the one in SELECT, and this will return empty.

Temporarily you could write:

SELECT  order_id,  
        address_id,  
        size,  
        street,  
        country,  
        'temporary_code' AS country_code  
FROM orders  
    LEFT JOIN addresses ON orders.address_id = addresses.id
WHERE 'temporary_code' LIKE '%temporary_code%'