SQL- Difference based interview Question

What is the difference between inner and outer join?

• The results of an outer join will always equal the results of the corresponding inner join between the two tables plus some unmatched rows from either the left table, the right table, or both—depending on whether it is a left, right, or full outer join, respectively.
• Thus the difference between a left outer join and a right outer join is simply the difference between whether the left table’s rows are all returned, with or without matching rows from the right table, or whether the right table’s rows are all returned, with or without matching rows from the left table.
• A full outer join, meanwhile, will always include the results from both left and right outer joins.

What is the difference between JOIN and UNION?

What is the difference between UNION and UNION ALL?

Sometimes it’s important to retain all rows produced by a union operation, and not have the duplicate rows removed. This can be accomplished by using the keywords UNION ALL instead of UNION.

• UNION removes duplicate rows. Only one row from each set of duplicate rows is included in the result set.
• UNION ALL retains all rows produced by the sub selects of the union, maintaining duplicate rows.

UNION ALL is significantly faster because the need to search for duplicate rows—in order to remove them—is redundant.

What is the difference between WHERE clause and HAVING clause?
What is the difference among UNION, MINUS and INTERSECT?
What is the difference among ROW_Number(), RANK and DENSE_RANK?