Queries using multiple tables and ANDs: should that be avoided?
Is this a valid approach?


FROM tissue_source_site, ampl_cols, mrna_cols


What you are doing here is to get a product of those tables, meaning that if the RDBMS is not wise it may attempt to create an intermediate structure
with size = product of the sizes of each table.

Gladly I just got a query plan execution for SQL Server and they can infer the same plan for either your where clauses, and the join operator, meaning that they are just as efficient (but I would prefer use a join anyway).


  Is there a point at which I will hit a wall, concerning the length of
  the queries (endless ANDs) or performance-wise?


You shouldn't be too concerned about the length of your queries. 
Querying, after all, is the way to get your data. So you should let your query express what you want to retrieve.

Performance wise you should get a query plan as I did for SQLServer so you can choose between a join and a where. Doing many joins can also hinder performance but most of times this isn't an issue because they are done over the primary key of the tables which most of the times are indexed (you can see indexing as a way to arrange data so it's easier to retrieve it by that index)


  I did not really see this used much the tutorials I did (in a hurry).
  What are the drawbacks of doing it like that? Is it wiser to figure
  out how JOIN works?


There are many types of joins and someday you will probably be wanting the functionality of one of them, having the wisdom of picking the right tool for the job is always nice.


  Or WITH table AS? Or something else I am totally unaware of?


Your query is still pretty simple and doesn't require any "black magic". try to keep things simple and give yourself time to understand and get used to the sql querying concept.