# Which Lookup should I Use?

**URL:** <https://forum.cloverdx.com/t/which-lookup-should-i-use/2016>\
**Category:** CloverDX Platform\
**Created:** [January 23, 2019, 12:00am UTC](https://forum.cloverdx.com/t/which-lookup-should-i-use/2016 "2019-01-23T00:00:00Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![mohamedosharif](https://avatars.discourse-cdn.com/v4/letter/m/bbce88/32.png) [@mohamedosharif](https://forum.cloverdx.com/u/mohamedosharif)\
**Post date:** [January 23, 2019, 12:00am UTC](https://forum.cloverdx.com/t/which-lookup-should-i-use/2016/1 "2019-01-23T00:00:00Z")

</div>

We load a flat file into our database, the flat file has a unique column flat\_file.Column\_1 which we use to check against the database (database\_table.Column\_2) to see if the information already exists. I want to do a full outer join, so basically if flat\_file.Column\_1 = database\_table.Column\_2, i don’t want to load the data, i only want to load the data where flat\_file.Column 1 != database\_table.Column 2.

I’m not sure if there’s a way to do a lookup and just return the values that aren’t equal. I was thinking about doing a full outer join and creating a condition after the join that only passes through null values for Column 2.

However, I’m having trouble using the LookupJoin(assuming that is the right one i should be using). I defined the lookup key, and lookup table.

Query for lookup table

```auto
Select database_table.Column_2
from Database_table;

```

Lookup Key: Flat\_file.Column\_1

Where in the lookup do i define the sql?

basically want this;

```auto
select * from Database_table
where Database_table.Column_2=flat_file.Column_1

```

::Disclaimer:: I come from an Informatica background and thus the sql above is similar to what we’d use in a lookup.

Any help is appreciated.

---

<div class="post-metadata">

**Author:** ![Lukas\_Cholasta](https://yyz1.discourse-cdn.com/flex007/user_avatar/forum.cloverdx.com/lukas_cholasta/32/166_2.png) [@Lukas\_Cholasta](https://forum.cloverdx.com/u/Lukas_Cholasta)\
**Post date:** [February 1, 2019, 3:07pm UTC](https://forum.cloverdx.com/t/which-lookup-should-i-use/2016/2 "2019-02-01T15:07:16Z")

</div>

Hi,

I would rather suggest a solution where you would use a [DBInputTable](https://doc.cloverdx.com/documentation/UserGuide/index.jsp?topic=/com.cloveretl.gui.docs/docs/dbinputtable.html) to search the database table for:

```auto
select * from Database_table
where Database_table.Column_2=flat_file.Column_1

```

and if it returns zero records, write the flat file into the DB.

Searching in the database should be quite a lot faster than loading all the data from it into the lookup table and searching that. Please correct me if misunderstood your scenario.

Best regards.