Join Opinion

What is the better practice when joining tables in a query example 1 or 2? I've always joined tables together like example 2, but it seems that many people are advocates of example 1. Are there any differences performance wise between the two?

Example 1:
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
ORDER BY a.au_lname DESC

Example 2:
SELECT *
FROM authors AS a, publishers AS p
WHERE a.city = p.city
ORDER BY a.au_lname DESCBoth examples are executionally identical.|||I find the first way helps against inadvertant cross joins. Also, it is a lot easier to do the outer joins in the first method.|||Both examples are executionally identical because the optimizer converts the second syntax to the first syntax, at least for simple queries such as your example. For more complex queries it is possible that the optimer will not be able to do this, and then the first syntax is preferable.

blindman|||For both queries you posted, I do not think there is a different performance between them, but INNER JOIN is a really good way to be used in a query statement. It also can be used in the WHERE clause.|||ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...|||Brett, there's no need to get all ansi about it! :D|||Brett, tell me how you really feel about it LOL|||Well,

You could probably still use...

SELECT *
FROM Orders o, [Order Details] d
WHERE o.Orderid =* d.Orderid

Then there Oracle

SELECT *
FROM Orders o, [Order Details] (+) d
WHERE o.Orderid = d.Orderid

Or Informix

SELECT *
FROM Orders o, [Order Details] d
WHERE o.Orderid = d.Orderid(+)

Ya gotta love standards...

But as far as performance goes...howzabout for nonexistance

SELECT * FROM Orders o LEFT JOIN [Order Details] d
WHERE o.OrderId = d.OrderId AND d.OrderId IS NULL

instead on NOT EXISTS or NOT IN...|||You will never regret using ANSI syntax when you go back to do maintenance.

Separating the JOIN conditions from the WHERE conditions will always make things much clearer six months down the road when you are asking yourself 'Now what the hell is this query/view doing??'

The ANSI syntax may be more verbose, but is always clearer to understand when you come back to it later.

Trust me, it grows on you. The more complex the queries you write, the more sense it makes. When you are writing a query like the example above...

SELECT *
FROM authors AS a, publishers AS p
WHERE a.city = p.city
ORDER BY a.au_lname DESC

...it doesn't make much difference, but when you write something like this piece of a view definition....

SELECT (huge select section left out for clarity)
FROM table_case AS cs
CROSS JOIN table_report_window AS rw
JOIN table_gbst_elm AS elm ON cs.casests2gbst_elm = elm.objid
JOIN table_close_case AS cc ON cc.last_close2case = cs.objid
LEFT OUTER JOIN table_ibm_machine_type AS mt ON cs.case2machine_type = mt.objid
LEFT OUTER JOIN table_atlas_dispatch AS ad ON cs.case2dispatch = ad.objid
LEFT OUTER JOIN table_user AS usr ON cs.case_owner2user = usr.objid
LEFT OUTER JOIN table_time_zone AS tmzn ON cs.case2time_zone = tmzn.objid
LEFT OUTER JOIN table_contract AS cont ON cs.entitlement2contract = cont.objid
LEFT OUTER JOIN table_act_entry AS ae200 ON ae200.act_entry2case = cs.objid
AND ae200.entry_time = (SELECT MAX(aecsq1.entry_time)
FROM table_act_entry AS aecsq1
WHERE aecsq1.act_code = 200
AND aecsq1.act_entry2case = cs.objid)
AND ae200.act_code = 200
LEFT OUTER JOIN table_act_entry AS ae92002 ON ae92002.act_entry2case = cs.objid
AND ae92002.entry_time = (SELECT MIN(aecsq2.entry_time)
FROM table_act_entry AS aecsq2
WHERE aecsq2.act_code = 92002
AND aecsq2.act_entry2case = cs.objid)
AND ae92002.act_code = 92002
LEFT OUTER JOIN table_atlas_part_order AS po1 ON po1.part_order2dispatch = cs.case2dispatch
AND po1.objid = (SELECT MIN(objid)
FROM table_atlas_part_order AS pocsq1
WHERE pocsq1.part_order2dispatch = cs.case2dispatch)
LEFT OUTER JOIN table_atlas_part_order AS po2 ON po2.part_order2dispatch = cs.case2dispatch
AND po2.objid = (SELECT MIN(objid)
FROM table_atlas_part_order AS pocsq2
WHERE pocsq2.part_order2dispatch = cs.case2dispatch
AND pocsq2.objid > (SELECT MIN(objid)
FROM table_atlas_part_order AS pocsq3
WHERE pocsq3.part_order2dispatch = cs.case2dispatch))
LEFT OUTER JOIN table_demand_hdr AS hdr on hdr.caseinfo2case = cs.objid
WHERE cc.close_date BETWEEN rw.start_date AND rw.end_date
UNION
SELECT (huge select section left out for clarity)
FROM table_demand_hdr AS hdr
CROSS JOIN table_report_window AS rw
JOIN table_case AS cs ON hdr.caseinfo2case = cs.objid
JOIN table_gbst_elm AS elm1 ON cs.calltype2gbst_elm = elm1.objid
JOIN table_gbst_elm AS elm2 ON cs.casests2gbst_elm = elm2.objid
LEFT OUTER JOIN table_ibm_machine_type AS mt ON cs.case2machine_type = mt.objid
LEFT OUTER JOIN table_atlas_dispatch AS ad ON cs.case2dispatch = ad.objid
LEFT OUTER JOIN table_user AS usr ON cs.case_owner2user = usr.objid
LEFT OUTER JOIN table_time_zone AS tmzn ON cs.case2time_zone = tmzn.objid
LEFT OUTER JOIN table_contract AS cont ON cs.entitlement2contract = cont.objid
LEFT OUTER JOIN table_act_entry AS ae200 ON ae200.act_entry2case = cs.objid
AND ae200.entry_time = (SELECT MAX(aecsq1.entry_time)
FROM table_act_entry AS aecsq1
WHERE aecsq1.act_code = 200
AND aecsq1.act_entry2case = cs.objid)
AND ae200.act_code = 200
LEFT OUTER JOIN table_act_entry AS ae92002 ON ae92002.act_entry2case = cs.objid
AND ae92002.entry_time = (SELECT MIN(aecsq2.entry_time)
FROM table_act_entry AS aecsq2
WHERE aecsq2.act_code = 92002
AND aecsq2.act_entry2case = cs.objid)
AND ae92002.act_code = 92002
LEFT OUTER JOIN table_atlas_part_order AS po1 ON po1.part_order2dispatch = cs.case2dispatch
AND po1.objid = (SELECT MIN(objid)
FROM table_atlas_part_order AS pocsq1
WHERE pocsq1.part_order2dispatch = cs.case2dispatch)
LEFT OUTER JOIN table_atlas_part_order AS po2 ON po2.part_order2dispatch = cs.case2dispatch
AND po2.objid = (SELECT MIN(objid)
FROM table_atlas_part_order AS pocsq2
WHERE pocsq2.part_order2dispatch = cs.case2dispatch
AND pocsq2.objid > (SELECT MIN(objid)
FROM table_atlas_part_order AS pocsq3
WHERE pocsq3.part_order2dispatch = cs.case2dispatch))
WHERE elm2.title = 'Cancel'
AND hdr.x_req_type = 'Vendor Exchange'
AND elm1.title = 'Customer'
AND cs.modify_stmp BETWEEN rw.start_date AND rw.end_date

... with multiple and cascaded correlated subqueries on outer joined tables, and multiple conditions on a single join, you will be VERY happy you used the ANSI syntax when it's time to make changes.

Once you start joining several tables, it becomes difficult to tell where the join conditions end and the conditions that actually select the rows begin - and that's assuming you didn't intermix them. In the big unioin above, it's easy to see the two different selection criteria that are applied to what are in fact two very similar joins (they only differ by one table), but you don't need a union to make this separation of join conditions and where conditions handy. Get in the habit now.