1 thought on “What’s the difference between and Join and a Subquery (T-SQL)?”
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.
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.