CODEWITHSUNDEEP
sqljoinimplicit
Will Nasby
Will Nasby

Reputation: 1148

SQL Implicit Join joining 3 tables

I understand implicit JOINS are outdated but it's for a homework assignment. All his examples for implicit joins only join 2 tables, and I can't find an example anywhere that joins 3.

SELECT Name 
FROM Employee 
JOIN EmployeeSkills 
ON (EmployeeID=E.ID) 
JOIN Skill ON (SkillID=S.ID)
WHERE Title=’DBA’;

Here is the explicit version of what I want. How would I write this implicitly?

Thanks

Upvotes: 1

Views: 3199

Answers (2)

Hart CO
Hart CO

Reputation: 34774

It's pretty much the same as the two table examples:

SELECT a.Name 
FROM Employee a,EmployeeSkills b ,Skill c
WHERE a.EmployeeID = b.ID 
   AND b.SkillID = c.ID
   AND Title=’DBA’;

Edit: Good point made by Everett Warren to alias, best practice is not using implicit joins as they were deprecated long ago.

Upvotes: 1

Everett Warren
Everett Warren

Reputation: 61

Here's how I'd write it:

SELECT E.Name FROM Employee E,EmployeeSkills ES,Skill S
WHERE E.ID = ES.EmployeeID
  AND ES.SkillID = S.ID
  AND S.Title=’DBA’;

Pretty much the same answer as the first you received, but, for clarity, once I'd start using aliases I'd use them throughout, and make sure you define them. In the above example, both the employee and the skill could have a Name, and the Title could be an employee's title or the name of a skill. Using the table name (or alias) makes it easy to see what's going on even if you're not familiar with the schema.

Also, might have been the database I was using years ago, but it had a performance hit (very slight, but big difference on big data) if you didn't write the joins in the order you introduced the tables.

Upvotes: 2

Related Questions