When to use an identifier in SQL queries

I have just been looking at some example database queries, and came across this :

Find the identifier, name & address of employees of the Research department.

Method I: Join Method

SELECT Ss开发者_JS百科n, FName, LName, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dno = Dnumber
AND Dname = ‘Research’

Method II: Subquery Method

SELECT Ssn, FName, LName, Address
FROM EMPLOYEE
WHERE Dno IN
( SELECT Dnumber
FROM DEPARTMENT
WHERE Dname = ‘Research’ );

In these examples, why can you not leave out the Dno = Dnumber line? How do you know when to include this?

John, you are using implicit join syntax cf ansi SQL '89.

WHERE JOIN

SELECT Ssn, FName, LName, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dno = Dnumber
AND Dname = 'Research'

You should never ever use that because it is confusing as hell.
And it causes a lot of errors because it does a cross join if you're not careful.
The following syntax using explicit joins cf ANSI SQL '92 which is much clearer.

SELECT Ssn, FName, LName, Address
FROM EMPLOYEE
inner join DEPARTMENT on (employee.dnumber = department.dno)
WHERE Dname = 'Research'

This also answers why you cannot leave out the dnumber = dno, because that's the join condition

SUBQUERY
A subquery is really a join by other means.
In general you should avoid a subquery because a join is faster (90% of the time)

Some people find subqueries easier to understand. I would say that if you don't grok joins stay away from SQL!
Still sometimes you're doing something to complex or bizarre for a join and then the subquery is an option.

Back to your question: because the subquery is really a join by other means you need that join condition to make the join :-).

I would actually write something like this.

SELECT 
   Ssn, FName, LName, Address
FROM 
   EMPLOYEE
LEFT JOIN 
   DEPARTMENT ON EMPLOYEE.Dno = DEPARTMENT.Dnumber
WHERE 
   Dname = "Research"

The Dno = Dnumber is used to bridge the JOIN between the Employee and Department tables. You need it to identify the relationship between tables you are joining.

There are many ways to write JOIN statements.

This is a good tutorial about joins - http://mysqljoin.com/

Each employee belongs to a department. Dno = Dnumber is what defines the relationship between the two. So you have to keep that relationship in your join. Dname = 'Research' further filters to only include Research department employees.

Should you not join Dno to Dnumber, you will wind up with a Cartesian product.

The "Dno = Dnumber" line is the join clause - it tells the query only to include records in the employee table whose Dno matches the department number in the department table.

You use the Dno = Dnumber line to basically create JOIN criteria.

If you didn't use them you'd have a full JOIN However, I would contend that the correct way to do the JOIN is to actually do

SELECT 
    EMPLOYEE.[Ssn]
    , EMPLOYEE.[FName]
    , EMPLOYEE.[LName]
    , EMPLOYEE.[Address]
FROM 
    EMPLOYEE
JOIN 
    DEPARTMENT 
        ON DEPARTMENT.[Dnumber] = EMPLOYEE.[Dno]    
WHERE 
    DEPARTMENT.[Dname] = 'Research'

The Dno = Dnumber clause is needed for you to join the two tables. Without this, you'll get what's called a Cartesian join, where you'll get n x m number of rows returned, where n = # rows in the EMPLOYEE table, and m = # rows in the DEPARTMENT table.

You can find a good tutorial on SQL join at http://www.1keydata.com/sql/sqljoins.html.