[INNER JOIN] use OR with ON

SQL++
1

i have a N1QL query :

SELECT *
FROM bucket_name parent
INNER JOIN bucket_name reply
ON parent.quoted_id = reply.id
WHERE parent.obj_type = 0
AND parent.sender_id = $1;

^ that query only return all parent that has quoted_id, now iā€™m trying to return all matched parent.quoted_id = reply.id and all empty parent.quoted_id,

the code :

SELECT *
FROM bucket_name parent
INNER JOIN bucket_name reply
ON parent.quoted_id = reply.id OR parent.quoted_id = ''
WHERE parent.obj_type = 0
AND parent.sender_id = $1;

but, i got :

[4330] No index available for ANSI join term reply

2

OR parent.quoted_id = ā€˜ā€™ there is no JOIN condition so it returns error. The Join condition must present on all OR terms.

You can try this.

SELECT *
FROM bucket_name parent
WHERE parent.obj_type = 0
AND  parent.quoted_id = ""
AND parent.sender_id = $1

UNION ALL

SELECT *
FROM bucket_name parent
INNER JOIN bucket_name reply
ON parent.quoted_id = reply.id
WHERE parent.obj_type = 0
AND parent.quoted_id  != ""
AND parent.sender_id = $1;

OR

Do Left JOIN and The (parent.quoted_id = reply.id OR parent.quoted_id = ā€˜ā€™) in WHERE clause or in parent query where clause .

SELECT *
FROM bucket_name parent
LEFT  JOIN bucket_name reply
ON parent.quoted_id = reply.id 
WHERE parent.obj_type = 0
AND (parent.quoted_id = reply.id OR parent.quoted_id = '')
AND parent.sender_id = $1;

 SELECT d.* FROM (SELECT *
    FROM bucket_name parent
    LEFT  JOIN bucket_name reply
    ON parent.quoted_id = reply.id 
    WHERE parent.obj_type = 0
    AND parent.sender_id = $1) AS d
    WHERE  d.parent.quoted_id = d.reply.id OR d.parent.quoted_id = '';
1 Like
3

any clue why my query are super slow, and there is no No index available alert

SELECT *
FROM bucket_name joined
INNER JOIN bucket_name info
ON joined.group_id = info.id 
WHERE joined.obj_type = 2
AND info.obj_type = 1
AND joined.phone = "91588278271"
4

create index ix1 on bucket_name (phone, group_id) where obj_type = 2;
create index ix2 on bucket_name (id) where obj_type = 1;

1 Like