2

I needed to select certain records from a selection with a custom column that have another column's modified value as its own value. The following query just works for my needs. But I wanted to know if it's the best/right way of doing this.

/* this is just OK */
SELECT column1,column2,SUBSTRING(column2,4) AS custom_column
FROM MY_TABLE
WHERE SUBSTRING(column2,4) = 'xyz'

Also, I saw I cannot simply say

WHERE custom_column = 'xyz' /* error: custom_column doesn not exixts */ 

It would throw an error. That's because I used the syntax above instead. But it feels strange to me.

  • It's because your WHERE is evaluated before the SELECT. Sounds weird right? Remember, SQL is a declarative langue so your query is optimized on the back end. Gordon explains it a bit here and there are many other blogs if you google MySQL order of execution. Thus, the column alias doesn't exist yet when the WHERE is evaluated. So, yes, it's the right way. – scsimon Oct 11 at 15:41
  • Ok. It's a pity because there's repetition (substring execution). Maybe not so optimized then? Is there any other (better) way to create a custom column? – Luca Reghellin Oct 11 at 15:49
  • I'm not a MySQL guy, but are you sure it's actually being repeated (see this in the execution plan)? – scsimon Oct 11 at 16:23
  • 1
    @scsimon if you want to use the custom column name in the filtering part , you can specify them in the HAVING clause instead.. So, instead of WHERE .. use HAVING custom_column = 'xyz' – Madhur Bhaiya Oct 11 at 17:03
  • 2
    @scsimon actually MySQL allows specifying HAVING clause without GROUP BY. You dont really need to do aggregation using GROUP BY here. – Madhur Bhaiya Oct 11 at 17:06
3

It is unclear whether the method in your example is right or best for you, but if you need to query or return the same expression in many different queries, you can add a custom column to the table itself. In MySQL, this is called a generated column:

ALTER TABLE
  MY_TABLE
ADD COLUMN
  custom_column VARCHAR(50) AS
  (
    SUBSTRING(column2, 4)
  )
;

This way the column will be available for reference in any part of any query on MY_TABLE:

SELECT
  column1,
  column2,
  custom_column
FROM
  MY_TABLE
WHERE
  custom_column = 'xyz'
;

A live demo of this approach is available at dbfiddle logodb<>fiddle.uk.

On the other hand, if this is a one-off occasion where you simply need to be able to reference a calculated column in the WHERE clause of one specific query, then since MySQL 8 you can define such a column using a LATERAL join to a FROM-less SELECT. Once you have defined the column in this way, you can reference it by name in any part of the query, including the WHERE clause:

SELECT
  t.column1,
  t.column2,
  c.custom_column
FROM
  MY_TABLE AS t
  CROSS JOIN LATERAL
  (
    SELECT
      SUBSTRING(column2, 4) AS custom_column
  ) AS c
WHERE
  c.custom_column = 'xyz'
;

You can play with this method in another live demo.

1

As already answered in comments by @Madhur (All the credits goes to him).

You can use having clause to use alias name without group by.

So instead of WHERE it should be HAVING.

SELECT 
    column1, column2, SUBSTRING(column2, 4) AS custom_column
FROM
    MY_TABLE
HAVING custom_column = 'xyz'

Note: To know the detail reason why alias name can't be called in the where clause refer here.

  • 2
    You cannot just replace "where" with "having" and expect the query behaviour to be the same. The evaluation order is different and the query might end up being a lot slower. – Tom V - Team Monica Oct 16 at 7:47
  • 1
    I agree, but are you sure the having would use an index like a where would? – Tom V - Team Monica Oct 16 at 9:03
  • 1
    OK, so I take it they are just interchangeable in MySQL. Could be. Thanks – Tom V - Team Monica Oct 16 at 9:14
  • 1
    I've heard of this way of using HAVING in MySQL but still find it weird to be honest. For instance, I can't understand why the having b = 5 and count(*) >= 1 query in your fiddle produces no rows. Could you perhaps explain it, please? – Andriy M Oct 16 at 14:13
  • 1
    No, I get it that resetting sql_mode allows you to run this kind of query. I'm merely trying to understand why that specific query didn't produce rows, even though the having b = 1 and count(*) >= 1 did. – Andriy M Oct 16 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.