SQL Questions

1. What is the difference between an inner and an outer join?

The answer to this question comes from EssentialSQL:

"Both inner and outer joins are used to combine rows from two or more tables into a single result. This is done using a join condition...Inner joins don’t include non-matching rows, whereas outer joins do include them."

2. What is wrong with the following SQL query? Correct it so that it executes properly.

SELECT Id, YEAR(BillingDate) AS BillingYear
FROM Invoices
WHERE BillingYear
>
= 2010;

This question and answer comes from [Toptal](https://www.toptal.com/sql/interview-questions):

The expression BillingYear in the WHERE clause is invalid. Even though it is defined as an alias in the SELECT phrase, which appears before the WHERE phrase, the logical processing order of the phrases of the statement is different from the written order. Most programmers are accustomed to code statements being processed generally top-to-bottom or left-to-right, but T-SQL processes phrases in a different order.

The correct query should be:

SELECT Id, YEAR(BillingDate) AS BillingYear
FROM Invoices
WHERE YEAR(BillingDate)
>
= 2010;

3. What does UNION do? What is the difference between UNION and UNION ALL ?

The question and answer comes from [Toptal](https://www.toptal.com/sql/interview-questions):

"UNION merges the contents of two structurally compatible tables into a single combined table. The difference between UNION andUNION ALL is that UNION will omit duplicate records, whereas UNION ALL will include duplicate records.

It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where it is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons."