Database newbie question:

My sample database (T-SQL syntax):

CREATE DATABASE sample
GO
USE sample
CREATE TABLE customers (CustomerId int IDENTITY(1,1) PRIMARY KEY CLUSTERED,name varchar(50),address varchar(50))
CREATE TABLE invoices (InvoiceId int IDENTITY(1,1) PRIMARY KEY CLUSTERED,date datetime,CustomerId int REFERENCES customers(CustomerId))
INSERT customers (name,address) VALUES ('First Company Ltd.','New York')
INSERT customers (name,address) VALUES ('Second Company Ltd.','Washington')
INSERT invoices (date,CustomerId) VALUES ('Jan 1, 2004',1)
INSERT invoices (date,CustomerId) VALUES ('Jan 2, 2004',1)
INSERT invoices (date,CustomerId) VALUES ('Jan 2, 2004',2)
SELECT * FROM customers
GO

CustomerId name address

1 First Company Ltd. New York
2 Second Company Ltd. Washington

SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers AS c ON i.CustomerId=c.CustomerId
GO

date name address

2004-01-01 00:00:00.000 First Company Ltd. New York
2004-01-02 00:00:00.000 First Company Ltd. New York
2004-01-02 00:00:00.000 Second Company Ltd. Washington

UPDATE customers SET address='Boston' WHERE name='First Company Ltd.'
GO
INSERT invoices (date,CustomerId) VALUES ('Jan 3, 2004',1)
SELECT * FROM customers
GO

CustomerId name address

1 First Company Ltd. Boston
2 Second Company Ltd. Washington

SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers AS c ON i.CustomerId=c.CustomerId
GO

date name address

2004-01-01 00:00:00.000 First Company Ltd. Boston
2004-01-02 00:00:00.000 First Company Ltd. Boston
2004-01-02 00:00:00.000 Second Company Ltd. Washington
2004-01-03 00:00:00.000 Second Company Ltd. Boston

Is it possible in any of the RDBMS's to make this last query return the following result set?

2004-01-01 00:00:00.000 First Company Ltd. New York
2004-01-02 00:00:00.000 First Company Ltd. New York
2004-01-02 00:00:00.000 Second Company Ltd. Washington
2004-01-03 00:00:00.000 First Company Ltd. Boston

What, you mean that the select should ignore what is in the database and print what used to be there but isn't now? No.

What you can do is keep history. For example:

create table customer_address (customerid references customer, from_date date, to_date date, address varchar2(50),
primary key (customerid, from_date);

insert into customer_address values (1, '2004-01-01','2004-01-02','New York');
insert into customer_address values (1, '2004-01-03','2999-01-01','Boston');

Then use the dates in the join:

SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers AS c ON i.CustomerId=c.CustomerId JOIN customer_address a ON a.customerid=i.customerid AND i.date BETWEEN a.FROM_DATE;

(That syntax may not be quite right for SQL Server!)

I just want to know, if exists a feature in any of the today's DMBS's to manage the entity (record) changes or history without (sql)programatically adding history tables.