Further SQL Syntax

First: a comment

Comment:

Most features that you will learn in this webpage will not add any additional expressive power to SQL
(The outer joins are the only truly new operations that you will learn here)
Most of these new features will only allow you to formulate a query in a different manner
Sometimes, the query will look "nicer" with the new syntax (I call it sexier)

Syntactic sugar:

Because these additional syntax does not add any substance to the SQL language, they are what we call:
It "tastes good" (i.e., looks sexier)

But it's only fat - i.e., no nutritional value...

Wikipedia: In computer science, syntactic sugar is syntax within a programming language that is designed to make things easier to read or to express. It makes the language "sweeter" for humans to use: things can be expressed more clearly, more concisely, or in an alternative style that some may prefer.

Temporal Relations

A temporal relation is a result of a SELECT clause that exists temporally, usually to assist you in formulating a query

Temporal relations are created using one the following methods:

Using a bracketed SELECT command inside the FROM clause:

SELECT .... FROM .... , (SELECT ... ) ALIAS , .... ....

Using the WITH clause

WITH ALIAS as (SELECT .....) /* With clause */ SELECT ... FROM ... ...

NOTE: the WITH clause is not implemented in MySQ (version 5).

Temporal relations in FROM clause

The FROM clause contain a list of relations
Fact:

Therefore:

The result of a SELECT command (a (temporal) relation) can be used as a relation in the FROM clause !!!
Requirement:

Example:

Find fname, lname of male employees with salary > 50000

Solution:

We can first find all the male employees: select fname, lname, salary from employee where sex='M'
And then use this result to find those (male) employees earning > 40000: select * from (select fname, lname, salary from employee where sex='M') R1 where R1.salary > 40000

Notice that we must use an alias R1 to give the result relation produced by the SELECT command a name (here: R1)

Another example:

Find fname, lname of employees who work on projects controlled by the 'Research' department

Solution:

select fname, lname from employee, works_on, project, (select dnumber from department where dname='Research') X where project.dnum = X.dnumber and works_on.pno = project.pnumber and employee.ssn = works_on.essn

Important note

Important restriction:

You can specify multiple temporal relations inside the FROM clause

Example:

SELECT .... FROM .... , (SELECT .... ) R1 , (SELECT .... ) R2 , ... WHERE .... ...

You cannot use a temporal relation inside a sub query

In other words, this construct is not allowed:

SELECT .... FROM .... , (SELECT .... ) R1 , (SELECT .... FROM R1 ... ) R2 , ... WHERE .... ...

Creating a temporal relation using the WITH clause: refining a query

The WITH clause was introduced in the SQL-99 standard to help SQL users to refine the result of a query.

Syntax of the WITH clause:

WITH ALIAS as ( SELECT query ) SELECT query

Effect:

First, the SELECT query in the WITH clause is executed (and returns a temproal relation which will be named by the given ALIAS)
Then, the temporal relation is used in the second SELECT query

Example:

Find fname, lname of male employee who earn more than 30000

Solution:

with R1 as (select * from employee where sex='M') select fname, lname from R1 where salary > 30000

Processing:

First, the subquery in the WITH clause is computed (and stored)
Then the SELECT command is applied on the result of the subquery

NOTE:

Stepwise refinement

Fact:

You can have multiple SELECT queries in the WITH clause
The subqueries inside a WITH clause are executed in the order specified
A subsequent query in the WITH clause can use the result of a previous query in the same WITH clause

Consequently:

Example:

Find fname, lname of male employee in the 'Research' department who earn more than 30000

Solution:

with R1 as (select * from employee, department where dno=dnumber and dname='Research') , /* Research employees */ R2 as (select * from R1 where sex='M') /* ... that are male */ select fname, lname from R2 where salary > 30000 /* ... who earn > 30000 */

R1 is the set of employees in the 'Research' department
R2 is the set of male employees in R1
Finally, the tuple with salary > 30000 from R2 are selected

Query:

Solution using WITH clause

with R1 as (SELECT ssn FROM employee WHERE fname='John' AND lname='Smith') select * from dependent where essn IN (select ssn from R1)

The Join operators
- The SQL-99 standard has added a number of join operation :

The JOIN operator

The join operator:

Syntax:

R1 JOIN R2 ON join-condition

Meaning:

(With the JOIN operator, the SQL query will look a lot like a Relational Algebra query.)

Example:

Find fname, lname of employees in the 'Research' department

Relational Algebra query:

π_{fname, lname} ( σ_{dname='Research'} ( Employee ⋈_dno=dnumber Department ) )

SQL query using join:

select fname, lname from employee join department on dno=dnumber where dname='Research'

Note:

Now you can really witness the syntactic sugar:

You could have formulated the above query as followed (without the inner join operator):

select fname, lname from (select * from employee, department where dno=dnumber) where dname='Research' Or even: select fname, lname from employee, department where dno=dnumber and dname='Research'

Deeper Joins

Example: Joining 2 relations

select ssn, fname, lname, essn, pno from employee join works_on on ssn=essn 123456789 John Smith 123456789 1 123456789 John Smith 123456789 2 333445555 Frank Wong 333445555 2 333445555 Frank Wong 333445555 3 333445555 Frank Wong 333445555 10 333445555 Frank Wong 333445555 20 999887777 Alice Miller 999887777 30 999887777 Alice Miller 999887777 10 987654321 Jack Wallace 987654321 30 987654321 Jack Wallace 987654321 20 666884444 John Doe 666884444 3 453453453 Joyce English 453453453 1 453453453 Joyce English 453453453 2 987987987 Jake Jones 987987987 30 987987987 Jake Jones 987987987 10 888665555 James Borg 888665555 20

Example: Joining 3 relations

select ssn, fname, lname, essn, pno, pnumber, pname from (employee join works_on on ssn=essn) join project on pno=pnumber SSN FNAME LNAME ESSN PNO PNUMBER PNAME --------- ---------- ---------- --------- ---------- ---------- --------------- 123456789 John Smith 123456789 2 2 ProductY 123456789 John Smith 123456789 1 1 ProductX 333445555 Frank Wong 333445555 20 20 Reorganization 333445555 Frank Wong 333445555 10 10 Computerization 333445555 Frank Wong 333445555 3 3 ProductZ 333445555 Frank Wong 333445555 2 2 ProductY 999887777 Alice Miller 999887777 10 10 Computerization 999887777 Alice Miller 999887777 30 30 Newbenefits 987654321 Jack Wallace 987654321 20 20 Reorganization 987654321 Jack Wallace 987654321 30 30 Newbenefits 666884444 John Doe 666884444 3 3 ProductZ 453453453 Joyce English 453453453 2 2 ProductY 453453453 Joyce English 453453453 1 1 ProductX 987987987 Jake Jones 987987987 10 10 Computerization 987987987 Jake Jones 987987987 30 30 Newbenefits 888665555 James Borg 888665555 20 20 Reorganization

Inner Join
- An inner join is the same as the ("ordinary") join operation....
  I.e., it's the same as the join operation that we are accustomed to
- Syntax of the INNER JOIN operator:
- The result is the SAME as JOIN operator.
- Example:
- Solution:

Outer Joins

An outer join is an join that perserves all tuples from some input relation
Recall that the join operation will omit tuples that do not satisfy the join condition

Example:

select ssn, fname, lname from employee; SSN FNAME LNAME --------- ---------- ---------- 123456789 John Smith 333445555 Frank Wong 987654321 Jack Wallace 999887777 Alice Miller 666884444 John Doe 453453453 Joyce English 987987987 Jake Jones 888665555 James Borg
select * from dependent; ESSN NAME S BDATE RELATIONSH --------- ---------- - ---------- ---------- 333445555 Alice F 05-APR-76 DAUGHTER 333445555 Theodore M 25-OCT-73 SON 333445555 Joy F 03-MAY-48 SPOUSE 987654321 Abner M 29-FEB-32 SPOUSE 123456789 Micheal M 01-JAN-78 SON 123456789 Alice F 31-DEC-78 DAUGHTER 123456789 Elizabeth F 05-MAY-57 SPOUSE
select ssn, fname, lname, name from employee join dependent on ssn=essn SSN FNAME LNAME NAME --------- ---------- ---------- ---------- 123456789 John Smith Elizabeth 123456789 John Smith Alice 123456789 John Smith Micheal 333445555 Frank Wong Joy 333445555 Frank Wong Theodore 333445555 Frank Wong Alice 987654321 Jack Wallace Abner

Notice that the non-matching tuples are NOT select

The outer join operation will select non-matching tuples ALSO
The non-matching tuples will have NULL values in other attributes

There are THREE different flavors of outer joins :

Left Outer Join: preserves ALL tuples of the left operand (relation)
Right Outer Join preserves ALL tuples of the right operand (relation)
Full Outer Join preserves ALL tuples of the both operands (relations)

Left Outer Join

Syntax:

r1 LEFT [OUTER] JOIN r2 ON join-condition

Example:

Employee: SSN FNAME LNAME --------- ---------- ---------- 123456789 John Smith 333445555 Frank Wong 987654321 Jack Wallace 999887777 Alice Miller 666884444 John Doe 453453453 Joyce English 987987987 Jake Jones 888665555 James Borg
Dependent: ESSN NAME S BDATE RELATIONSH --------- ---------- - ---------- ---------- 333445555 Alice F 05-APR-76 DAUGHTER 333445555 Theodore M 25-OCT-73 SON 333445555 Joy F 03-MAY-48 SPOUSE 987654321 Abner M 29-FEB-32 SPOUSE 123456789 Micheal M 01-JAN-78 SON 123456789 Alice F 31-DEC-78 DAUGHTER 123456789 Elizabeth F 05-MAY-57 SPOUSE
select ssn, fname, lname, name from employee left outer join dependent on ssn=essn SSN FNAME LNAME NAME --------- ---------- ---------- ---------- 333445555 Frank Wong Alice 333445555 Frank Wong Theodore 333445555 Frank Wong Joy 987654321 Jack Wallace Abner 123456789 John Smith Micheal 123456789 John Smith Alice 123456789 John Smith Elizabeth 999887777 Alice Miller (NULL) 666884444 John Doe (NULL) 987987987 Jake Jones (NULL) 888665555 James Borg (NULL) 453453453 Joyce English (NULL)

Full Outer Join

Syntax:

r1 FULL [OUTER] JOIN r2 ON join-condition

Right Outer Join

Syntax:

r1 RIGHT [OUTER] JOIN r2 ON join-condition

A note on Outer Join
- NULL values is a royal pain in the butt to handle in the formulation of a query
- Because outer joins can return NULL values in some attributes, the query techniques that you have learned may failed to obtain the correct tuples (it may return extraneous tuples )
- So: avoid using outer joins

An alternative technique to compute the difference of 2 sets

Query:

Solution using an outer join:

First, consider the employees in the Research department:

select ssn, fname, lname from employee, department where dno=dnumber and dname='Research'
ssn fname lname --------- ------ -------- 123456789 John Smith 333445555 Frankl Wong 666884444 Ramesh Narayan 453453453 Joyce English

Next, consider the employees with dependent(s):

select essn, name from dependent
essn name --------- ---------- 333445555 Alice 333445555 Theodore 333445555 Joy 987654321 Abner 123456789 Micheal 123456789 Alice 123456789 Elizabeth

We can now see that the difference of the 2 sets:

666884444 Ramesh Narayan 453453453 Joyce English

do not have any dependents.

Fact:

Let's do an outer join on these 2 sets:

select * from (select ssn, fname, lname from employee, department where dno=dnumber and dname='Research') A LEFT JOIN (select essn, name from dependent) B ON A.ssn = B.essn
ssn fname lname essn name --------- ------ -------- --------- ---------- 123456789 John Smith 123456789 Micheal 123456789 John Smith 123456789 Alice 123456789 John Smith 123456789 Elizabeth 333445555 Frankl Wong 333445555 Alice 333445555 Frankl Wong 333445555 Theodore 333445555 Frankl Wong 333445555 Joy 666884444 Ramesh Narayan NULL NULL 453453453 Joyce English NULL NULL

The unmatched tuples contain NULL values !!!

Therefore, we can find the difference by testing on NULL:

select * from (select ssn, fname, lname from employee, department where dno=dnumber and dname='Research') A LEFT JOIN (select essn, name from dependent) B ON A.ssn = B.essn where B.essn IS NULL
ssn fname lname essn name --------- ------ -------- --------- ---------- 666884444 Ramesh Narayan NULL NULL 453453453 Joyce English NULL NULL

Postscript:

I don't like the logic of the above solution
(I.e., there is no clear line of thought in the development of the solution)

I prefer:

SELECT ssn FROM employee WHERE ssn IN {ssn of employees in 'Research' department} AND ssn NOT IN {ssn of emp's with dependent} (I can clearly see a difference being computed)
SELECT ssn, fname, lname FROM employee WHERE ssn IN (SELECT ssn /* emp's in 'Research' */ FROM employee, department WHERE dno = dnumber AND dname = 'Research') AND ssn NOT IN (SELECT essn /* emp's with dependent */ FROM dependent)

Natural Join

A natural join is an equi-join on attributes with the same name
The common attrubute will appear ONCE in the result
(You can't have 2 attributes (columns) withe the same name !!!)

Example:

select * from works_on; ESSN PNO HOURS --------- ---------- ---------- 123456789 1 32.5 123456789 2 7.5 333445555 2 10 333445555 3 10 333445555 10 10 333445555 20 10 999887777 30 30 999887777 10 10 987654321 30 20 987654321 20 15 666884444 3 40 453453453 1 20 453453453 2 20 987987987 30 5 987987987 10 35 888665555 20 1
select * from dependent; ESSN NAME S BDATE RELATIONSH --------- ---------- - ---------- ---------- 333445555 Alice F 05-APR-76 DAUGHTER 333445555 Theodore M 25-OCT-73 SON 333445555 Joy F 03-MAY-48 SPOUSE 987654321 Abner M 29-FEB-32 SPOUSE 123456789 Micheal M 01-JAN-78 SON 123456789 Alice F 31-DEC-78 DAUGHTER 123456789 Elizabeth F 05-MAY-57 SPOUSE
select * from works_on natural join dependent ESSN PNO HOURS NAME S BDATE RELATIONSH --------- ---------- ---------- ---------- - ---------- ---------- 123456789 1 32.5 Elizabeth F 05-MAY-57 SPOUSE 123456789 1 32.5 Alice F 31-DEC-78 DAUGHTER 123456789 1 32.5 Micheal M 01-JAN-78 SON 123456789 2 7.5 Elizabeth F 05-MAY-57 SPOUSE 123456789 2 7.5 Alice F 31-DEC-78 DAUGHTER 123456789 2 7.5 Micheal M 01-JAN-78 SON 333445555 2 10 Joy F 03-MAY-48 SPOUSE 333445555 2 10 Theodore M 25-OCT-73 SON 333445555 2 10 Alice F 05-APR-76 DAUGHTER 333445555 3 10 Joy F 03-MAY-48 SPOUSE 333445555 3 10 Theodore M 25-OCT-73 SON 333445555 3 10 Alice F 05-APR-76 DAUGHTER 333445555 10 10 Joy F 03-MAY-48 SPOUSE 333445555 10 10 Theodore M 25-OCT-73 SON 333445555 10 10 Alice F 05-APR-76 DAUGHTER 333445555 20 10 Joy F 03-MAY-48 SPOUSE 333445555 20 10 Theodore M 25-OCT-73 SON 333445555 20 10 Alice F 05-APR-76 DAUGHTER 987654321 30 20 Abner M 29-FEB-32 SPOUSE 987654321 20 15 Abner M 29-FEB-32 SPOUSE

Notice that the common attribute name ESSN appears only once in the result.

Cross Join

A cross join is the same as a Cartesian Product

r1 CROSS JOIN r2 ON join-condition

Example:

select ssn, fname, lname, dno, dnumber, dname from employee cross join department SSN FNAME LNAME DNO DNUMBER DNAME --------- ---------- ---------- ---------- ---------- --------------- 123456789 John Smith 5 5 Research 333445555 Frank Wong 5 5 Research 999887777 Alice Miller 4 5 Research 987654321 Jack Wallace 4 5 Research 666884444 John Doe 5 5 Research 453453453 Joyce English 5 5 Research 987987987 Jake Jones 4 5 Research 888665555 James Borg 1 5 Research 123456789 John Smith 5 4 Administration 333445555 Frank Wong 5 4 Administration 999887777 Alice Miller 4 4 Administration 987654321 Jack Wallace 4 4 Administration 666884444 John Doe 5 4 Administration 453453453 Joyce English 5 4 Administration 987987987 Jake Jones 4 4 Administration 888665555 James Borg 1 4 Administration 123456789 John Smith 5 1 Headquarters 333445555 Frank Wong 5 1 Headquarters 999887777 Alice Miller 4 1 Headquarters 987654321 Jack Wallace 4 1 Headquarters 666884444 John Doe 5 1 Headquarters 453453453 Joyce English 5 1 Headquarters 987987987 Jake Jones 4 1 Headquarters 888665555 James Borg 1 1 Headquarters