0

I have two tables, hotel and interests. I'd like to find the distance between each hotel and each interests. I tried using CROSS JOIN but my boss told me to not use cross join. Is there any other way? Implicit join and ON 1=1 is not allowed either. I was told to use join and brackets but I don't understand how is it possible to perform a cross join without using cross join. I'm using oracle sql by the way

GMB
  • 216,147
  • 25
  • 84
  • 135
Jace Lee
  • 49
  • 8
  • In some versions of SQL (e.g. MySQL), doing an _inner_ join without an `ON` condition defaults to being a cross join. This isn't the case with Oracle though, and an inner join without `ON` will error out. In practice, your bigger problem here is probably going to be finding a way to compute those distances. – Tim Biegeleisen Oct 22 '19 at 15:41
  • Ah sorry, forgot to mention. We have a function that calculates distance between coordinates – Jace Lee Oct 22 '19 at 15:42
  • How that function will be useful in joins? – Popeye Oct 22 '19 at 16:08
  • 4
    *" I tried using CROSS JOIN but my boss told me to not use cross join"* So what is the point of this? There's no apparent value from implementing a cross join without using CROSS JOIN notation other than confusing the other poor blighters who have to work with your code. – APC Oct 22 '19 at 16:48
  • Possible duplicate of [CROSS JOIN vs INNER JOIN in SQL Server 2008](https://stackoverflow.com/questions/17759687/cross-join-vs-inner-join-in-sql-server-2008) – philipxy Oct 22 '19 at 23:25
  • 1
    What does "don't use cross join" mean? We can't tell you an answer until you clarify this. *Every* join (explicit/keyword/`JOIN` or implicit/comma/`,`) does a cross join, but then we only keep some of the rows per `ON`, `WHERE`, etc & the DBMS optimizes the implementation. PS Please explain the exact task you are to do. [mre] PS Clarify via edits, not comments. – philipxy Oct 22 '19 at 23:30
  • After you find out from your boss what they meant by whatever they said that you report here as "don't use cross join" & "use join and brackets" please let us know. That hopefully won't leave any question to ask or answer, but what you say here about what your boss said just is not clear. PS Presumably a reasonable calculation involves recursively calculating longer distances only from relevant slightly shorter distances & also presumably it is in comparison with that that cross join is undesirable. – philipxy Oct 24 '19 at 23:50

3 Answers3

5

Well, if both tables have no column in common, you can do a NATURAL JOIN: it will generate a cartesian product (also called CROSS JOIN):

select t1.*, t2.* from table1 t1 natural join table2 t2

Demo on DB Fiddle:

with 
    a as (select 1 as ida from dual union all select 2 from dual),
    b as (select 3 as idb from dual union all select 4 from dual)
select a.*, b.* from a natural join b

IDA | IDB
--: | --:
  1 |   3
  1 |   4
  2 |   3
  2 |   4

Other options (but apparently you are not allowed to use them):

Explicit join on 1=1

select t1.*, t2.* from table1 t1 inner join table2 t2 on 1 = 1

Implicit join:

select t1.*, t2.* from table1 t1, table2 t2
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Your natural join option [does not seem to work](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=5e356d024ee7f40625ec64d28e463a23) on Oracle. – Tim Biegeleisen Oct 22 '19 at 15:46
  • It works with two different tables though (see the link in my answer). With a self-join, it [seems to work with this syntax](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=c97e86ac0449511fe86d2548aaec1159) – GMB Oct 22 '19 at 15:55
  • 1
    Fair enough +1, in any case your other two options seem good. – Tim Biegeleisen Oct 22 '19 at 15:56
2

You don't need any extra syntax to carry out a cross join using Oracle sql.

Just listing the tables and not giving a join condition will give you the cartesian product that you're after

select table1.*, table2.* from table1, table2

user1717259
  • 2,717
  • 6
  • 30
  • 44
-3

After searching around I found that you can use < and > in ON. So I tried this:

SELECT * FROM a JOIN b ON (a.x<>b.x OR a.x=b.x)

And it seems to work just like a CROSS JOIN.

Jace Lee
  • 49
  • 8
  • This will filter out `null` values in `a.x` or `b.x`: [db fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=443c3319ea8319eaf669b1034d1b0acc) – GMB Oct 22 '19 at 19:16