Introduction

Last week, we were going over query writing in the SQL standard.
We described the basic SELECT FROM WHERE clause in SQL, how to rename output columns, how to disambiguate same named attributes in different tables, and how to use tuple variables to do queries involving multiple copies of the same table.
We gave three different semantics for evaluating SELECT FROM WHERE queries: As a Nested For Loop, via parallel assignment, and via conversion to relational algebra.
We then looked at the keywords: UNION, INTERSECT, and EXCEPT for set operations in SQL and began considering more general queries involving subqueries.
We started looking at three kinds of queries with subqueries: those that compute a single value used in a where clause in comparisons such as =, <, >, etc., those that return relations that can be used in various ways in WHERE clauses with EXISTS, ANY, ALL, etc. clauses, and those that are used in use in FROM-clauses followed by a tuple variable so that one can refer to the tuples it produces.
The last of these three possibilities we haven't gone over yet, so we begin today by considering it.

Subqueries in FROM Clauses

Suppose we want to find the names of producers of movies of Harrison Ford.

One way to do this in SQL is the following:

SELECT name 
FROM MovieExec, (SELECT producerC# 
    FROM Movies, StarsIn 
    WHERE title = movieTitle AND
        year = movieYear AND
        starName = 'Harrison Ford' 
    ) Prod
WHERE cert# = Prod.producerC#;

Notice we are using a subquery to essential create a tuple variable Prod to enumerate the certificate numbers of producers of movies of Harrison Ford.
Then we use the result together with the MovieExec table to find the names of the producers.

Joins in SQL

So far we have been using a comma separated list of tables in the FROM clause to do a cartesian product, and then the WHERE clause to specify a relational algebra selection of tuples, hence allowing as to do `theta`-joins.
SQL actually has various methods for explicitly writing joins, and sometimes these are viewed as preferable to a comma separated list in the FROM clause.
SQL joins can appear on the FROM line. For example,
```
SELECT *
FROM Movies CROSS JOIN StarsIn;
```
Here CROSS JOIN computes a Cartesian product, so the above is actually equivalent to:
```
SELECT *
FROM Movies, StarsIn;
```
In the above we could have parenthesize the joined tables and used a tuple variable:
```
(Movies CROSS JOIN StarsIn) MSI
```

To do a `theta`-join, we use the JOIN..ON syntax (alternatively, INNER JOIN..ON syntax). For example,

SELECT title, year, length, genre, 
    studioName, producerC#, starName 
FROM (Movies JOIN StarsIn ON 
    title = movieTitle AND year = movieYear);

Natural Joins

Recall a natural join is like a theta join where:
1. The join condition is that all pairs of attributes from the two relations having a common name are equated, and there are no other conditions
2. One of each pair of equated attributes is projected out.
In SQL, we use the keyword NATURAL JOIN to indicate we want the natural join of two tables.

For example, if we had the two tables:

MovieStar(name, address, gender, birthdate)
MovieExec(name, address, cert#, netWorth)

we could output their natural join as:

SELECT *
FROM (MovieStar NATURAL JOIN  MovieExec);

The output would have columns: name, address, gender, birthdate, cert#, netWorth and would only have rows on which the name and address of the MovieStar and MovieExec were equal.

Outer Joins

Recall we used outer joins in the relational algebra to preserve information about tuples from one of the relations in the join that doesn't join with anything in the other.
To do this, if a tuple doesn't join with the other relation we output a tuple in the outer join where we pad this tuple with the columns of the other table have been set to null.
The syntax for the outer join of two tables R and S in SQL is:
```
R NATURAL FULL OUTER JOIN S
```
We can also specify which side of the join that we want to preserve tuples for using syntax like:
```
R NATURAL LEFT OUTER JOIN S
or
R NATURAL RIGHT OUTER JOIN S
```
If we want outer `theta`-joins we use FULL OUTER JOIN ... ON, LEFT OUTER JOIN ... ON, or RIGHT OUTER JOIN ... ON. For example,
```
Movies LEFT OUTER JOIN StarsIn ON 
   title = movieTitle AND year = movieYear;
```
In Mysql, the syntax is LEFT JOIN (resp. RIGHT JOIN) for LEFT OUTER JOIN (resp. RIGHT OUTER JOIN) and rather than say FULL OUTER JOIN, one uses OUTER JOIN.

In-Class Exercise

Give an example SELECT FROM WHERE query without the JOIN ON syntax but with multiple tables in the FROM clause.
Express your query again with the JOIN ON syntax. Why would the JOIN on syntax be better. If it is not better for your particular query, come up with a query for which it is better.
Post your solutions to the Nov 13 In-Class Exercise Thread.

Full-Relation Operations

We now look at some operations on queries that act on relations as a whole rather than tuples individually.
First, we look at how to force the results of a query to be a set rather than a bag.
Then we discuss how to do grouping and aggregation with SQL using a GROUP BY clause.
Finally, we discuss how one can select what groups calculations are done for using a HAVING clause.

Eliminating Duplicates

Consider the query:
```
SELECT FirstName FROM EMPLOYEE;
```
There are likely several employees with the first name John, and as queries are computed as bags, these would be returned as duplicates.
To prevent such duplication we can use the keyword DISTINCT. So the above becomes:
```
SELECT DISTINCT FirstName FROM EMPLOYEE;
```
To ensure duplicate elimination the DBMS often has to do a sort on the output of the query, so using DISTINCT will typically be slower.
For set operations, by default SQL does duplicate elimination, if we actually want to preserve duplicates (for example because we want to do counting) we add the keyword ALL to UNION, INTERSECT, or EXCEPT to make the operation be performed as bags. For example,
```
(SELECT title, year FROM Movies) 
UNION ALL 
(SELECT movieTitle AS title, movieYear AS year FROM StarsIn);
```
In the above, a give title and year might now appear several times.

Aggregation in SQL

SQL has several aggregation operators, for example, SUM, AVG, MIN, MAX, and COUNT.
These can be applied to scalar values if we want normal evaluation or values of the form DISTINCT `x` where `x` is scalar, if we want duplicate elimination.
As an example, if we want the average net worth of MovieExec's we can do:
```
SELECT AVG(netWorth)
FROM MovieExec;
```
If we wanted the number of distinct first names of employees, we can do:
```
SELECT COUNT(DISTINCT FirstName)
FROM EMPLOYEE;
```
If we just wanted the total number of employees, and all employees have first names, then either of the following would work:
```
SELECT COUNT(FirstName)
FROM EMPLOYEE;
or
SELECT COUNT(*)
FROM EMPLOYEE;
```
In the above * acts as a placeholder for an arbitrary tuple.

GROUP BY Clauses

Sometimes we want our aggregation operations to be computed over subgroups of tuples from an entire relations.
To do this we can use a GROUP BY clause.
For example, suppose we want to compute the number of minutes of film produced by each movie studio and then output name of studio followed by this sum. We could do this with the following SQL query:
```
SELECT studioName, SUM(length)
FROM Movies
GROUP BY studioName;
```
To compute the above, tuples are sorted into groups by studioName, then for each group the sum is computed and the studioName, sum is output.
GROUP BY makes sense even if there is no aggregation function. For example,
```
SELECT studioName
FROM Movies
GROUP BY studioName
```
would sort tuples from movies into distinct groups by movie name and then output the sudioName for each group.

The above query is equivalent to:

SELECT DISTINCT studioName
FROM Movies;

One can also have multiple attributes in the GROUP BY clause by comma separating them.
GROUP BY can also be combined with a WHERE clause. For example,
```
SELECT name, SUM(length) 
FROM MovieExec, Movies
WHERE producerC# = cert#
GROUP BY name;
```
computes the length of movies for each producer.

Grouping, Aggregation, and Nulls

When tuples have nulls, one has to be a little careful with group and aggregation. Namely:
- NULLs are ignored for aggregations, so won't contribute to a sum, average, or count. So while COUNT(*) can be used to count all tuples in a relation, COUNT(A) might miss tuples whose A column is NULL.
- NULL acts as an ordinary value when forming groups. So one or more of the grouping attributes is allowed to be NULL.
- An aggregation of any type except COUNT over an empty bag has value NULL. The count of an empty bag is 0.

Having Clauses

Suppose we want to do an aggregation over groups but only want to return results for groups that meet some criteria.
To do this in SQL, we can make use of a HAVING clause.
For example, suppose we want to return the names of producers and the total length of their works, but only for producers who made at least one film before 1930. We could do this with the query:
```
SELECT name, SUM(length) 
FROM MovieExec, Movies 
WHERE producerC# = cert#
GROUP BY name 
HAVING MIN(year) < 1930;
```
HAVING clauses only apply to the tuples of the group being tested.
The only attributes in HAVING clause that are allowed to appear unaggregated in a HAVING clause are the GROUP BY attributes. For example, in the above, we applied a MIN to year which is not in the GROUP BY clause.

SQL Finish Subqueries, Distinct, Grouping, and Aggregation

Outline