How does Greater than operator works in Joins

Question

How does Greater than operator works in Joins

khurmi.navpreet

Old Hand

Points: 376
More actions
September 25, 2013 at 9:39 pm

#283142

Hello,
I have writing SQL Queries for sometime now and writing inner joins, but always used the equal to operator(=) for joining 2 tables. Just recently I came across a SQL written by my co worker and he is uisng greater than and equal to operator (>=) to join tables.
How in the earth you can join 2 tables with this operator(>=). I always thought of joining 2 tables if they have a matching value in the columns and using the equal operator to do that.
He is doing some kind of a rolling average and using this (>=) operator.
can someone please explain, how exactly this (>=) operator works in joins.
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 1

khurmi.navpreet (9/25/2013)
Hello,
I have writing SQL Queries for sometime now and writing inner joins, but always used the equal to operator(=) for joining 2 tables. Just recently I came across a SQL written by my co worker and he is uisng greater than and equal to operator (>=) to join tables.
How in the earth you can join 2 tables with this operator(>=). I always thought of joining 2 tables if they have a matching value in the columns and using the equal operator to do that.
He is doing some kind of a rolling average and using this (>=) operator.
can someone please explain, how exactly this (>=) operator works in joins.
Thanks.

T-SQl provides the facility of "EQUI JOIN" i.e. joining tables on the "=",

In other cases, for example say, it uses "<>" to join the tables, it will become like a Cross join of non equality, !

and in case of ">=", it will work, but will bring the extra rows of greater compare values (what i see here is a bad approach.)

Rest wait for the SSC champions to write their reviews,,, (me also waiting for a proper explanation.)

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Have a read of this article --> http://www.sqlservercentral.com/articles/T-SQL/61539/%5B/url%5D

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

busraker Ten Centuries Points: 1116 More actions · Answer 3

Don't listen to RBAR-phobes ... there are plenty of valid uses for a comparison operator in a SQL Join.

Bad = using it to make 1,000,000's of Cartesian products to filter the few you want in a WHERE statement

Good = take for instance:

Say you have a table of salespeople with their hire dates (SalesPerson):

SalesPersonID

HireDate

And a table of orders (SalesOrder)

OrderID

SalesPersonID

OrderDate

OrderAmount

How many orders did they have in the first 100 days? If you want to see all SalesPeople

SELECT p.SalesPersonID, ISNULL(SUM(o.OrderAmount),0) AS TotalAmount

FROM SalesPerson p LEFT JOIN SalesOrder o ON p.SalesPersonID = o.SalesPersonID AND DATEADD(d,101,p.HireDate) < o.OrderDate

GROUP BY p.SalesPersonID

Which could also simply be, if you want to see only salespeople with orders:

SELECT p.SalesPersonID, ISNULL(SUM(o.OrderAmount),0) AS TotalAmount

FROM SalesPerson p LEFT JOIN SalesOrder o ON p.SalesPersonID = o.SalesPersonID

WHERE DATEADD(d,101,p.HireDate) < o.OrderDate

GROUP BY p.SalesPersonID

Many Time SQL writers will use it to move code from a WHERE clause into a FROM clause out of personal preference, or the ability to get NULL results in a LEFT JOIN

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It works very badly most of the time because of some limitations of the optimiser. It'll usually result in multiple scans of one or both tables in the join.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

busraker (9/26/2013)
Good = take for instance:
Say you have a table of salespeople with their hire dates (SalesPerson):
<snip>
How many orders did they have in the first 100 days? If you want to see all SalesPeople
SELECT p.SalesPersonID, ISNULL(SUM(o.OrderAmount),0) AS TotalAmount
FROM SalesPerson p LEFT JOIN SalesOrder o ON p.SalesPersonID = o.SalesPersonID AND DATEADD(d,101,p.HireDate) < o.OrderDate
GROUP BY p.SalesPersonID
Which could also simply be, if you want to see only salespeople with orders:
SELECT p.SalesPersonID, ISNULL(SUM(o.OrderAmount),0) AS TotalAmount
FROM SalesPerson p LEFT JOIN SalesOrder o ON p.SalesPersonID = o.SalesPersonID
WHERE DATEADD(d,101,p.HireDate) < o.OrderDate
GROUP BY p.SalesPersonID

In neither case are you joining on an inequality. Both of those are just a normal inequality predicate comparing a column with a literal. An inequality in a join would be an inequality predicate comparing a table column with another table column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dan-572483 SSCoach Points: 15870 More actions · Answer 6

I would think that using anything to join tables that's not a unique key in at least one of the tables would be very risky.

busraker Ten Centuries Points: 1116 More actions · Answer 7

Okay ... a better example.

I am a police supervisor wondering how many crimes are committed during my officers shifts:

Officer

---------

OfficerID

ShiftID

ShiftStart

ShiftEnd

Crime

------

CrimeID

CrimeDate

SELECT

OfficerID,

ShiftStart,

ShiftEnd,

COUNT(CrimeID)

FROM

Officer o

INNER JOIN Crime c ON o.ShiftStart <= i.CrimeDate AND o.ShiftEnd >= i.CrimeDate

GROUP BY

OfficerID,

ShiftStart,

ShiftEnd

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

SELECT

OfficerID,

ShiftStart,

ShiftEnd,

CrimeTotal

FROM

Officer o

CROSS APPLY (SELECT COUNT(*) AS CrimeTotal FROM Crime c WHERE CrimeDate BETWEEN o.ShiftStart AND o.ShiftEnd) c

May well perform better, though would need testing. It's essentially the same as calling a function, so the subquery executes only once for each row in the outer table, no chance that the optimiser will end up generating plans with multiple scans for the second table, plus with decent indexes the count will be easier to do since it's not grouping by columns in the other table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

busraker Ten Centuries Points: 1116 More actions · Answer 9

Thanks Gail ... I just moved from SQL Server 2000 to 2008 this year so tips like this really help!