What is JOIN

Join returns all records that match the JOIN and WHERE criteria and any GROUP BY or HAVING clauses

• If you want unique records, you have to specify the record wanted through additional conditions or SELECT DISTINCT

INNER JOIN

SELECT t1.* FROM "Cars" t1
INNER JOIN "Dealership" t2 
  ON t2._vehicleBrand = ANY(t1."carBrands")
WHERE t2."State" = 'NewYork' AND t2."zipCode" = '11101'

Inner Join fetches all records that match a condition between the two tables with a single-column as a reference overlap from each table

• There can only be one column referenced as a join condition from each table
  • e.g. t1.id and t2.userId when t1 is a users table
• If you want to further narrow-down a join result, you have to define the criteria in the WHERE clause
  • you can't reference two columns as a join condition

Subqueries vs Join

Source

Sub-queries are the logically correct way to solve problems of the form, "Get facts from A, conditional on facts from B".

• In such instances, it makes more logical sense to stick B in a sub-query than to do a join.
• It is also safer, in a practical sense, since you don't have to be cautious about getting duplicated facts from A due to multiple matches against B.

Practically speaking, however, the answer usually comes down to performance. Some optimisers suck lemons when given a join vs a sub-query, and some suck lemons the other way, and this is optimiser-specific, DBMS-version-specific and query-specific.

Historically, explicit joins usually win, hence the established wisdom that joins are better, but optimisers are getting better all the time, and so I prefer to write queries first in a logically coherent way, and then restructure if performance constraints warrant this.

From MySQL manual:

"A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone."