In: Computer Science
We can use where clause to join tables like by using sub queries, Actually here clause is used to filter the records .It is not compulsory that every time in the where clause we should use sub queries, where clause is used to apply conditions and used to extract only those records that fulfil l a specified condition.
Exist is logical operator which is used to check the result of the sub query, it will return true if the sub query returns one or more rows, otherwise it will return false ie., if the sub query doesn't return any row it will return false.
In is used along with the Where Clause ,it is used to specify multiple values in a where clause.it is used to compare the values returning from the subquery .it is used to match the values returning from the subquery to the values matching the where clause column. It is a shorthand for multiple OR conditions.
Both are easy to understand "in" has its own function and "Exists" has it's own function .Both of them are used at different problems in the sql .
"AND" and "OR" operators are used along with the where clause to filter records based on more than one condition. "AND" operator is used to specify more then one condition and it will displays the records if all the conditions used in "AND" are satisfied, Where as "OR" is also used to specify more than one condition but here if any one of the condition used in "OR" is satisfied then it will displays the records.
"ALL" and "ANY" operators are used in the WHERE or HAVING clause. ALL is a logical operator it returns true if all of the subquery values meets the condition .it is used to compare a value to all values in another value set.it is same as AND operator but ALL used to test the value set where as AND is used to test multiple condition in where clause. ANY is logical operator it returns true if any of the subquery value meets the condition .it is same as the "OR" operator but ANY is used to compare a value to any values in a list as per condition where as "OR" is use to combine multiple conditions in where clause.