# SQL question **URL:** \ **Category:** Factual Questions\ **Created:** [October 27, 2015, 12:46pm UTC](https://boards.straightdope.com/t/sql-question/735870 "2015-10-27T12:46:02Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [October 27, 2015, 12:46pm UTC](https://boards.straightdope.com/t/sql-question/735870/1 "2015-10-27T12:46:02Z")
I write quite a bit of SQL code, but I’ve never come across this before: INNER { OPTION SWAP\_ORDER=“True”, SEMIJOIN=“True” } JOIN and googling isn’t getting me anything that makes sense…what the heck is the SWAP\_ORDER option? Everything else about the query looks straightforward but I can’t figure out what this option is doing and if it’s something useful. Any SQL experts here know what it does? ---
**Author:** ![bob\_2](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bob_2/32/3341_2.png) [@bob\_2](https://boards.straightdope.com/u/bob_2)\ **Post date:** [October 27, 2015, 12:56pm UTC](https://boards.straightdope.com/t/sql-question/735870/2 "2015-10-27T12:56:29Z")
Is [this](http://www-01.ibm.com/support/knowledgecenter/SSEP7J_10.1.1/com.ibm.swg.ba.cognos.vvm_reference_guide.10.1.1.doc/r_swap_ord.html%23SWAP_ORD) any help ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [October 27, 2015, 12:58pm UTC](https://boards.straightdope.com/t/sql-question/735870/3 "2015-10-27T12:58:09Z")
Not really. That’s the only site I can find, and I have no idea what they mean by “the order of the join”. ---
**Author:** ![yellowjacketcoder](https://avatars.discourse-cdn.com/v4/letter/y/4bbf92/32.png) [@yellowjacketcoder](https://boards.straightdope.com/u/yellowjacketcoder)\ **Post date:** [October 27, 2015, 1:12pm UTC](https://boards.straightdope.com/t/sql-question/735870/4 "2015-10-27T13:12:31Z")
At first I thought that inner joins weren’t commutative, but I think that they are, and this is just a bad example. [I find this reference helpful](http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins). It would make a difference for right and left joins, but then you could just change a single keyword instead of adding an option. Maybe it makes a difference to the query plan, so it’s included for performance reasons. ---
**Author:** ![Mithras](https://avatars.discourse-cdn.com/v4/letter/m/f04885/32.png) [@Mithras](https://boards.straightdope.com/u/Mithras)\ **Post date:** [October 27, 2015, 1:13pm UTC](https://boards.straightdope.com/t/sql-question/735870/5 "2015-10-27T13:13:17Z")
It’s a query tuning thing. The order of (inner) joins can affect query performance but not the actual results you get. Though the optimizer doesn’t just run things in the order you have them written, sometimes it comes up with an execution plan that is very far from optimal. In that case, it might help to give it some guidance as to the order of your joins. So the swap\_order you’ve run into is probably the result of someone trying to speed up a slow query. ---
**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\ **Post date:** [October 27, 2015, 1:13pm UTC](https://boards.straightdope.com/t/sql-question/735870/6 "2015-10-27T13:13:45Z")
Yeah, that’s not really a helpful manual reference. The only thing I can think of is that: select \* FROM table1 INNER { OPTION SWAP\_ORDER=“True”} JOIN table2 on table1.field1 = table2.fieldB is equivalent to select \* FROM table2 INNER JOIN table1 on table2.fieldB = table1.field1 That is, the fields from table2 will be returned towards the left, and the fields from table1 towards the right. Can’t really figure out why this would be important, even in “queries with complex joins.” (At least, not for anybody who can write out an explicit select list…) ETA: Hmm… a few of the other opinions that appeared while I was typing this might be on better tracks… ---
**Author:** ![Mithras](https://avatars.discourse-cdn.com/v4/letter/m/f04885/32.png) [@Mithras](https://boards.straightdope.com/u/Mithras)\ **Post date:** [October 27, 2015, 1:18pm UTC](https://boards.straightdope.com/t/sql-question/735870/7 "2015-10-27T13:18:28Z")
> [@chrisk](#): > > Yeah, that’s not really a helpful manual reference. The only thing I can think of is that: > > select \* FROM table1 INNER { OPTION SWAP\_ORDER=“True”} JOIN table2 on table1.field1 = table2.fieldB > > is equivalent to > > select \* FROM table2 INNER JOIN table1 on table2.fieldB = table1.field1 > > That is, the fields from table2 will be returned towards the left, and the fields from table1 towards the right. Can’t really figure out why this would be important, even in “queries with complex joins.” (At least, not for anybody who can write out an explicit select list…) > > ETA: Hmm… a few of the other opinions that appeared while I was typing this might be on better tracks… And I’m thinking your idea might be the right one. It doesn’t sound practical, but re-reading the manual entry, I’m inclined to think this is what it’s for. ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [October 27, 2015, 1:30pm UTC](https://boards.straightdope.com/t/sql-question/735870/8 "2015-10-27T13:30:18Z")
> [@yellowjacketcoder](#): > > At first I thought that inner joins weren’t commutative, but I think that they are, and this is just a bad example. > > [I find this reference helpful](http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins). It would make a difference for right and left joins, but then you could just change a single keyword instead of adding an option. > > Maybe it makes a difference to the query plan, so it’s included for performance reasons. Am I missing something on this page? I’m not finding any reference to the SWAP\_ORDER option there. I get the difference between left/right, inner/outer, etc., as I use them all the time. I’m just trying to see how this piece of code is doing anything meaningful. ---
**Author:** ![yellowjacketcoder](https://avatars.discourse-cdn.com/v4/letter/y/4bbf92/32.png) [@yellowjacketcoder](https://boards.straightdope.com/u/yellowjacketcoder)\ **Post date:** [October 27, 2015, 1:33pm UTC](https://boards.straightdope.com/t/sql-question/735870/9 "2015-10-27T13:33:16Z")
> [@batsto](#): > > Am I missing something on this page? I’m not finding any reference to the SWAP\_ORDER option there. I get the difference between left/right, inner/outer, etc., as I use them all the time. I’m just trying to see how this piece of code is doing anything meaningful. It was just a reference I use a lot. It was helpful to point out that switching the order of the join won’t affect the result set, but as \*\*Mithras \*\*pointed out, it probably affects the query plan ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [October 27, 2015, 1:36pm UTC](https://boards.straightdope.com/t/sql-question/735870/10 "2015-10-27T13:36:27Z")
So how is that command more effective than just typing the correct join in the first place? I tried the query that way, and then the same query but replaced it with an inner join and the execution time was almost identical—the 2nd way was about 0.2 seconds faster. ---
**Author:** ![aktep](https://avatars.discourse-cdn.com/v4/letter/a/f0a364/32.png) [@aktep](https://boards.straightdope.com/u/aktep)\ **Post date:** [October 27, 2015, 1:49pm UTC](https://boards.straightdope.com/t/sql-question/735870/11 "2015-10-27T13:49:11Z")
From **bob++**'s link, these options appear to be Cognos-specific. IME, Cognos can make some pretty inefficient calls to the database, and I guess these options were added to provide a little help. Also in my experience, the optimizer in most RDBMS does a better job than I can on inner joins, and most slowness issues in those queries could be fixed with an index. So, I’m having trouble coming up with an non-completely complex example where this SWAP\_ORDER flag would make a real difference. I think, though, since the SEMIJOIN flag is also set, that there might be something going on. It looks like the SEMIJOIN will pre-fetch the values from the right side of the join and, if the list is small enough, convert the join to in IN clause. So, INNER {OPTION SWAP\_ORDER=“true”, SEMIJOIN="true} JOIN on a.id=b.id would become, more or less WHERE b.id IN (1,2,3,5,10,30) This could probably provide some improvement in conditions where the number of rows in a is much smaller than the number of rows in b. It does feel, though, like something where someone went to a site like stackoverflow and said “my query is slow-HALP!”, and found out about this from someone who used it once in a esoteric edge case, and it sort of worked, so now they try it whenever query perfomance needs a boost. I think if we came across something like this on my team during code review, we’d try to re-write the query to make it a bit more understandable what is going on. ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [October 27, 2015, 1:55pm UTC](https://boards.straightdope.com/t/sql-question/735870/12 "2015-10-27T13:55:11Z")
The guy who wrote this does a lot of work in Cognos, so that makes some sense to me. Unfortunately he’s out on disability for the next few months, so i can’t ask him what he was trying to do. ---
**Author:** ![yellowjacketcoder](https://avatars.discourse-cdn.com/v4/letter/y/4bbf92/32.png) [@yellowjacketcoder](https://boards.straightdope.com/u/yellowjacketcoder)\ **Post date:** [October 27, 2015, 4:10pm UTC](https://boards.straightdope.com/t/sql-question/735870/13 "2015-10-27T16:10:37Z")
> [@batsto](#): > > So how is that command more effective than just typing the correct join in the first place? I tried the query that way, and then the same query but replaced it with an inner join and the execution time was almost identical—the 2nd way was about 0.2 seconds faster. It’s going to greatly depend on the query - I’m sure we’ve both written queries that took minutes when written one way and seconds when written another, even though they return the same result set. It could be that for your, probably fairly simple, test case, there’s not much of a difference, but for a complex query with subqueries and dozens of where clauses and billions of database rows, there’s a big difference, and rewriting it would be a pain. ---
**Author:** ![Senegoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/senegoid/32/6606_2.png) [@Senegoid](https://boards.straightdope.com/u/Senegoid)\ **Post date:** [October 28, 2015, 7:45pm UTC](https://boards.straightdope.com/t/sql-question/735870/14 "2015-10-28T19:45:49Z")
> [@batsto](#): > > The guy who wrote this does a lot of work in Cognos, so that makes some sense to me. Unfortunately he’s out on disability for the next few months, so i can’t ask him what he was trying to do. ## This sounds like the original programmer could make better use of those options consisting of program lines beginning with ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [October 28, 2015, 9:02pm UTC](https://boards.straightdope.com/t/sql-question/735870/15 "2015-10-28T21:02:34Z")
> [@aktep](#): > > From **bob++**'s link, these options appear to be Cognos-specific. IME, Cognos can make some pretty inefficient calls to the database, and I guess these options were added to provide a little help. > > Also in my experience, the optimizer in most RDBMS does a better job than I can on inner joins, and most slowness issues in those queries could be fixed with an index… I only use SQL Server and Oracle now and those optimizers are so good you don’t have to tinker with them. As Aktep said, usually all you need to do is to add an index. In the old days you would try strange things like changing the order of tables in the FROM clause, or WHERE a.key=b.key AND a.key=c.key AND b.key=c.key