10 Replies - 485 Views - Last Post: 15 August 2013 - 09:51 AM Rate Topic: -----

#1 ishkabible  Icon User is offline

  • spelling expret
  • member icon




Reputation: 1622
  • View blog
  • Posts: 5,709
  • Joined: 03-August 09

is there ever a reason to join on a predicate other than equality?

Posted 14 August 2013 - 01:43 PM

So I'm implementing an SQL query building library (yes, I'm reinventing the wheel) for Java and I am implementing joining now. I've only ever used equality in joins when I working with SQL and as I've looked for examples of joins I only see equality being used. I understand what the behavior should be but is actually ever useful or recommended? It seems like not equal could be used to find all unique parings but that's all I can think of. What would less/greater than [or equal to] help you do?

Is This A Good Question/Topic? 0
  • +

Replies To: is there ever a reason to join on a predicate other than equality?

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9089
  • View blog
  • Posts: 34,142
  • Joined: 12-June 08

Re: is there ever a reason to join on a predicate other than equality?

Posted 14 August 2013 - 01:56 PM

Would you be able to articulate a reason wny joins with a greater than/less than would be beneficial? If you think about it

http://www.codinghor...-sql-joins.html

How about concocting an example:

create table #tbl1 (lval int, sval varchar(20))

lval        sval
----------- --------------------
1           a
2           c
3           d
5           e



create table #tbl2 (lval int, sval varchar(20), dateentered datetime)

lval        sval                 dateentered
----------- -------------------- -----------------------
1           abc1                 2013-08-14 00:00:00.000
2           cd2                  2013-08-01 00:00:00.000
4           weree                2013-08-04 00:00:00.000
5           rtye                 2013-07-14 00:00:00.000




select *
from #tbl1 a 
join #tbl2 b 
  on a.lval > b.lval


lval        sval                 lval        sval                 dateentered
----------- -------------------- ----------- -------------------- -----------------------
2           c                    1           abc1                 2013-08-14 00:00:00.000
3           d                    1           abc1                 2013-08-14 00:00:00.000
5           e                    1           abc1                 2013-08-14 00:00:00.000
3           d                    2           cd2                  2013-08-01 00:00:00.000
5           e                    2           cd2                  2013-08-01 00:00:00.000
5           e                    4           weree                2013-08-04 00:00:00.000





select *
from #tbl1 a 
join #tbl2 b 
  on a.lval < b.lval

lval        sval                 lval        sval                 dateentered
----------- -------------------- ----------- -------------------- -----------------------
1           a                    2           cd2                  2013-08-01 00:00:00.000
1           a                    4           weree                2013-08-04 00:00:00.000
2           c                    4           weree                2013-08-04 00:00:00.000
3           d                    4           weree                2013-08-04 00:00:00.000
1           a                    5           rtye                 2013-07-14 00:00:00.000
2           c                    5           rtye                 2013-07-14 00:00:00.000
3           d                    5           rtye                 2013-07-14 00:00:00.000



What would you get from matching up the tbl1 row with every row in tbl2 where the index of tbl1 is greater than tbl2?
Was This Post Helpful? 0
  • +
  • -

#3 ishkabible  Icon User is offline

  • spelling expret
  • member icon




Reputation: 1622
  • View blog
  • Posts: 5,709
  • Joined: 03-August 09

Re: is there ever a reason to join on a predicate other than equality?

Posted 14 August 2013 - 03:13 PM

Quote

What would you get from matching up the tbl1 row with every row in tbl2 where the index of tbl1 is greater than tbl2?


Perhaps there was more significance to the string values in #tbl2 but I'm not really sure what the usefulness of the above output is. I understand what should be output I just can't think of a good reason to do it or a reason not to do it.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9089
  • View blog
  • Posts: 34,142
  • Joined: 12-June 08

Re: is there ever a reason to join on a predicate other than equality?

Posted 14 August 2013 - 03:20 PM

I could maybe see it as some sort of quick multi-permutation for a 'fitness' function of sorts, but nothing comes to mind on why I would want to cram random rows together that have a bare association.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5801
  • View blog
  • Posts: 12,636
  • Joined: 16-October 07

Re: is there ever a reason to join on a predicate other than equality?

Posted 14 August 2013 - 03:31 PM

The reason you join tables is because those tables have a defined relationship. Indeed, generally you should always be joining to the primary key of one table to foreign key of another. Enforcing that constraint in code would be interesting...

Once you have established that relationship in your query, then you start thinking about more complex logic. Any question I can think of that would require the kind of test you're talking about happens AFTER the tables are joined.

e.g. all the salesmen with July sales greater that 10000:
select a.*, b.tot
   from employee a
      inner join (select emp_id, sum(amount) as tot from sale where month=7 group by emp_id) b
         on a.emp_id=b.emp_id
            and b.tot>10000 -- note, I should have used "having" on the inner join...



A contra question might be: find all the salesmen with no sales in July. This could be where you're thinking about odd <> stuff. However, it doesn't work that way. The solution would be:
select a.*
   from employee a
      left outer join (select distinct emp_id from sale where month=7) b
         on a.emp_id=b.emp_id

   where b.emp_id is null


Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: is there ever a reason to join on a predicate other than equality?

Posted 14 August 2013 - 03:43 PM

Here's a few examples from my work:
  • Find all incidents with a risk ranking greater than 3
  • Find all audits that have more than 1 auditee
  • Find records between date ranges
  • Find all instances of a versioned document, where the version is greater than "this" document (i.e. - a self join)

Now sure, you can change these to WHERE clauses, but that's the case for any JOIN predicate. It depends where you want to store the logic that relates the tables or filters the data. I tend to JOIN where a relation exists, and then use the WHERE clauses for specific filtering, usually parameter driven.
Was This Post Helpful? 0
  • +
  • -

#7 ishkabible  Icon User is offline

  • spelling expret
  • member icon




Reputation: 1622
  • View blog
  • Posts: 5,709
  • Joined: 03-August 09

Re: is there ever a reason to join on a predicate other than equality?

Posted 14 August 2013 - 04:00 PM

It should be noted that I'm following SQL-92 per this grammar and that I'm actually creating a system which statically dose not allow you to write a query with a syntax error or a type error. That is, if you were able to get the thing to compile then your query is both syntactically correct and has no type errors. Other semantic errors like weather or not a table or column exist are not checked. There is also one issue that is checked dynamically by my system but it is done while the query is being constructed; it checks to make sure column names and table names are syntactically valid using regular expressions. In SQL-92 'on' is only allowed to have an extremely simple predicate, two scalar expressions separated by one of =, <>, <, >, <=. >=. To enforce this I actually make the ON clause of my queries take 3 arguments: two scalar expressions (which cannot contain AND, OR, =, <, etc...) and one of the comparison operators listed above. This question was to decide if I should actually allow all of them or just equality (in which case I would only need two arguments to the ON clause).

@baavgai: why not just leave that as JUST 'a.emp_id=b.emp_id' then at the end have a 'where' cluase that says "b.tot>1000". That seems to be what you would have to do in SQL-92 anyhow because of the restriction above.

Quote

I could maybe see it as some sort of quick multi-permutation for a 'fitness' function of sorts, but nothing comes to mind on why I would want to cram random rows together that have a bare association.


Ya; I'm kinda in the same boat. hence I am asking this question

Quote

Find all instances of a versioned document, where the version is greater than "this" document

Could you elaborate on this some? The other examples you're comparing to a constant like baavgai said but this one sounds more interesting.

edit:
I was interested in the differences in SQL and I've found out there a TON of kinds and that I was not reading a super set of SQL-92 but rather something that is just simalair. I found a better grammar here that DOES allow all the stuff you guys are talking about. So I'm going to correct it to account for these things.

So because there is a reason to have more complex expressions than "x=y" and the standard allows it I should support it. Problem solved! Thanks guys!

This post has been edited by ishkabible: 14 August 2013 - 04:19 PM

Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5801
  • View blog
  • Posts: 12,636
  • Joined: 16-October 07

Re: is there ever a reason to join on a predicate other than equality?

Posted 14 August 2013 - 06:19 PM

View Postishkabible, on 14 August 2013 - 07:00 PM, said:

@baavgai: why not just leave that as JUST 'a.emp_id=b.emp_id' then at the end have a 'where' cluase that says "b.tot>1000". That seems to be what you would have to do in SQL-92 anyhow because of the restriction above.


To an extent, this is a style decision. However, I do have some reasoning.

I prefer to keep the dependent "where" constraints coupled with their table joins. This documents not just the join, but the other criteria associated with that table in the statement. I find this makes it easier remove that table join from a complex query. Ultimately, it modularized elements of the statement, making logic reuse more straight forward.

The other style would be to ONLY place the joined fields in the join and put all other criteria in the where at the bottom. Here, the joined fields are immediately clear, but the rest criteria related to join is located elsewhere.

I write SQL in text editors. Sometimes very large statements. I've experimented with many ways to organize the mess that SQL can be. I find keeping the criteria with the table join works best for me.
Was This Post Helpful? 1
  • +
  • -

#9 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: is there ever a reason to join on a predicate other than equality?

Posted 14 August 2013 - 08:47 PM

View Postishkabible, on 15 August 2013 - 10:00 AM, said:

Quote

Find all instances of a versioned document, where the version is greater than "this" document

Could you elaborate on this some? The other examples you're comparing to a constant like baavgai said but this one sounds more interesting.

Imagine a system such as SharePoint where you can have multiple versions of a document uploaded, and you want to retrieve the history of a document after, say, verion 3:
SELECT
	*
FROM Documents
INNER JOIN Documents AS SubsequentDocuments
	ON SubsequentDocuments.DocumentStreamID = Documents.DocumentStreamID
	AND SubsequentDocuments.Version > Documents.Version
WHERE Documents.DocumentStreamID = @ParamDocumentStreamID
AND Documents.Version = 3


Was This Post Helpful? 1
  • +
  • -

#10 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: is there ever a reason to join on a predicate other than equality?

Posted 14 August 2013 - 08:56 PM

Another example is where you have gridded data, and you want to find out all locations within that grid, for example:
SELECT
	Locations.Description
FROM Zones
INNER JOIN ZoneGridSquares ON ZoneGridSquares.ZoneID = Zones.ID
INNER JOIN GridSquares ON GridSquares.ID = ZoneGridSquares.GridSquareID
INNER JOIN Locations
	ON Locations.XCoordinate > GridSquares.XCoordinateLeft
	AND Locations.XCoordinate <= GridSquares.XCoordinateRight
	AND Locations.YCoordinate > GridSquares.YCoordinateLeft
	AND Locations.YCoordinate <= GridSquares.XCoordinateRight
WHERE Zones = @ParamZoneID
AND Locations.Type = 'Tourist Attractions'


...though I would use the BETWEEN clause rather than > and <=. Still, BETWEEN is an example of a non-equivalence clause.
Was This Post Helpful? 1
  • +
  • -

#11 ishkabible  Icon User is offline

  • spelling expret
  • member icon




Reputation: 1622
  • View blog
  • Posts: 5,709
  • Joined: 03-August 09

Re: is there ever a reason to join on a predicate other than equality?

Posted 15 August 2013 - 09:51 AM

That last one is the kind of thing I was looking for!

It also seems that there is agreement on when to use JOIN for filtering and when to use WHERE which I'm going to try to follow as I write queries in the future to see if I agree.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1