The inner join operator of SQL-99

Introductory comment....

Comment:

The (inner)(inner) join operator that you will learn in this webpage will not add any additional expressive power to the SQL language
This join operator will only allow you to formulate a query in a different (nicer) manner
A query may look "nicer" with these new operators (I call it sexier...)

Syntactic sugar:

When an operator 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.

The inner join operator: JOIN

The join operator:

Syntax:

R1 [INNER] 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'
This query is the same as: select fname, lname from employee, department where dname='Research' and dno=dnumber

Comment:

Now you can see the syntactic sugar:

Multiple cascaded 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

Another way to denote the Inner Join operation

An inner join is the same operation as the join operation above....

Syntax of the INNER JOIN operator:

r1 INNER JOIN r2 ON join-condition
Meaning: it does the same thing as r1 JOIN r2 ON join-condition

The effect is the same as JOIN operator (see above).

Example:

Solution:

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