CODEWITHSUNDEEP
sqloracle-databasejoin
Jace Lee
Jace Lee

Reputation: 49

How to do cross join without cross in oracle sql

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

Upvotes: 0

Views: 2310

Answers (3)

user1717259
user1717259

Reputation: 2863

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

Upvotes: 2

Jace Lee
Jace Lee

Reputation: 49

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.

Upvotes: -3

GMB
GMB

Reputation: 222482

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

Upvotes: 5

Related Questions