clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1716893 fiddles created (23847 in the last week).

CREATE TABLE t AS SELECT 1 AS id, 'a' AS cat FROM dual UNION SELECT 2, 'b' FROM dual
2 rows affected
 hidden batch(es)


CREATE TABLE t_ch AS SELECT 1 AS id, 1 AS t_id FROM dual UNION SELECT 2, 3 FROM dual
2 rows affected
 hidden batch(es)


SELECT * FROM t LEFT JOIN t_ch ON t.id = t_ch.t_id WHERE t.id < 2
ID CAT ID T_ID
1 a 1 1
 hidden batch(es)


-- transformed into select * from T, T_CH where ( T.ID < 2 and T.ID = T_CH.T_ID(+) )
ID CAT ID T_ID
1 a 1 1
 hidden batch(es)


SELECT * FROM t LEFT JOIN t_ch ON t.id = t_ch.t_id AND t.id < 2
ID CAT ID T_ID
1 a 1 1
2 b
 hidden batch(es)


-- transformed into select * from T, T_CH where ( T.ID = T_CH.T_ID(+) and T.ID < 2 )
ID CAT ID T_ID
1 a 1 1
 hidden batch(es)


-- CASE 2 select * from T, T_CH where (T.ID = T_CH.T_ID(+) AND T_CH.ID = 1) union all select * from T, T_CH where (T_CH.ID = 1 AND T.ID = T_CH.T_ID(+))
ID CAT ID T_ID
1 a 1 1
1 a 1 1
 hidden batch(es)


select * from T join T_CH on T.ID = T_CH.T_ID where T_CH.ID = 1 union all select * from T left outer join T_CH on T.ID = T_CH.T_ID where T_CH.ID = 1
QCSJ_C000000000300000 CAT QCSJ_C000000000300001 T_ID
1 a 1 1
1 a 1 1
 hidden batch(es)