SOQL does not allow us to compare two fields (more specifically, a field cannot appear in the Right-Hand Side of a filter expression).
In some cases (generally when you're trying to compare a field value on a child record against a field value on a related parent record), we can use a formula field to do the field comparison and then use that formula field in the WHERE clause.
In this situation though, you'll want to use the Aggegate-Query-Update pattern (well, the Aggregate and Query parts of it at least)
// The first step is to aggregate the data you want to use in your query
// This will typically be a loop over a trigger context variable, or the result
// of another query
Set<String> aggregatedValues = new Set<String>();
for(Contact cont :[SELECT <fields> FROM Contact WHERE <filters>]){
aggregatedValues.add(cont.My_Field__c);
}
// The second step is to use the information we gathered to feed the query
// we really want to run
// This is the part you're interested in
List<Account> accounts = [SELECT <fields> FROM Account WHERE Some_Field__c IN :aggregatedValues];
That Some_Field__c IN :aggregatedValues
part contains a variable bind (specifically the :aggregatedValues
part). SOQL allows us to use Apex variables in filter expressions. It's a useful tool that helps us work around some of the limitations of SOQL. Beyond this, you'd also use variable binds to do things like being able to include user input in a query.