Question: **Complicated JOIN query as:**

```
SELECT 
  FIRST.NAME,
  SECOND.FIRST_NAME,
  SECOND.LAST_NAME
FROM FIRST_TABLE FIRST
LEFT JOIN SECOND_TABLE SECOND 
  ON (SECOND.FIRST_NAME = FIRST.NAME OR SECOND.LAST_NAME = FIRST.NAME)

```

Will result with bad performance.

***How to get better performance?***

Answer: 
THIS ANSWERS THE ORIGINAL VERSION OF THE QUESTION.

You may be better off with `exists`:

```
SELECT FIRST.ID, FIRST.NAME
FROM FIRST_TABLE FIRST 
WHERE EXISTS (SELECT 1 FROM SECOND_TABLE SECOND WHERE SECOND.FIRST_NAME = FIRST.NAME) OR
      EXISTS (SELECT 1 FROM SECOND_TABLE SECOND WHERE  SECOND.LAST_NAME = FIRST.NAME);

```

Then for performance, you want indexes on `SECOND_TABLE(LAST_NAME)` AND `SECOND_TABLE(FIRST_NAME)`.
You could try this, see what you get. It would depend a lot on the indexes on `FIRST_NAME` and `LAST_NAME`, but it avoids the `OR`.

```
SELECT 
  FIRST.NAME,
  SECOND.FIRST_NAME,
  SECOND.LAST_NAME
FROM FIRST_TABLE FIRST
  LEFT JOIN (
     SELECT S1.FIRST_NAME as TEST_NAME,
            S1.FIRST_NAME,
            S1.LAST_NAME       
     FROM   SECOND_TABLE S1
     WHERE  S1.FIRST_NAME <> S1.LAST_NAME
     UNION ALL
     SELECT S2.LAST_NAME as TEST_NAME,
            S2.FIRST_NAME,
            S2.LAST_NAME       
     FROM   SECOND_TABLE S2
   ) AS SECOND
  ON FIRST.NAME = SECOND.TEST_NAME

```