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

```

Answer: 
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.
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](https://stackoverflow.com/questions/348363/what-is-the-best-practice-for-storing-uploaded-images))