There are actually a couple more options, but the one that you were likely looking for is called a correlated subquery. The subquery can reference values from the outer query, so there is no need for a join. (NOTE: This query will produce an error if there are multiple rows in TableThree that meet the criteria.)