CODEWITHSUNDEEP
mysql
Carlos Morales
Carlos Morales

Reputation: 1149

Cannot show information between in a period of time

I'm trying to show a condition between 2 columns in a period of time like this:

Here is the demo

Here is the information:

CREATE TABLE clients
(date_birth date, [date_anniversary] date)
;

INSERT INTO clients
([date_birth], [date_anniversary])
VALUES
('1911-01-20',NULL ),
('1921-01-20',NULL ),
('1931-01-20',NULL ),
('1941-01-20',NULL ),
('1951-01-20',NULL ),
('1961-01-20',NULL ),
('1971-01-20',NULL ),
('1981-01-20',NULL ),
('1991-01-20',NULL ),
(NULL, '1998-02-13'),
(NULL, '1999-02-13'),
(NULL, '2000-02-13'),
(NULL, '2001-02-13'),
(NULL, '2002-02-13'),
(NULL, '2003-02-13'),
(NULL, '2004-02-13'),
(NULL, '2005-02-13'),
(NULL, '2006-02-13');

I'm trying to show:

('1911-01-20',NULL ),
('1921-01-20',NULL ),
('1931-01-20',NULL ),
('1941-01-20',NULL ),
('1951-01-20',NULL ),
('1961-01-20',NULL ),
('1971-01-20',NULL ),
('1981-01-20',NULL ),
('1991-01-20',NULL ),
(NULL, '1998-02-13'),
(NULL, '1999-02-13'),

I tried this but is not working:

select * from clients where date_birth OR date_anniversary BETWEEN '1910-01-01' AND '2000-01-01'

Please somebody can help me?

Upvotes: 0

Views: 22

Answers (1)

VMai
VMai

Reputation: 10346

You've got to write complete conditions:

select * from clients 
where 
    date_birth BETWEEN '1910-01-01' AND '2000-01-01'
OR 
    date_anniversary BETWEEN '1910-01-01' AND '2000-01-01'

In your special case: only one of the two columns contains a not NULL value you could use the function COALESCE, that returns the first non-null value of the list of arguments:

select 
    * from clients 
where 
    COALESCE(date_birth, date_anniversary) BETWEEN '1910-01-01' AND '2000-01-01';

But even if this looks simpler, it may be that the performance is worse than the first solution. You should check with EXPLAIN.

See it in your (modified) Demo

Upvotes: 1

Related Questions