Using alias from COALESCE from SELECT in WHERE clause

Question

DB Fiddle

CREATE TABLE logistics (
    id int primary key,
    campaign VARCHAR(255),
    inbound_date VARCHAR(255),
    outbound_date VARCHAR(255)
);

INSERT INTO logistics
(id, campaign, inbound_date, outbound_date)
VALUES 
("1", "C001", "2019-01-01", "2019-02-08"),
("2", "C001", "2019-05-10", "2019-05-12"),
("3", "C001", "2019-06-12", "2019-06-15"),
("4", "C001", "2019-08-13", "2019-08-20"),
("5", "C001", "2019-11-14", "2019-11-22");

In the table above I have the columns inbound_date and outbound_date for date values.
In my query I coalesce them to one column called event_date.

Now, I want to use the alias for the coalesce in the WHERE clause of my query but I get error Unknown column 'event_date' in 'where clause':

SELECT
campaign,
coalesce(inbound_date, outbound_date) as event_date
FROM logistics
WHERE
event_date BETWEEN "2019-06-01" AND "2019-10-01"

I know I could solve the issue by using the inbound_date and outbound_date as two seperate conditions in the WHERE clause but isn't there a smarter way using the alias of the coalesce?

Answer 1

I believe you could do this:

SELECT campaign, event_date
FROM (
  SELECT
  campaign,
  coalesce(inbound_date, outbound_date) as event_date
  FROM logistics
)
WHERE
event_date BETWEEN "2019-06-01" AND "2019-10-01"

(Not sure if this is something specific to MySQL, but in generic SQL I'd expect single quotes not double around those strings.)

Note that in any case, I think this is unlikely to utilize an index on either date column.