View Full Version : JOIN versus AND

06-20-2011, 04:33 PM
When I was learning mysql, my professor taught us to use ANDs to join two columns, though there were times that we needed to use RIGHT JOINS and LEFT JOINS for more complex queries.

Sample query for first way of doing things with commas and AND

SELECT volunteers.fname,volunteers.minit,volunteers.lname,volunteers.address,students.fname,students.minit, students.lname
FROM volunteers,students,relationships
WHERE (relationships.volunteer_id=volunteers.volunteer_id AND students.student_id=relationships.student_id) AND relationships.relationship='grandparent';

Sample query for second way with JOIN and ON

FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;

However, while poking online, it seems like JOIN was the thing to do. I've had experience with CS professors being behind on the times and I want to make sure I get things right.

06-20-2011, 09:48 PM
No difference, just another syntax.

SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation".


Old Pedant
06-21-2011, 01:06 AM
I call joins using only WHERE clauses "implicit joins".

For all practical purposes, including performance (speed), there is no difference between an implicit join and an INNER join. Use whichever one gives you a warm fuzzy feeling.

But there is no way to do an OUTER join of any kind using an implicit join. (There used to be, 15 years or so ago, when using MS SQL Server and Oracle, but those methods were deprecated by ANSI and are now obsolete.)

Personally, I think that a teacher in a decent school should *NOT* teach implicit joins. Not because there is anything evil or wrong with them (I use them more often than not), but just because they don't prepare you for the very different syntax and semantics of outer joins.

And as long as we are discussing outer joins, look at an old post of mine that may help you avoid problems with outer joins: