Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: JOIN versus AND
06-20-2011, 04:33 PM #1
- Join Date
- Apr 2011
- Thanked 0 Times in 0 Posts
JOIN versus AND
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';
SELECT * 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.
Last edited by mingqi; 06-20-2011 at 04:55 PM.
06-20-2011, 09:48 PM #2
- Join Date
- Dec 2005
- Thanked 76 Times in 76 Posts
No difference, just another syntax.
http://en.wikipedia.org/wiki/Join_%28SQL%29SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation".
06-21-2011, 01:06 AM #3
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: