1 thought on “What’s the difference between and Join and a Subquery (T-SQL)?”

  1. Its usually different ways of thinking or solving the same problem.

    A join will join two or more tables together by a field related to both tables (ie, relationship of primary and foreign keys). It is typically easy to understand.

    A subquery statement involves a SELECT statement that selects particular values from a table. The values that the select query selects is dependant upon the subquery. The subquery itself is another SELECT statement.

    IE)
    SELECT au_id, au_lname, au_fname
    FROM authors
    WHERE au_id NOT IN (
    SELECT authors.au_id
    FROM authors, titleauthor
    WHERE
    authors.au_id = titleauthor.au_id
    )

    The

    SELECT authors.au_id
    FROM authors, titleauthor
    WHERE
    authors.au_id = titleauthor.au_id

    here acts as the subquery.

    Notice that it is almost as if the query and its subquery are “joined” by au_id. The query will only return values for which au_id does NOT exist in the subquery.

Leave a Comment