how to join two tables in oracle on blob column?

Question

how to join two tables in oracle on blob column

when this query is executed "SQL command not properly ended" error message is appearing

select name,photo 
from tbl1 join tbl2 on tbl1.photo = tbl2.photo

Are you _really_ using the ancient, outdated and unsupported 8i version? If yes, then that version does not support explicit `JOIN` syntax. But this is a horrible design in the first place — , Mar 13 '16 at 10:32

score 2 · Accepted Answer · answered Mar 13 '16 at 10:24

First, it seems very very odd to have a design where you are storing the same blob in two different tables and very odd that you would want to join on an image. That doesn't seem like a sensible design.

You've tagged this for Oracle 8i. That is an ancient version of Oracle that didn't support the SQL 99 join syntax. You would need to do the join in the where clause instead. You can't directly test for equality between two blob values. But you can use dbms_lob.compare

select name,photo 
  from tbl1,
       tbl2 
 where dbms_lob.compare(tbl1.photo, tbl2.photo) = 0

This will be rather hideous from a performance perspective. You'll have to compare every photo from tbl1 against every photo from tbl2 and comparing two lobs isn't particularly quick. If you are really intent on comparing images, you are probably better off computing a hash, storing that in a separate column that is indexed, and then comparing the hashes rather than comparing the images directly.

score 0 · Answer 2 · edited May 23 '17 at 12:17

The code:

SELECT 
    name, photo
FROM 
    tbl1 T1
INNER JOIN 
    tbl2 T2
ON
    T1.photo = T2.photo

If not running fine, you would have to make few changes in your TABLE structure:
1. ...Add a new TABLE named as IMAGES having columns (image_id, image_blob)
2. ...And then you you would have to change the: tbl1's blob and tbl2's blob to image_id
3. ...Then perform the JOIN on the basis of COLUMN named as image_id

NOTE: You can not perform GROUP BY, JOIN(any JOIN), CONCAT operations on BLOB datatype

SUGGESTION: save the Paths to images in the DATABASE and save the IMAGES somewhere on that SERVER's Directory (As saving images in BLOB in the DATABASE is not a good practice..... To ensure what i said VISIT HERE)

Oracle 8i did not support the `JOIN` operator – Mar 13 '16 at 10:33 — , Mar 13 '16 at 10:33

how to join two tables in oracle on blob column?

2 Answers2