3

Is it valid if I try doing a LEFT JOIN between two tables without using an ON clause. How does the SQL engine treat such joins? Would it be treated as a CROSS JOIN or will I be getting an error.

zero323
  • 322,348
  • 103
  • 959
  • 935
Teja
  • 13,214
  • 36
  • 93
  • 155
  • 6
    "or will I be getting an error" --- what if you put any effort to your problem **before** you ask? – zerkms Nov 02 '16 at 06:15
  • 1
    Possible duplicate of [Can I use mysql JOIN without ON condition?](http://stackoverflow.com/questions/16470942/can-i-use-mysql-join-without-on-condition) – Eugene Nov 02 '16 at 06:16
  • 1
    I dont have a working laptop and I posted the ques from my mobile.. sorry about that... – Teja Nov 02 '16 at 06:16
  • SQLfiddle works on a smartphone – Strawberry Nov 02 '16 at 06:44
  • Rather than jumping straight to which keywords you'll randomly append together in the hopes of getting your desired result - maybe explain to us what you're doing and what results you're hoping to achieve. Hopefully, with sample data and a table of expected results. – Damien_The_Unbeliever Nov 02 '16 at 07:12
  • **NO.** I add more simply to get past the minimum length. – Paul Maxwell Nov 02 '16 at 07:48
  • Please wait for @Teja response before declaring this as duplicate. – David דודו Markovitz Nov 02 '16 at 08:14
  • There's a NON-ANSI compliant format you could use if you absolutely want to avoid using an ON clause (though why would be beyond me). `SELECT * FROM t1, t2 WHERE t1.ID = t2.F_ID`. Keep in mind this will work for INNER joins, but will give different results if you'd try doing an OUTER join. – SchmitzIT Nov 02 '16 at 10:06
  • @SchmitzIT I don't recall MySQL having an non-ansi outer join syntax like Oracle's (+) http://stackoverflow.com/questions/6658334/syntax-for-outer-joins-in-mysql so I don't see the relevance of proposing the old-style inner join – Paul Maxwell Nov 02 '16 at 10:37
  • @Used_By_Already That's the issue when an OP flags it with different tags ;) I went for straight SQL. Didn't consider MySQL (or Postgres, for that matter). – SchmitzIT Nov 02 '16 at 12:37

2 Answers2

14

If I'm guessing your intention right -
It seems you are looking for something similar to CROSS JOIN but you still want to return the left side even if the right side is empty.
You can indeed implement this using LEFT JOIN.
For LEFT JOIN you must have ON but you can use ON TRUE. 

create table t1 (i int);
insert into t1 (i) values (1),(2),(3);
create table t2 (i int);

select * from t1 cross join t2;

(empty result set)

select * from t1 left join t2 on true;

i i      
- ------ 
1 (null) 
2 (null) 
3 (null)
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
  • Which causes the join to be the equivalent of a cross join...... there simply is no point to using left join without a qualification to that join where some rows are matched and some might not be matched. ON TRUE does not permit some rows to be unmatched. – Paul Maxwell Nov 02 '16 at 07:47
  • There is a single "edge case" where `left join on true` would permit rows to be returned but where `cross join` would not. I had not considered that edge case in my earlier reply. For my omission I apologize. However if you re-read that reply it states `there simply is no point` and there is no practical reason for having a table with no rows 'left joined on true' to a table with rows. And, it is still not possible to have a left join without ON (which was the question) – Paul Maxwell Nov 02 '16 at 09:31
  • @Used_By_Already - You'll probably want to take a look at the following post :-) http://stackoverflow.com/questions/41010016/how-to-get-dummy-value-for-xml-empty-tag-in-table-using-oracle/41011346#41011346 – David דודו Markovitz Dec 07 '16 at 07:45
0

While Dudu's code is admittedly neat, I did have to actually run it to figure out what it does.

It seems to me you have two scenarios i.e. (1) empty table and (2) non-empty table. The table expressions for each can be unioned together. This may make things easier to understand.

Also, I would recommend you avoid nulls; three-valued logic is not worth the bother. Here I'm guessing a default value of -99 is suitable:

create table t1 (c1 int);
insert into t1 values (1),(2),(3);
create table t2 (c2 int);

select c1, c2
  from t1 CROSS JOIN t2
union
select c1, -99 as c2
  from t1
 where not exists ( select * from t2 );
onedaywhen
  • 55,269
  • 12
  • 100
  • 138