Hi,
Basically "IN" clause helps us to determine if a given value matches any value in a subquery or a list.For example
a. In a list of values
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
b. in subquery returning a list of values
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM titleauthor )
Whereas "JOIN" clause/Condition helps to get the required resultset from two different tables with the help of a matching criteria. For example
The same above query in point b, can be re-written using JOIN, like
Finding all the authors who are associated with a title.
SELECT au_lname, au_fname
FROM authors a
INNER JOIN titleauthor t
ON a.au_id=t.au_id
When querying/using bigger tables it is always suggestible to use JOIN than IN with Subquery for better performance. And also JOIN has other benefits;Joins can be specified in either the FROM or WHERE clauses. The join conditions combine with the WHERE and HAVING search conditions to control the rows that are selected from the base tables referenced in the FROM clause
Hope this will be of some help.
Regards,
Siva
| 
