Breaking my Non-Equi Join cherry

Posted on February 17, 2010, 9:56 pm, by Rhys, under MySQL, T-SQL.

There’s few SQL techniques you seem to keep in the cupboard gathering dust. I don’t think I’ve ever needed to use RIGHT JOIN outside of the classroom. I can recall using FULL OUTER JOIN, just once, to show an employer how not-in-sync their "integrated system" was. Today I broke my professional Non-Equi JOIN cherry!

I basically had one table of appointments and another table providing appointment banding by a date range.  The banding wasn’t consistent, spanning weeks and months,  so there was no possibility of using an equi-join or doing anything with datetime arithmetic. Perhaps that non-equi join thing I remember reading in the textbook will do?

Here’s a quick run-through of a similar situation. Create some tables and insert some test data. (This first example is in SQL Server 2005)

?View Code TSQL
CREATE TABLE dbo.Appointments
(
	AppointId INTEGER IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
	AppointmentDateTime DATETIME NOT NULL,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	Reason VARCHAR(50) NULL
);
GO
 
CREATE TABLE AppointmentBands
(
	AppointBandId INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	StartDateTime DATETIME NOT NULL,
	EndDateTime DATETIME NOT NULL
);
GO
 
-- Insert Test Data
INSERT INTO dbo.Appointments
(
	AppointmentDateTime,
	FirstName,
	LastName,
	Reason
)
SELECT '2010-02-18T17:00:00',
	   'Rhys',
	   'Campbell',
	   'Eye Test'
UNION ALL
SELECT '2010-02-23T12:00:00',
	   'John',
	   'Smith',
	   'Ear Test'
UNION ALL
SELECT '2010-02-28T14:00:00',
	   'Frank',
	   'Zappa',
	   'Eye Test';
GO
 
INSERT INTO dbo.AppointmentBands
(
	StartDateTime,
	EndDateTime
)
SELECT '2010-02-18T00:00:00',
	   '2010-02-22T23:59:59'
UNION ALL
SELECT '2010-02-23T00:00:00',
	   '2010-02-27T23:59:59'
UNION ALL
SELECT '2010-02-28T00:00:00',
	   '2010-03-01T23:59:59';

I needed to identify which appointment band each record belonged to. It’s a trivial example here, but the real life situation involved hundreds of thousands of appointments and a few thousand appointment bands. The solution involved using a BETWEEN in the join clause.

?View Code TSQL
SELECT app.*, band.AppointBandId
FROM dbo.Appointments app
INNER JOIN dbo.AppointmentBands band
	ON app.AppointmentDateTime BETWEEN band.StartDateTime AND band.EndDateTime;

appointments non equi join

Such a simple, elegant, single query solution. Perhaps we need to dust these things off from time-to-time?

Here’s the same example, a’la MySQL

?View Code MYSQL
CREATE TABLE Appointments
(
	AppointId INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
	AppointmentDateTime DATETIME NOT NULL,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	Reason VARCHAR(50) NULL
);
 
CREATE TABLE AppointmentBands
(
	AppointBandId INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
	StartDateTime DATETIME NOT NULL,
	EndDateTime DATETIME NOT NULL
);
 
-- Insert Test Data
INSERT INTO Appointments
(
	AppointmentDateTime,
	FirstName,
	LastName,
	Reason
)
SELECT '2010-02-18T17:00:00',
	   'Rhys',
	   'Campbell',
	   'Eye Test'
UNION ALL
SELECT '2010-02-23T12:00:00',
	   'John',
	   'Smith',
	   'Ear Test'
UNION ALL
SELECT '2010-02-28T14:00:00',
	   'Frank',
	   'Zappa',
	   'Eye Test';
 
INSERT INTO AppointmentBands
(
	StartDateTime,
	EndDateTime
)
SELECT '2010-02-18T00:00:00',
	   '2010-02-22T23:59:59'
UNION ALL
SELECT '2010-02-23T00:00:00',
	   '2010-02-27T23:59:59'
UNION ALL
SELECT '2010-02-28T00:00:00',
	   '2010-03-01T23:59:59';
?View Code MYSQL
SELECT app.*, band.AppointBandId
FROM Appointments app
INNER JOIN AppointmentBands band
	ON app.AppointmentDateTime BETWEEN band.StartDateTime AND band.EndDateTime;

MySQL non equi join

Tags: , ,
Comment (RSS)  |  Trackback

Leave a Reply

«
»