This can be a question which has hassled more for any couple of days now and that i don't have around to asking it to everyone.
When I am choosing data from multiple tables I did previously use JOINS a great deal and lately I began to make use of one other way but I am unclear about the impact over time.
Choose * FROM table_1 LEFT JOIN table_2 ON (table_1.column = table_2.column)
Making this your fundamental LEFT JOIN across tables but have a look in the query below.
Choose * FROM table_1,table_2 WHERE table_1.column = table_2.column
Personally basically was joining across allows say 7 tables of information I would rather do that over JOINS.
But what are the benefits and drawbacks regarding the 2 techniques ?
Second technique is a shortcut for INNER JOIN.
Choose * FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column
Is only going to choose records that match the problem both in tables (LEFT JOIN will choose all records from table around the left, and matching records from table around the right)
[...] we consider each comma in a listing of table_reference products as equal to an inner join
INNER JOIN and , (comma) are semantically equivalent even without the a join condition: both create a Cartesian product between your specified tables (that's, every single row within the first table is became a member of to every single row within the second table).
However, the priority from the comma operator is under of INNER JOIN, Mix JOIN, LEFT JOIN, and so forth. Should you mix comma joins using the other join types when there's a join condition, a mistake from the form Unknown column 'col_name' in 'on clause' may occur. Details about coping with this issue is offered later within this section.
Generally you will find a number of things pointed out there, which should cause you to consider not using commas.
The very first technique is the ANSI/ISO version from the Join. The 2nd technique is the older format (pre-89) to create the same as an Inner Join. It will this by mix joining all of the tables you list after which thinning the Cartesian product within the Where clause to create the same as an inner join.
I'd highly recommend from the second method.
- It's tougher for other designers to see
- It breaks the rule of least astonishment with other designers who'll question whether you just would never know much better or maybe there is some specific reason behind not while using ANSI/ISO format.
- It'll result in grief when you begin attempting to use that format with something apart from Inner Joins.
- It causes it to be harder to discern your intent particularly in a sizable query with lots of tables. Are many of these tables said to be inner joins? Have you miss something within the Where clause and make up a mix join? Did you want to create a mix join? Etc.
There's virtually no reason to make use of the 2nd format and actually many database systems are ending support for your format.