Select from a table if conditions met in joined table I have two tables

members

+-----------+
|id   | name|
+-----------+-
| 1   | john | 
| 2   | adam | 
| 3   | sara | 
+-----------+-

keys

+-----------+
|id   | color|
+-----------+-
| 1   | blue | 
| 2   | green| 
| 3   | red  | 
+-----------+-


and a third joined table

members_keys

+-----------+---------
| member_id | key_id |
+-----------+---------
| 1         | 1      | 
| 1         | 3      | 
| 2         | 1      | 
+-----------+--------

both rows are assigned as primary key and foreign key


What I need is to get john's keys (keys 1 and 3 in this example)

This is what I am doing

select * from keys join members_keys on keys.id = members_keys.key_id where members_keys.member_id = 1;


from my limited testing I got the required rows, but I feel there is a better way to do it and this solution looks like wasting a lot of resources (comparing each row in keys with all rows in members_keys)
Comparing each row in keys with all rows in members_keys is not how the join is actually implemented.

This is the query plan without any indexes:

create table members(id int, name text);
create table keys(id int, color text);
create table members_keys(member_id int, key_id int);
explain select * from keys join members_keys on keys.id = members_keys.key_id where members_keys.member_id = 1;
                                QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=38.39..66.55 rows=70 width=44)
   Hash Cond: (keys.id = members_keys.key_id)
   -&gt;  Seq Scan on keys  (cost=0.00..22.70 rows=1270 width=36)
   -&gt;  Hash  (cost=38.25..38.25 rows=11 width=8)
         -&gt;  Seq Scan on members_keys  (cost=0.00..38.25 rows=11 width=8)
               Filter: (member_id = 1)


The database first goes through all rows of the members_keys table, and only those with the matching member_id are inserted into a hash table. It then goes through all rows in the keys table (once), and searches for matching entries in the hash table. (Hash lookups are very fast; their speed is ideally independent of the size of the hash table.)

If we add primary keys with their implicit indexes, then the join works pretty much the same, except that the member_key = 1 check can be sped up with index lookups:

alter table members add primary key(id);
alter table keys add primary key(id);
alter table members_keys add primary key(member_id, key_id);
explain select * from keys join members_keys on keys.id = members_keys.key_id where members_keys.member_id = 1;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Hash Join  (cost=15.05..41.09 rows=11 width=44)
   Hash Cond: (keys.id = members_keys.key_id)
   -&gt;  Seq Scan on keys  (cost=0.00..22.70 rows=1270 width=4)
   -&gt;  Hash  (cost=14.91..14.91 rows=11 width=4)
         -&gt;  Bitmap Heap Scan on members_keys  (cost=4.24..14.91 rows=11 width=4)
               Recheck Cond: (member_id = 1)
               -&gt;  Bitmap Index Scan on members_keys_pkey  (cost=0.00..4.24 rows=11 width=0)
                     Index Cond: (member_id = 1)


The database did rewrite your query into something like the following (except that select * returns columns from both tables):

select * from keys where id in (select key_id from members_keys where member_id = 1);


And depending on database version, options, and statistics, the database might do something entirely different. But it will not do an actual cross join if it can avoid it.