Cant quite get my head around SQL Inner Join.

[BolshieBastard]

Im struggling with part of the SQL statement for an inner join.

Suppose we have 2 tables, Employee and Training Taken.

The Employee table has the following columns; EmployeeID, Name, Telephone, StartedEmployment. There are 4 records in the table ie EmpID numbers 1, 2, 3 & 4.

The Training Taken table has the following columns; TrainingTakenID, EmployeeID, TrainingTitle, TrainingDate. There are also 4 records in the table for employees with the ID 1, 2 & 3.

A straightforward statement to retrieve a list of employee names along with the title and date of any training they have been on would seem to be along the lines of

SELECT Employee.Name, TrainingTaken.TrainingTitle, TrainingTaken.TrainingDate
FROM Employee
INNER JOIN TrainingTaken ON Employee.EmployeeID = TrainingTaken.EmployeeID

Now, I can understand the identification of the table and column names and just about get my head around the inner join.

But what I dont understand is where does the FROM Employee part of the statement is there or needed since the Employee table is identified by Employee.Name isnt it?

Why would FROM Employee be used instead of FROM TrainingTaken?

Why does FROM Employee appear and not FROM TrainingTaken??

[chicane]

But what I dont understand is where does the FROM Employee part of the statement is there or needed since the Employee table is identified by Employee.Name isnt it?

Why would FROM Employee be used instead of FROM TrainingTaken?

Why does FROM Employee appear and not FROM TrainingTaken??

To answer your questions in the order presented:

1) Most people writing SQL wouldn't prefix each column name with the full table name - it would make the query unnecessarily long. It's therefore necessary to specify the table name in full following the FROM clause. You might consider to reading up on table aliases to get a better understanding of how queries are written in the real world.

2) The query should work in exactly the same way if you were to select FROM TrainingTaken and INNER JOIN with Employee. It's a matter of personal preference and shouldn't affect the performance or behaviour of the query.

3) See answer to previous question.

As a sidenote, if you find the INNER JOIN syntax unintuitive, you might consider using the older SQL-89 syntax. It makes more sense to me, but I understand that it's sadly being phased out in favour of the newer syntax.

[doodab]

SELECT Employee.Name, TrainingTaken.TrainingTitle, TrainingTaken.TrainingDate
  FROM Employee, TrainingTaken 
 WHERE Employee.EmployeeID = TrainingTaken.EmployeeID

Make any more sense now?

[TheFaQQer]

Wouldn't you actually have three tables: emp, training, empTraining so that you only have to record the training title in one record?

[Spacecadet]

Im struggling with part of the SQL statement for an inner join.

Suppose we have 2 tables, Employee and Training Taken.

The Employee table has the following columns; EmployeeID, Name, Telephone, StartedEmployment. There are 4 records in the table ie EmpID numbers 1, 2, 3 & 4.

The Training Taken table has the following columns; TrainingTakenID, EmployeeID, TrainingTitle, TrainingDate. There are also 4 records in the table for employees with the ID 1, 2 & 3.

A straightforward statement to retrieve a list of employee names along with the title and date of any training they have been on would seem to be along the lines of

SELECT Employee.Name, TrainingTaken.TrainingTitle, TrainingTaken.TrainingDate
FROM Employee
INNER JOIN TrainingTaken ON Employee.EmployeeID = TrainingTaken.EmployeeID

You might want to use a left outer join instead in this sort of scenario, so that you can easily see which employee's haven't had any training

Now, I can understand the identification of the table and column names and just about get my head around the inner join.

But what I dont understand is where does the FROM Employee part of the statement is there or needed since the Employee table is identified by Employee.Name isnt it?

SQL is a declarative language, the order in which items appear in the statement isn't necessarily the order in which they are processed.
As chicane pointed out, you may want to look up aliases.

The query you have could be written as

SELECT Emp.Name, Trn.TrainingTitle, Trn.TrainingDate
FROM Employee Emp
    INNER JOIN TrainingTaken Trn ON Emp.EmployeeID = Trn.EmployeeID

This might look a bit pointless, untill you start dealing with self join (tables joined to themselves) and distributed queries (SQL Statements that get data from other databases/servers) or just very long table names

now

 
 
SELECT HRDatabase.dbo.Employee.Name, TrainingDatabase.dbo.TrainingTaken.TrainingTitle, TrainingDatabase.dbo.TrainingTaken.TrainingDate
FROM HRDatabase.dbo.Employee 
    INNER JOIN TrainingDatabase.dbo.TrainingTaken ON HRDatabase.dbo.Employee.EmployeeID = TrainingDatabase.dbo.TrainingTaken.EmployeeID

becomes:

SELECT Emp.Name, Trn.TrainingTitle, Trn.TrainingDate
FROM HRDatabase.dbo.Employee Emp
    INNER JOIN TrainingDatabase.dbo.TrainingTaken Trn ON Emp.EmployeeID = Trn.EmployeeID

Why would FROM Employee be used instead of FROM TrainingTaken?

Why does FROM Employee appear and not FROM TrainingTaken??

for a simple inner join like yours, you could have them either way round

[Spacecadet]

As a sidenote, if you find the INNER JOIN syntax unintuitive, you might consider using the older SQL-89 syntax. It makes more sense to me, but I understand that it's sadly being phased out in favour of the newer syntax.

Having grown up using the older method and having to use the horrible plus bracket things ( (+) )when defining outer joins I can definitely say that the INNER/LEFT/RIGHT/CROSS/FULL JOIN syntax is a lot more intuitive

[TheFaQQer]

Having grown up using the older method and having to use the horrible plus bracket things ( (+) )when defining outer joins I can definitely say that the INNER/LEFT/RIGHT/CROSS/FULL JOIN syntax is a lot more intuitive

I agree.

Last year, though, I worked on a project where the other guy who had been there for 3 years insisted that my code was no good because I used the ANSI syntax (as recommended by Oracle!) instead of the + notation. His reasoning was twofold. 1 - he didn't like it. 2 - it made the code longer.

But, he was a ****.

[Durbs]

As chicane pointed out, you may want to look up aliases.

And dont do what the tossrag who wrote the procs i'm currently looking at and alias everything with incrementing letters of the alphabet so in a big procedure i'm constantly looking back to remember what h.code is (its the Country table, course it is, silly me should have realised that from the 'h'!)

[Spacecadet]

And dont do what the tossrag who wrote the procs i'm currently looking at and alias everything with incrementing letters of the alphabet so in a big procedure i'm constantly looking back to remember what h.code is (its the Country table, course it is, silly me should have realised that from the 'h'!)

Even the scripts I wrote that build queries for me know better than that

[VectraMan]

I also have an INNER JOIN confusion to do with nesting.

This works:

SELECT * FROM prices INNER JOIN (products INNER JOIN basket ON products.product_id = basket.product_id) WHERE basket.id = 123 AND prices.region_id = 2 AND prices.discount_id = basket.discount_id

The prices table contains a price based on the product, region, and discount. The nested join is there to get the name of the product from the products table at the same time.

But I need a third thing which is the name of the discount associated with basket.discount_id from the discounts table, but I can't figure out how or where I get that in.

So the records I need to end up with are prices.price, products.product_name, and discounts.discount_name, based on basket.product_id, basket.discount_id, and basket.region_id.

Can anybody help?