Question: I need to select all records on `database1` where `database1.table1.foreign_key` does not exist in `database2.table2.some_id`.

The other questions here talk about joining both databases through their table's foreign keys but that doesn't work on my case since I'm looking for records where their foreign keys do not exist on the other database's table.

Here's some sample data:

On `database1`.`table1`:

```
id - name - foreign_key
-----------------------

1 - No need - 253

2 - Don't need - 627

3 - Need this - 166

```

On `database2`.`table2`:

```
id - name - some_id
-------------------

1 - Sample - 627

2 - Another - 253

```

So with those sample data, after running the query, I expect to get

`3 - Need this - 166`.

Here is my current solution which doesn't work.

```
SELECT fish_system_sandbox.receivables.*
FROM fish_system_sandbox.receivables
WHERE fish_system_sandbox.receivables.catch_datum_id NOT IN (SELECT inventory_sandbox2.holdings.catch_id FROM inventory_sandbox2.holdings)

```

This returns an empty result, and does not produce errors.

Answer: 
try this:

```
 select table_1.* from table_1 left join table_2
 on table_1.foreign_key=table_2.some_id
 where table_2.id is null

```

or alternatively:

```
select table_1.* from table_1
where foreign_key not in (select some_id from table_2)

```

these queries gives records from *table\_1* whose *ids* are not present in *table\_2*

* The first query runs faster in **MySQL**
* The first query may returns some records more than once if the `some_id` is not a `unique` key. To prevent it, you may use `select distinct` instead of `select` .
You can try it-

```
SELECT t1.* 
FROM db1.table1 t1 
LEFT JOIN db2.table2 t2 ON t1.fk=t2.some_id 
WHERE t2.id IS NULL;

```

Note: Join fields (t1.fk and t2.some\_id) should be indexed for better performance.